MySQL 數據類型詳情

Posted on 2021-11-09 by WalkonNet

1、數值類型

1.1、數值類型分類

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

MySQL中數值類型：

整數類型	字節	最小值	最大值
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
浮點數類型	字節	最小值	最大值
FLOAT	4	±1.175494351E-38	±3.402823466E+38
DOUBLE	8	±2.2250738585072014E-308	±1.7976931348623157E+308
定點數類型	字節	描述
DEC(M,D)， DECIMAL(M,D)	M+2	最大取值范圍與 DOUBLE 相同，給定 DECIMAL 的有效取值范圍由 M 和 D 決定
位類型	字節	最小值	最大值
BIT(M)	1～8	BIT(1)	BIT(64)

1.1.1、浮點數

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

1.1.2、位類型

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

例：

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類型數據插入時，首先將值轉換為二進制，如果允許，則進行插入，如果位數小於實際定義的位數，則插入失敗。

例：如果在剛剛的那張表中插入2，實際轉換為二進制為10，超出瞭bit(1)的實際定義位數，就會報異常，將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、時間日期類型

日期和時間類型	字節	最小值	最大值
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：經常插入或者更新日期為當前系統時間
YEAR：表示年

例1：創建時間表（dt），字段分別為 date、time、datetime 三種日期類型，並插入數值，查看顯示結果

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：創建測試表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)

可以發現，系統給 id1 自動創建瞭默認值 CURRENT_TIMESTAMP（系統日期）。（註意，MySQL隻給表中的第一個TIMESTAMP字段設置默認值為系統日期，如果有第二個TIMESTAMP類型，則默認值設置為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、字符串類型

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

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)

可以發現，當保存 BINARY 值時，在值的最後通過填充“0x00”（零字節）以達到指定的字段定義長度。從上例中看出，對於一個 BINARY(3)列，當插入時’a’變為’a\0\0′

1.1.5、ENUM 類型

ENUM：對 1～255 個成員的枚舉需要 1 個字節存儲；對於 255～65535 個成員，需要 2 個字節存儲。

例：

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開始；查詢的時候會轉換為大寫，也可以插入空（null）

1.1.6、SET類型

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

1～8 成員的集合，占 1 個字節。
9～16 成員的集合，占 2 個字節。
17～24 成員的集合，占 3 個字節。
25～32 成員的集合，占 4 個字節。
33～64 成員的集合，占 8 個字節。
Set和EMUN區別在於，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，插入不存在的定義列時，會報異常插入失敗

MySQL 數據類型詳情

目錄

1、數值類型

1.1、數值類型分類

1.1.1、浮點數

1.1.2、位類型

1.1.3、時間日期類型

1.1.3、時間日期類型

1.1.4、字符串類型

1.1.5、ENUM 類型

1.1.6、SET類型

推薦閱讀：

發佈留言取消回覆

近期文章

目錄

1、數值類型

1.1、數值類型分類

1.1.1、浮點數

1.1.2、位類型

1.1.3、時間日期類型

1.1.3、時間日期類型

1.1.4、字符串類型

1.1.5、ENUM 類型

1.1.6、SET類型

推薦閱讀：

發佈留言 取消回覆

近期文章

標籤

發佈留言取消回覆