MySQL 數(shù)據(jù)類型詳情

更新時間：2021年11月09日 14:53:46 作者：隨風去遠方

這篇文章主要介紹了MySQL 數(shù)據(jù)類型，數(shù)值類型分類又分嚴格數(shù)值類型和近似數(shù)值數(shù)據(jù)類型，下面文章圍繞MySQL 數(shù)據(jù)類型展開內(nèi)容,需要的朋友可以參考一下

1、數(shù)值類型

1.1、數(shù)值類型分類

嚴格數(shù)值類型（INTEGER、SMALLINT、DECIMAL 和 NUMERIC）
近似數(shù)值數(shù)據(jù)類型（FLOAT、REAL 和 DOUBLE PRECISION）

MySQL中數(shù)值類型：

整數(shù)類型	字節(jié)	最小值	最大值
TINYINT	1	有符號-128 無符號 0	有符號 127 無符號 255
SMALLINT	2	有符號-32768 無符號 0	有符號 32767 無符號 65535
MEDIUMINT	3	有符號-8388608 無符號 0	有符號 8388607 無符號 1677215
INT、INTEGER	4	有符號-2147483648 無符號 0	有符號 2147483647 無符號 4294967295
BIGINT	8	有符號-9223372036854775808 無符號 0	有符號 9223372036854775807 無符號 18446744073709551615
浮點數(shù)類型	字節(jié)	最小值	最大值
FLOAT	4	±1.175494351E-38	±3.402823466E+38
DOUBLE	8	±2.2250738585072014E-308	±1.7976931348623157E+308
定點數(shù)類型	字節(jié)	描述
DEC(M,D)， DECIMAL(M,D)	M+2	最大取值范圍與 DOUBLE 相同，給定 DECIMAL 的有效取值范圍由 M 和 D 決定
位類型	字節(jié)	最小值	最大值
BIT(M)	1～8	BIT(1)	BIT(64)

1.1.1、浮點數(shù)

浮點數(shù)如果不寫精度和標度，則會按照實際精度值顯示，如果有精度和標度，則會自動將四舍五入后的結(jié)果插入，系統(tǒng)不會報錯；定點數(shù)如果不寫精度和標度，則按照默認值 decimal(10,0)來進行操作，并且如果數(shù)據(jù)超越了精度和標度值，系統(tǒng)則會報錯。

1.1.2、位類型

BIT(位)類型：范圍從1-64，如果不寫，默認為1位，對于此字段，直接使用select命令將不會看到結(jié)果，可以用bin()(顯示為二進制格式)或者hex()（顯示為16進制格式）函數(shù)進行讀取

例：

mysql> desc t2;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t2 values(1);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|     |
+------+
1 row in set (0.00 sec)

mysql> select bin(id),hex(id) from t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
+---------+---------+
1 row in set (0.03 sec)

bit類型數(shù)據(jù)插入時，首先將值轉(zhuǎn)換為二進制，如果允許，則進行插入，如果位數(shù)小于實際定義的位數(shù)，則插入失敗。

例：如果在剛剛的那張表中插入2，實際轉(zhuǎn)換為二進制為10，超出了bit(1)的實際定義位數(shù)，就會報異常，將id定義為bit(2),在插入，就會成功

mysql> insert into t2 values(2);
ERROR 1406 (22001): Data too long for column 'id' at row 1
mysql> alter table t2 modify id bit(2);
Query OK, 1 row affected (0.67 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(2);
Query OK, 1 row affected (0.03 sec)

mysql> select bin(id),hex(id) from t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
| 10      | 2       |
+---------+---------+
2 rows in set (0.00 sec)

1.1.3、時間日期類型

日期和時間類型	字節(jié)	最小值	最大值
DATE	4	1000-01-01	9999-12-31
DATETIME	8	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	4	19700101080001	2038 年的某個時刻
TIME	3	-838:59:59	838:59:59
YEAR	1	1901	2155

DATE：用來表示年月日
DATETIME：用來表示年月日時分秒（支持插入格式：2007-9-3 12:10:10'、'2007/9/3 12+10+10'、'20070903121010'、20070903121010 等）
TIME：只用來表示時分秒
TIMESTAMP：經(jīng)常插入或者更新日期為當前系統(tǒng)時間
YEAR：表示年

例1：創(chuàng)建時間表（dt），字段分別為 date、time、datetime 三種日期類型，并插入數(shù)值，查看顯示結(jié)果

mysql> create table dt(d date,t time,dt datetime);
Query OK, 0 rows affected (0.23 sec)


mysql> insert into dt values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from dt;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2021-05-13 | 10:14:07 | 2021-05-13 10:14:07 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

例2：創(chuàng)建測試表t，字段id1為TIMESTAMP類型，插入空值，并顯示

mysql> create table t(id1 timestamp);
Query OK, 0 rows affected (0.22 sec)

mysql> insert into t values(null);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t;
+---------------------+
| id1                 |
+---------------------+
| 2021-05-13 10:18:05 |
+---------------------+
1 row in set (0.00 sec)

可以發(fā)現(xiàn)，系統(tǒng)給 id1 自動創(chuàng)建了默認值 CURRENT_TIMESTAMP（系統(tǒng)日期）。（注意，MySQL只給表中的第一個TIMESTAMP字段設(shè)置默認值為系統(tǒng)日期，如果有第二個TIMESTAMP類型，則默認值設(shè)置為0值）

例3：解釋如上說明

mysql> alter table t add column id2 timestamp;
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t \G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `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=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

1.1.4、字符串類型

字符串類型	字節(jié)	描述及存儲需求
CHAR（M）	M	M 為 0～255 之間的整數(shù)
VARCHAR（M）		M 為 0～65535 之間的整數(shù)，值的長度+1 個字節(jié)
TINYBLOB		允許長度 0～255 字節(jié)，值的長度+1 個字節(jié)
BLOB		允許長度 0～65535 字節(jié)，值的長度+2 個字節(jié)
MEDIUMBLOB		允許長度 0～167772150 字節(jié)，值的長度+3 個字節(jié)
LONGBLOB		允許長度 0～4294967295 字節(jié)，值的長度+4 個字節(jié)
TINYTEXT		允許長度 0～255 字節(jié)，值的長度+2 個字節(jié)
TEXT		允許長度 0～65535 字節(jié)，值的長度+2 個字節(jié)
MEDIUMTEXT		允許長度 0～167772150 字節(jié)，值的長度+3 個字節(jié)
LONGTEXT		允許長度 0～4294967295 字節(jié)，值的長度+4 個字節(jié)
VARBINARY（M）		允許長度 0～M 個字節(jié)的變長字節(jié)字符串，值的長度+1 個字節(jié)
BINARY（M）	M	允許長度 0～M 個字節(jié)的定長字節(jié)字符串

CHAR：固定長度，在檢索時，會刪除尾部的空格
VARCHAR：可變長字符串，在檢索時，會保留這些空格

例：

mysql> create table varc(v varchar(4),c char(4));
Query OK, 0 rows affected (0.20 sec)

mysql> insert into varc values('abc ','abc ');
Query OK, 1 row affected (0.03 sec)

mysql> select length(v),length(c) from varc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         3 |
+-----------+-----------+
1 row in set (0.01 sec)
mysql> select concat(v,'+'),concat(c,'+') from varc;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| abc +         | abc+          |
+---------------+---------------+
1 row in set (0.00 sec)

BINARY：類似于char，但他是二進制字符串
VARBINARY：類似于varchar，單他是存儲二進制字符串

例：

mysql> create table bina(c binary(3));
Query OK, 0 rows affected (0.22 sec)

mysql> insert into t set c='a';
ERROR 1054 (42S22): Unknown column 'c' in 'field list'
mysql> insert into bina set c='a';
Query OK, 1 row affected (0.05 sec)

mysql> select *,hex(c),c='a',c='a\0',c='a\0\0' from bina;
+------+--------+-------+---------+-----------+
| c    | hex(c) | c='a' | c='a\0' | c='a\0\0' |
+------+--------+-------+---------+-----------+
| a    | 610000 |     0 |       0 |         1 |
+------+--------+-------+---------+-----------+
1 row in set (0.00 sec)

可以發(fā)現(xiàn)，當保存 BINARY 值時，在值的最后通過填充“0x00”（零字節(jié)）以達到指定的字段定義長度。從上例中看出，對于一個 BINARY(3)列，當插入時'a'變?yōu)?a\0\0'

1.1.5、ENUM 類型

ENUM：對 1～255 個成員的枚舉需要 1 個字節(jié)存儲；對于 255～65535 個成員，需要 2 個字節(jié)存儲。

例：

mysql> create table gend(gender enum('M','F'));
Query OK, 0 rows affected (0.20 sec)

mysql> insert into gend values('M'),('F'),('1'),(null);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from gend;
+--------+
| gender |
+--------+
| M      |
| F      |
| M      |
| NULL   |
+--------+
4 rows in set (0.00 sec)

由此可以看出，枚舉類型的插入值得時候，是忽略大小寫的，初始下標從1開始；查詢的時候會轉(zhuǎn)換為大寫，也可以插入空（null）

1.1.6、SET類型

SET：字符串對象，里面可以包含64個成員，成員不同，存儲上也有所不同

1～8 成員的集合，占 1 個字節(jié)。
9～16 成員的集合，占 2 個字節(jié)。
17～24 成員的集合，占 3 個字節(jié)。
25～32 成員的集合，占 4 個字節(jié)。
33～64 成員的集合，占 8 個字節(jié)。
Set和EMUN區(qū)別在于，Set一次可以選則多個成員，而ENUM則只能選擇一個

例：

mysql> create table st(col set('a','b','c','d'));
Query OK, 0 rows affected (0.20 sec)

mysql> insert into st values('a,b'),('a,d,a'),('a,c'),('a');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into st values('f');
ERROR 1265 (01000): Data truncated for column 'col' at row 1
mysql> insert into st values(null);
Query OK, 1 row affected (0.03 sec)

mysql> select * from st;
+------+
| col  |
+------+
| a,b  |
| a,d  |
| a,c  |
| a    |
| NULL |
+------+
5 rows in set (0.00 sec)

由此可以看出，set是可以插入多個成員的，也可以插入null，插入不存在的定義列時，會報異常插入失敗

您可能感興趣的文章: