MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型。
這些類型包括嚴(yán)格數(shù)值數(shù)據(jù)類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數(shù)值數(shù)據(jù)類型(FLOAT、REAL和DOUBLE PRECISION)。
關(guān)鍵字INT是INTEGER的同義詞，關(guān)鍵字DEC是DECIMAL的同義詞。
MySQL支持的整數(shù)類型有TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數(shù)類型的存儲和范圍。
對于小數(shù)的表示，MYSQL分為兩種方式：浮點數(shù)和定點數(shù)。浮點數(shù)包括float(單精度)和double(雙精度),而定點數(shù)只有decimal一種，在mysql中以字符串的形式存放，比浮點數(shù)更精確，適合用來表示貨幣等精度高的數(shù)據(jù)。
BIT數(shù)據(jù)類型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
?
?
| 類型 | 大小 | 范圍（有符號） | 范圍（無符號）unsigned約束 | 用途 |
|---|---|---|---|---|
| TINYINT | 1 字節(jié) | (-128，127) | (0，255) | 小整數(shù)值 |
| SMALLINT | 2 字節(jié) | (-32 768，32 767) | (0，65 535) | 大整數(shù)值 |
| MEDIUMINT | 3 字節(jié) | (-8 388 608，8 388 607) | (0，16 777 215) | 大整數(shù)值 |
| INT或INTEGER | 4 字節(jié) | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整數(shù)值 |
| BIGINT | 8 字節(jié) | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 極大整數(shù)值 |
| FLOAT | 4 字節(jié) float(255,30) | (-3.402 823 466 E 38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E 38) | 0，(1.175 494 351 E-38，3.402 823 466 E 38) | 單精度 浮點數(shù)值 |
| DOUBLE | 8 字節(jié) double(255,30) | (-1.797 693 134 862 315 7 E 308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E 308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E 308) | 雙精度 浮點數(shù)值 |
| DECIMAL | 對DECIMAL(M,D) ，如果M>D，為M 2否則為D 2 double(65,30) | 依賴于M和D的值 | 依賴于M和D的值 | 小數(shù)值 |
# 創(chuàng)建表一個是默認寬度的int，一個是指定寬度的int(5)mysql> create table t1 (id1 int,id2 int(5));Query OK, 0 rows affected (0.02 sec)# 像t1中插入數(shù)據(jù)1，1mysql> insert into t1 values (1,1);Query OK, 1 row affected (0.01 sec)# 可以看出結(jié)果上并沒有異常mysql> select * from t1; ------ ------ | id1 | id2 | ------ ------ | 1 | 1 | ------ ------ 1 row in set (0.00 sec)# 那么當(dāng)我們插入了比寬度更大的值，會不會發(fā)生報錯呢？mysql> insert into t1 values (111111,111111);Query OK, 1 row affected (0.00 sec)# 答案是否定的，id2仍然顯示了正確的數(shù)值，沒有受到寬度限制的影響mysql> select * from t1; ------------ -------- | id1 | id2 | ------------ -------- | 0000000001 | 00001 || 0000111111 | 111111 | ------------ -------- 2 rows in set (0.00 sec)# 修改id1字段 給字段添加一個unsigned表示無符號mysql> alter table t1 modify id1 int unsigned;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc t1; ------- ------------------ ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- ------------------ ------ ----- --------- ------- | id1 | int(10) unsigned | YES | | NULL | || id2 | int(5) | YES | | NULL | | ------- ------------------ ------ ----- --------- ------- 2 rows in set (0.01 sec)# 當(dāng)給id1添加的數(shù)據(jù)大于214748364時，可以順利插入mysql> insert into t1 values (2147483648,2147483647);Query OK, 1 row affected (0.00 sec)# 當(dāng)給id2添加的數(shù)據(jù)大于214748364時，會報錯mysql> insert into t1 values (2147483647,2147483648);ERROR 1264 (22003): Out of range value for column 'id2' at row 1int整數(shù)示例
# 創(chuàng)建表的三個字段分別為float，double和decimal參數(shù)表示一共顯示5位，小數(shù)部分占2位mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));Query OK, 0 rows affected (0.02 sec)# 向表中插入1.23，結(jié)果正常mysql> insert into t2 values (1.23,1.23,1.23);Query OK, 1 row affected (0.00 sec)mysql> select * from t2; ------ ------ ------ | id1 | id2 | id3 | ------ ------ ------ | 1.23 | 1.23 | 1.23 | ------ ------ ------ 1 row in set (0.00 sec)# 向表中插入1.234，會發(fā)現(xiàn)4都被截斷了mysql> insert into t2 values (1.234,1.234,1.234);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from t2; ------ ------ ------ | id1 | id2 | id3 | ------ ------ ------ | 1.23 | 1.23 | 1.23 || 1.23 | 1.23 | 1.23 | ------ ------ ------ 2 rows in set (0.00 sec)# 向表中插入1.235發(fā)現(xiàn)數(shù)據(jù)雖然被截斷，但是遵循了四舍五入的規(guī)則mysql> insert into t2 values (1.235,1.235,1.235);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from t2; ------ ------ ------ | id1 | id2 | id3 | ------ ------ ------ | 1.23 | 1.23 | 1.23 || 1.23 | 1.23 | 1.23 || 1.24 | 1.24 | 1.24 | ------ ------ ------ 3 rows in set (0.00 sec)# 建新表去掉參數(shù)約束mysql> create table t3 (id1 float,id2 double,id3 decimal);Query OK, 0 rows affected (0.02 sec)# 分別插入1.234mysql> insert into t3 values (1.234,1.234,1.234);Query OK, 1 row affected, 1 warning (0.00 sec)# 發(fā)現(xiàn)decimal默認值是(10,0)的整數(shù)mysql> select * from t3; ------- ------- ------ | id1 | id2 | id3 | ------- ------- ------ | 1.234 | 1.234 | 1 | ------- ------- ------ 1 row in set (0.00 sec)# 當(dāng)對小數(shù)位沒有約束的時候，輸入超長的小數(shù)，會發(fā)現(xiàn)float和double的區(qū)別mysql> insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from t3; --------- -------------------- ------ | id1 | id2 | id3 | --------- -------------------- ------ | 1.234 | 1.234 | 1 || 1.23556 | 1.2355555555555555 | 1 | --------- -------------------- ------ 2 rows in set (0.00 sec)小數(shù)示例 返回頂部
表示時間值的日期和時間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每個時間類型有一個有效值范圍和一個"零"值，當(dāng)指定不合法的MySQL不能表示的值時使用"零"值。
TIMESTAMP類型有專有的自動更新特性，將在后面描述。
| 類型 | 大小 (字節(jié)) | 范圍 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 年月日 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 時分秒 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 年月日時分秒 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 結(jié)束時間是第?2147483647?秒，北京時間?2038-1-19 11:14:07，格林尼治時間 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和時間值，時間戳 |
mysql> create table t4 (d date,t time,dt datetime);Query OK, 0 rows affected (0.02 sec)mysql> desc t4; ------- ---------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- ---------- ------ ----- --------- ------- | d | date | YES | | NULL | || t | time | YES | | NULL | || dt | datetime | YES | | NULL | | ------- ---------- ------ ----- --------- ------- 3 rows in set (0.01 sec)mysql> insert into t4 values (now(),now(),now());Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t4; ------------ ---------- --------------------- | d | t | dt | ------------ ---------- --------------------- | 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 | ------------ ---------- --------------------- 1 row in set (0.00 sec)mysql> insert into t4 values (null,null,null);Query OK, 1 row affected (0.01 sec)mysql> select * from t4; ------------ ---------- --------------------- | d | t | dt | ------------ ---------- --------------------- | 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 || NULL | NULL | NULL | ------------ ---------- --------------------- 2 rows in set (0.00 sec)date/time/datetime示例
mysql> create table t5 (id1 timestamp);Query OK, 0 rows affected (0.02 sec)mysql> desc t5; ------- ----------- ------ ----- ------------------- ----------------------------- | Field | Type | Null | Key | Default | Extra | ------- ----------- ------ ----- ------------------- ----------------------------- | id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | ------- ----------- ------ ----- ------------------- ----------------------------- 1 row in set (0.00 sec)# 插入數(shù)據(jù)null，會自動插入當(dāng)前時間的時間mysql> insert into t5 values (null);Query OK, 1 row affected (0.00 sec)mysql> select * from t5; --------------------- | id1 | --------------------- | 2018-09-21 14:56:50 | --------------------- 1 row in set (0.00 sec)#添加一列 默認值是'0000-00-00 00:00:00'mysql> alter table t5 add id2 timestamp;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table t5 \G;*************************** 1. row *************************** Table: t5Create Table: CREATE TABLE `t5` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00') ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specified# 手動修改新的列默認值為當(dāng)前時間mysql> alter table t5 modify id2 timestamp default current_timestamp;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table t5 \G;*************************** 1. row *************************** Table: t5Create Table: CREATE TABLE `t5` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specifiedmysql> insert into t5 values (null,null);Query OK, 1 row affected (0.01 sec)mysql> select * from t5; --------------------- --------------------- | id1 | id2 | --------------------- --------------------- | 2018-09-21 14:56:50 | 0000-00-00 00:00:00 || 2018-09-21 14:59:31 | 2018-09-21 14:59:31 | --------------------- --------------------- 2 rows in set (0.00 sec)timestamp示例
mysql> create table t6 (t1 timestamp);Query OK, 0 rows affected (0.02 sec)mysql> desc t6; ------- ----------- ------ ----- ------------------- ----------------------------- | Field | Type | Null | Key | Default | Extra | ------- ----------- ------ ----- ------------------- ----------------------------- | t1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | ------- ----------- ------ ----- ------------------- ----------------------------- 1 row in set (0.01 sec)mysql> insert into t6 values (19700101080001);Query OK, 1 row affected (0.00 sec)mysql> select * from t6; --------------------- | t1 | --------------------- | 1970-01-01 08:00:01 | --------------------- 1 row in set (0.00 sec)# timestamp時間的下限是19700101080001mysql> insert into t6 values (19700101080000);ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 't1' at row 1mysql> insert into t6 values ('2038-01-19 11:14:07');Query OK, 1 row affected (0.00 sec)# timestamp時間的上限是2038-01-19 11:14:07mysql> insert into t6 values ('2038-01-19 11:14:08');ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1mysql> timestamp示例2mysql> create table t7 (y year);Query OK, 0 rows affected (0.02 sec)mysql> insert into t7 values (2018);Query OK, 1 row affected (0.00 sec)mysql> select * from t7; ------ | y | ------ | 2018 | ------ 1 row in set (0.00 sec)year示例
mysql> create table t8 (dt datetime);Query OK, 0 rows affected (0.01 sec)mysql> insert into t8 values ('2018-9-26 12:20:10');Query OK, 1 row affected (0.01 sec)mysql> insert into t8 values ('2018/9/26 12 20 10');Query OK, 1 row affected (0.00 sec)mysql> insert into t8 values ('20180926122010');Query OK, 1 row affected (0.00 sec)mysql> insert into t8 values (20180926122010);Query OK, 1 row affected (0.00 sec)mysql> select * from t8; --------------------- | dt | --------------------- | 2018-09-26 12:20:10 || 2018-09-26 12:20:10 || 2018-09-26 12:20:10 || 2018-09-26 12:20:10 | --------------------- 4 rows in set (0.00 sec)datetime示例 返回頂部字符串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節(jié)描述了這些類型如何工作以及如何在查詢中使用這些類型。
| 類型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字節(jié) | 定長字符串 |
| VARCHAR | 0-65535 字節(jié) | 變長字符串 |
| TINYBLOB | 0-255字節(jié) | 不超過 255 個字符的二進制字符串 |
| TINYTEXT | 0-255字節(jié) | 短文本字符串 |
| BLOB | 0-65 535字節(jié) | 二進制形式的長文本數(shù)據(jù) |
| TEXT | 0-65 535字節(jié) | 長文本數(shù)據(jù) |
| MEDIUMBLOB | 0-16 777 215字節(jié) | 二進制形式的中等長度文本數(shù)據(jù) |
| MEDIUMTEXT | 0-16 777 215字節(jié) | 中等長度文本數(shù)據(jù) |
| LONGBLOB | 0-4 294 967 295字節(jié) | 二進制形式的極大文本數(shù)據(jù) |
| LONGTEXT | 0-4 294 967 295字節(jié) | 極大文本數(shù)據(jù) |
CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進行大小寫轉(zhuǎn)換。
CHAR列的長度固定為創(chuàng)建表是聲明的長度,范圍(0-255);而VARCHAR的值是可變長字符串范圍(0-65535)。
mysql> create table t9 (v varchar(4),c char(4));Query OK, 0 rows affected (0.01 sec)mysql> insert into t9 values ('ab ','ab ');Query OK, 1 row affected (0.00 sec)# 在檢索的時候char數(shù)據(jù)類型會去掉空格mysql> select * from t9; ------ ------ | v | c | ------ ------ | ab | ab | ------ ------ 1 row in set (0.00 sec)# 來看看對查詢結(jié)果計算的長度mysql> select length(v),length(c) from t9; ----------- ----------- | length(v) | length(c) | ----------- ----------- | 4 | 2 | ----------- ----------- 1 row in set (0.00 sec)# 給結(jié)果拼上一個加號會更清楚mysql> select concat(v,' '),concat(c,' ') from t9; --------------- --------------- | concat(v,' ') | concat(c,' ') | --------------- --------------- | ab | ab | --------------- --------------- 1 row in set (0.00 sec)# 當(dāng)存儲的長度超出定義的長度，會截斷mysql> insert into t9 values ('abcd ','abcd ');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t9; ------ ------ | v | c | ------ ------ | ab | ab || abcd | abcd | ------ ------ 2 rows in set (0.00 sec)char/varchar示例BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR，不同的是它們包含二進制字符串而不要非二進制字符串。也就是說，它們包含字節(jié)字符串而不是字符字符串。這說明它們沒有字符集，并且排序和比較基于列值字節(jié)的數(shù)值值。
BLOB 是一個二進制大對象，可以容納可變數(shù)量的數(shù)據(jù)。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區(qū)別在于可容納存儲范圍不同。
有 4 種 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應(yīng)的這 4 種 BLOB 類型，可存儲的最大長度不同，可根據(jù)實際情況選擇。
返回頂部ENUM中文名稱叫枚舉類型，它的值范圍需要在創(chuàng)建表時通過枚舉方式顯示。ENUM只允許從值集合中選取單個值，而不能一次取多個值。
SET和ENUM非常相似，也是一個字符串對象，里面可以包含0-64個成員。根據(jù)成員的不同，存儲上也有所不同。set類型可以允許值集合中任意選擇1或多個元素進行組合。對超出范圍的內(nèi)容將不允許注入，而對重復(fù)的值將進行自動去重。
| 類型 | 大小 | 用途 |
| ENUM | 對1-255個成員的枚舉需要1個字節(jié)存儲; 對于255-65535個成員，需要2個字節(jié)存儲; 最多允許65535個成員。 | 單選：選擇性別 |
| SET | 1-8個成員的集合，占1個字節(jié) 9-16個成員的集合，占2個字節(jié) 17-24個成員的集合，占3個字節(jié) 25-32個成員的集合，占4個字節(jié) 33-64個成員的集合，占8個字節(jié) | 多選：興趣愛好 |
mysql> create table t10 (name char(20),gender enum('female','male'));Query OK, 0 rows affected (0.01 sec)# 選擇enum('female','male')中的一項作為gender的值，可以正常插入mysql> insert into t10 values ('nezha','male');Query OK, 1 row affected (0.00 sec)# 不能同時插入'male,female'兩個值，也不能插入不屬于'male,female'的值mysql> insert into t10 values ('nezha','male,female');ERROR 1265 (01000): Data truncated for column 'gender' at row 1mysql> create table t11 (name char(20),hobby set('抽煙','喝酒','燙頭','翻車'));Query OK, 0 rows affected (0.01 sec)# 可以任意選擇set('抽煙','喝酒','燙頭','翻車')中的項，并自帶去重功能mysql> insert into t11 values ('yuan','燙頭,喝酒,燙頭');Query OK, 1 row affected (0.01 sec)mysql> select * from t11; ------ --------------- | name | hobby | ------ --------------- | yuan | 喝酒,燙頭 | ------ --------------- 1 row in set (0.00 sec)# 不能選擇不屬于set('抽煙','喝酒','燙頭','翻車')中的項，mysql> insert into t11 values ('alex','燙頭,翻車,看妹子');ERROR 1265 (01000): Data truncated for column 'hobby' at row 1?
來源：https://www.icode9.com/content-2-253501.html