MySQL数据库：数据的类型

目录

一. 数值类型

1.1 整形类型

1.2 bit类型

1.3 小数类型

二. 字符串类型

2.1 char类型

2.2 varchar类型

三. 日期和时间类型

四. 枚举和集合类型

4.1 enum类型

4.2 set类型

五. 总结

---

一. 数值类型

数值类型，包括整形类型、bit类型和浮点数（小数）类型，表1给出了MySQL所在支持的所有数值类型，MySQL中的数值类型与C/C++中的int/double类型数据相似，每种数值类型都有其能表示的数值范围，并占用特定大小的空间，在使用数据库时应当根据具体的场景来选择数据类型。

表1 MySQL支持的数值类型

分类	数值类型	大小(字节)	说明
整数类型	tinyint	1	无符号数范围是[0,2^8-1]，有符号数范围是[-2^7, 2^7-1]
	samllint	2	无符号数范围是[0,2^16-1]，有符号数范围是[-2^15, 2^15-1]
	mediumint	3	无符号数范围是[0,2^24-1]，有符号数范围是[-2^23, 2^23-1]
	int	4	无符号数范围是[0,2^32-1]，有符号数范围是[-2^31, 2^31-1]
	bigint	8	无符号数范围是[0,2^64-1]，有符号数范围是[-2^63, 2^63-1]
bit类型	bit (M)	/	类似与C语言中的位段类型，M表示有多少个bit位
小数类型	float	4	浮点数类型，能够表示最高约小数点后7位精度
	double	8	浮点数类型，与float类似，能够表示的数值范围高于float
	decimal	/	能够准确表述约小数点后约65位的精度

1.1 整形类型

本文以tinyint类型为例，讲解MySQL中整形的使用方法及相关特性，下面的代码定义了一张名为t1的表，其中包含有符号tinyint类型成员num，尝试插入位于[-128,127]范围内的数据，可以成功插入。但是，如果插入超过tinyint所能表示的范围的数据，那么就插入失败了，MySQL会对超过表示类型范围的数据进行拦截，禁止插入。

结论：MySQL会拦截非法数据的插入，如超过特定类型能表示范围的数据。

mysql> create table t1 (
    -> num tinyint
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql> insert into t1 values (122);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values (-128);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (127);  -- 插入位于tinyint表示范围内的数据插入成功
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into t1 values (128);  -- 超过tinyint类型表示范围的数据插入失败
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t1 values (-133);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> select * from t1;
+------+
| num  |
+------+
|  122 |
| -128 |
|  127 |  -- 非法数据没有出现在表中
+------+
3 rows in set (0.01 sec)

下面的代码定义了表t2，将t1中的有符号tinyint成员num的类型转变为无符号tinyint unsigned类型，此时向t2中插入位于[128,255]范围内的数据全部插入成功，但如果试图插入负数或者超过256的数据，则依然会插入失败。

无符号数，相对于有符号，能够表示更大的非负数范围，但无法表示负数。

mysql> create table t2 (
    -> num tinyint unsigned
    -> );
Query OK, 0 rows affected (0.17 sec)

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

mysql> insert into t2 values (256);  -- 超过255的数据插入失败
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values (255);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t2 values (-130); -- 负数插入失败
ERROR 1264 (22003): Out of range value for column 'num' at row 1

1.2 bit类型

bit类型，类似于C/C++中的位段，该成员的大小以二进制bit位来表示，如指定bit (1)，那么表示一个单比特位数据成员，只能表示0和1。

语法：bit (M)

说明：M为比特位的个数，范围1~64，如果忽略则默认为1

在建表的时候，如果给出的M值超过64，会直接报错，MySQL会检查M不能超过bit类型的界限，如果企图给bit (M)类型数据赋予超过表达能力范围的数据，那么MySQL也会拦截操作。 

mysql> create table t3 (
    -> bnum bit (3)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> create table t4 (
    -> bnum bit (64)
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> create table t5 (
    -> bnum bit (65)  -- 超过bit类型二进制位的上限，被MySQL拦截
    -> );
ERROR 1439 (42000): Display width out of range for column 'bnum' (max = 64)
mysql> insert into t3 values (8); -- 企图给bit(3)类型数据赋予超过表示能力范围的数据，被MySQL拦截
ERROR 1406 (22001): Data too long for column 'bnum' at row 1
mysql> insert into t3 values (7);
Query OK, 1 row affected (0.01 sec)

下面的代码创建了表t6，其中包含成员bnum，类型为bit (20)，线向其中插入数据10和65，并通过select * from t6 显示表中的全部数据值，我们看到，10没有被显示出来，而65显示为'A'，这说明bit (M) 类型数据在前端的默认显示的是其ASCII码对应的字符。

结论：bit (M) 在前端默认显示为其ASCII码对应的字符。

mysql> create table t6 (
    -> bnum bit (20)
    -> );
Query OK, 0 rows affected (0.22 sec)

mysql> insert into t6 values (10);
Query OK, 1 row affected (0.04 sec)

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

mysql> select * from t6;
+------+
| bnum |
+------+
|   
  |
|   A  |
+------+
2 rows in set (0.00 sec)

1.3 小数类型

首先介绍float类型，double类型与float类型的使用方式基本一致。

语法：float (M, D) unsigned

解释：M表示float类型数据最多能显示的有效数据长度（包括整数部分和小数部分），D表示精度为小数点后几位，必须要满足M ≥ N，unsigned表示无符号，可以省略。

对于float和double的无符号类型，与有符号的区别仅仅是不能表示有符号类型的负数部分，而正数部分并不能比对应有符号表示更广的范围，因此float和double一般都设置为有符号，但这也不绝对，有些场景下就是应当限制数值不为负数，这种情况下也应当声明unsigned。

下面的代码定义了float (4,2)类型的数据f1，其所能够表示的范围是-99.99~99.99，如果尝试插入超过范围的数据，那么就会报错。

mysql> create table t7 (
    -> f1 float(4,2)
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> insert into t7 values (-99.99);  -- 不超过范围的数据插入成功
Query OK, 1 row affected (0.01 sec)

mysql> insert into t7 values (99.99);
Query OK, 1 row affected (0.02 sec)

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

mysql> select * from t7;
+--------+
| f1     |
+--------+
| -99.99 |
|  99.99 |
|  12.37 |
+--------+
3 rows in set (0.00 sec) 

mysql> insert into t7 values (123.23);  -- 超过范围的数据插入失败
ERROR 1264 (22003): Out of range value for column 'f1' at row 1
mysql> insert into t7 values (-110.12);
ERROR 1264 (22003): Out of range value for column 'f1' at row 1
mysql> insert into t7 values (-100.00);
ERROR 1264 (22003): Out of range value for column 'f1' at row 1
mysql> select * from t7;
+--------+
| f1     |
+--------+
| -99.99 |
|  99.99 |
|  12.37 |
+--------+
3 rows in set (0.00 sec)

对于float (4,2)类型数据，如果试图插入的数据小数点后面超过了2位，那么所采取的策略是四舍五入，四舍五入也会受到范围的限制，如果舍入后在范围内，那么插入数据成功，如果在范围之外，那么插入失败。

如：尝试给float (4,2)插入99.994，插入成功，因为99.994舍入后的值是99.99未越界，如果尝试插入99.995，那么插入失败，因为舍入后的值为100，超过了范围。

mysql> create table t8 (
    -> f1 float(4,2)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t8 values (12.123);  -- 向下取值，实际插入12.12
Query OK, 1 row affected (0.04 sec)

mysql> insert into t8 values (91.567);  -- 向上取值，实际插入91.57
Query OK, 1 row affected (0.02 sec)

mysql> insert into t8 values (-91.567);  -- 向上取值，实际插入-91.57
Query OK, 1 row affected (0.02 sec)

mysql> insert into t8 values (99.994);  -- 向下取值得99.99，不越界，插入成功
Query OK, 1 row affected (0.05 sec)

mysql> insert into t8 values (99.995);  -- 向上取值得100.00，越界，插入失败
ERROR 1264 (22003): Out of range value for column 'f1' at row 1
mysql> select * from t8;
+--------+
| f1     |
+--------+
|  12.12 |
|  91.57 |
| -91.57 |
|  99.99 |
+--------+
4 rows in set (0.01 sec)

decimal类型数据的使用方法与float类型数据基本一致，decimal和float类型的区别在于：decimal相比于float能够表示更高的小数点后精度。

语法：decimal  (M, D) unsigned

解释：M表示decimal类型数据最多能显示的有效数据长度（包括整数部分和小数部分），D表示精度为小数点后几位，必须要满足M ≥ N，unsigned表示无符号，可以省略。

下面的程序对float和decimal的精度进行了测试，创建一张表，其中包含两个数据f1和d1，f1的类型为float (10,8)，d1的类型为decimal (10,8)，向表中插入数据，f1和d1的值全部设为10.12345678，可以看到d1对应的decimal(10,8)类型正确存储了数据，而f1对应的float (10,8)出现了数据失真。

float最多能准确表示小数点后约7为数字，decimal能够准确表示约小数点后约65位。在对精度要求极高的业务场景中（如银行金融系统），应当采用decimal类型数据。

mysql> create table t9 (
    -> f1 float(10,8),
    -> d1 decimal(10,8)
    -> );
Query OK, 0 rows affected (0.21 sec)

mysql> insert into t9 (f1,d1) values (10.12345678, 10.12345678);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t9;
+-------------+-------------+
| f1          | d1          |
+-------------+-------------+
| 10.12345695 | 10.12345678 |  -- decimal(10,8)准确表示了数据，而float(10,8)出现了失真
+-------------+-------------+
1 row in set (0.01 sec)

二. 字符串类型

在MySQL中，字符串类型数据包括固定长度字符串类型char和变长字符串类型varchar。

表2 字符串类型

类型	说明
char (L)	固定长度字符串，L表示最大存储字符数，L不超过255
varchar (L)	可变长度字符串，L表示最大存储字符数，varchar类型最大不能超过65535字节

2.1 char类型

语法：char (L)

解释：能够存储最多L个字符的定长字符串类型数据。

下面的代码在表中定义了char (2)类型的成员msg，先后向表中插入 'ab' 和 ‘你好’，均能够插入成功，这里要注意区分MySQL层面和语言层面对字符的理解，在语言层面，一个字符占用1bytes的存储空间，utf8格式编码一个汉字要占用3个字节。在MySQL中，无论是一个汉字，还是一个C语言字符，都被解释为一个字符。

结论：在MySQL中，无论是一个汉字还是一个语言层面的字符，都被解释为一个字符。

mysql> create table t10 (
    -> msg char(2)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t10 values ('10');
Query OK, 1 row affected (0.04 sec)

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

mysql> insert into t10 values ('你好');
Query OK, 1 row affected (0.04 sec)

mysql> insert into t10 values ('abc');
ERROR 1406 (22001): Data too long for column 'msg' at row 1
mysql> insert into t10 values ('早上好');
ERROR 1406 (22001): Data too long for column 'msg' at row 1

2.2 varchar类型

语法：varchar (L)

解释：最多能够存储L个字符的变长字符串。

对于varchar类型的数据，其最大的长度为65535个字节，注意是65535字节而不是字符串，同时还需要1~3个字节来存储varchar类型数据的实际长度。

对应使用utf8编码的数据库（表），一个字符要占用3个字节，那么一个varchar类型数据最多能够存放 (65535 - 3) / 3 = 21844 个字符，对于使用gbk编码的数据库，一个字符占用2个字节，那么一个varchar类型数据最多能存放 (65535 - 3) / 2 = 32766 个字符。如果定义的varchar类型数据企图包含超过上限的字符数，那么MySQL会拦截操作。

varchar类型和char类型的对比：

• 对于char(L)和varchar(L)，varchar占用的空间为 实际存储字符占用的空间 + 1~3字节，而char类型则永远为L。
• char类型的效率要高于varchar类型。
• 如果char(L)类型和varchar(L)类型存储的字符数量都达到了上限L，那么char(L)占用的空间反而更低，如char(3)和varchar(3)存储" abc"，char(3)就只需要3个字符的空间，但varchar(3)还需要1字节来存储实际占用的空间。

mysql> create table t11 (
    -> num varchar(21844)
    -> )charset=utf8;
Query OK, 0 rows affected (0.16 sec)

mysql> create table t12 (
    -> num varchar(21845) -- utf8编码下varchar超过21844个字符，插入失败
    -> )charset=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> 
mysql> create table t12 (
    -> num varchar(32766)
    -> )charset=gbk;
Query OK, 0 rows affected (0.08 sec)
mysql> create table t13 (
    -> num varchar(32767)  -- gbk编码下varchar超过21844个字符，插入失败
    -> )charset=gbk;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

三. 日期和时间类型

在MySQL中，日期和时间类型数据有如下三种：

• date：表示日期 年-月-日，格式为'yyyy-mm-dd'，占用3字节空间。
• datetime：表示 年-月-日 时:分:秒，格式为'yyyy-mm-dd HH:ii:ss'，占用8字节空间。
• timestamp，时间戳，表示从格林威治时间1970年1月1日0点到现在的秒数，timestamp类型数据在前端的显示格式为'yyyy-mm-dd HH:ii:ss'，与datetime完全一致，占用4字节空间。

如下代码所示，date和datetime类型数据需要用户手动插入，用于标识某个特定的时间，对于时间戳timestamp，每次在对表的某一行进行操作后，对应的时间戳timestamp类型数据都会自动被修改，时间戳是一种会动态变化的数据类型。

mysql> select * from t13;
+----+------------+---------------------+---------------------+
| id | d1         | d2                  | d3                  |
+----+------------+---------------------+---------------------+
|  1 | 1999-10-15 | 2011-12-08 12:08:23 | 2023-12-17 10:17:22 |
|  2 | 2003-07-22 | 2008-09-08 09:08:13 | 2023-12-17 10:17:55 |
+----+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> update t13 set d1='2022-11-11' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t13;
+----+------------+---------------------+---------------------+
| id | d1         | d2                  | d3                  |
+----+------------+---------------------+---------------------+
|  1 | 2022-11-11 | 2011-12-08 12:08:23 | 2023-12-17 10:19:21 |
|  2 | 2003-07-22 | 2008-09-08 09:08:13 | 2023-12-17 10:17:55 |
+----+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

四. 枚举和集合类型

4.1 enum类型

语法：enum ('选项1', '选项2', '选项3', ... ...)

解释：enum与C/C++中的枚举类型类似，向枚举类型插入数据时，只能进行单选。

如下代码，表stu1定义了enum类型数据gender，包含两个选项 '男' 和 '女'，对于enum类型数据，有以下两种方法可以插入数据：

1. 直接指定原始数据。
2. 指定数据在enum中的下标，注意下标是从1开始，而不是C/C++中下标从0开始。

当对枚举类型值进行修改和检索操作时，同样也可以使用直接给值和下标两种方式修改。 

mysql> create table stu1 (
    -> name varchar(20) not null,
    -> gender enum('男', '女')  -- 枚举类型数据，单选插入
    -> );
Query OK, 0 rows affected (0.23 sec)

mysql> insert into stu1 values ('zhangsan','男');  -- 直接给定值插入枚举类型数据
Query OK, 1 row affected (0.03 sec)

mysql> insert into stu1 values ('lisi','女');
Query OK, 1 row affected (0.05 sec)

mysql> insert into stu1 values ('wangwu',1);  -- 通过下标插入枚举类型数据
Query OK, 1 row affected (0.04 sec)

mysql> insert into stu1 values ('zhaoliu',2);
Query OK, 1 row affected (0.04 sec)

mysql> select * from stu1;
+----------+--------+
| name     | gender |
+----------+--------+
| zhangsan | 男     |
| lisi     | 女     |
| wangwu   | 男     |
| zhaoliu  | 女     |
+----------+--------+
4 rows in set (0.00 sec)

4.2 set类型

语法：set ('选项1', '选项2', '选项3', ... ...)

解释：set为集合类型，在插入的时候，可以选取其中的多个选项插入。

下面的代码定义了stu2类型数据，其中包含了 hobby set('编程', '绘画', '游泳', '篮球', '跑步')，对于set类型数据，通过直接给定数值，可以成功插入set类型数据。如果企图插入set中不存在的选项，那么MySQL会直接拦截操作。

set与enum的不同：

• enum类型数据为 “单选” 类型，必须选取且只能选取一个选项。
• set类型数据为 “多选” 类型，可以选取一个或多个，也可以一个都不选取。

mysql> create table stu2 (
    -> name varchar(20) not null,
    -> hobby set('编程','绘画','游泳','篮球','跑步')
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> insert into stu2 values ('zhang', ''); -- set类型数据可以不选取任何一个选项
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu2 values ('zhao', '篮球');
Query OK, 1 row affected (0.03 sec)

mysql> insert into stu2 values ('qian', '跑步');
Query OK, 1 row affected (0.00 sec)

mysql> insert into stu2 values ('qian', '编程');
Query OK, 1 row affected (0.03 sec)

mysql> insert into stu2 values ('sun', '绘画,篮球');
Query OK, 1 row affected (0.03 sec)

mysql> insert into stu2 values ('li', '绘画,篮球,跑步');
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu2;
+-------+----------------------+
| name  | hobby                |
+-------+----------------------+
| zhang |                      |
| zhao  | 篮球                 |
| qian  | 跑步                 |
| qian  | 编程                 |
| sun   | 绘画,篮球            |
| li    | 绘画,篮球,跑步       |
+-------+----------------------+
6 rows in set (0.00 sec)

mysql> insert into stu2 values ('li', '写作');  -- 不能插入set中不存在的选项
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
mysql> insert into stu2 values ('li', '编程,写作');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

set类型数据，也可以通过数值的方式来插入，但是这时的数值不表示下标，而是以二进制位1/0表示set的每个选项是否被选择。以上面代码中创建的表stu2的hobby set('编程', '绘画', '游泳', '篮球', '跑步')类型数据为例，二进制位从低到高分别表示 '编程' ~ '跑步'，如图4.1所示，5的二进制表示为00101，对应选择的就是 '编程' 和 '游泳'。

图4.1 以数值的方式插入set类型数据

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

mysql> insert into stu2 values ('wu',1);
Query OK, 1 row affected (0.04 sec)

mysql> insert into stu2 values ('zheng',5);
Query OK, 1 row affected (0.05 sec)

mysql> insert into stu2 values ('wang',7);
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu2 values ('yang',8);
Query OK, 1 row affected (0.04 sec)

mysql> insert into stu2 values ('yang',16);
Query OK, 1 row affected (0.03 sec)

mysql> select * from stu2;
+-------+----------------------+
| name  | hobby                |
+-------+----------------------+
| zhang |                      |
| zhao  | 篮球                 |
| qian  | 跑步                 |
| qian  | 编程                 |
| sun   | 绘画,篮球            |
| li    | 绘画,篮球,跑步       |
| zhou  | 编程                 |
| wu    | 编程                 |
| zheng | 编程,游泳            |
| wang  | 编程,绘画,游泳       |
| yang  | 篮球                 |
| yang  | 跑步                 |
+-------+----------------------+
12 rows in set (0.01 sec)

对于set类型的查找，如果通过where XXX=?的方式来进行查找，则查找的只能是完全匹配的数据，如下代码中，调用select * from stu2 where hobby='编程'，找出来的是hobby仅为编程的行数据，如果hobby中包含'编程'，但是又有其他的选项，那么则无法查找。

mysql> select * from stu2 where hobby='编程';
+------+--------+
| name | hobby  |
+------+--------+
| qian | 编程   |
| zhou | 编程   |
| wu   | 编程   |
+------+--------+
3 rows in set (0.00 sec)

为了克服上面的问题，应当使用MySQL库内置的子集查找函数find_in_set来进行查找。

函数原型：find_in_set(sub, setList)

说明：在setList中查找选项sub，如果sub在setList中存在，那么返回sub在setList中的下标，如果不存在，那么就返回0。

通过find_in_set查询的语法为：

• 查询单个子集：select * from TableName where find_in_set(sub1, setList);
• 查询多个子集同时存在： select * from TableName where find_in_set(sub1, setList) and find_in_set(sub2, setList);

mysql> select * from stu2;
+-------+----------------------+
| name  | hobby                |
+-------+----------------------+
| zhang |                      |
| zhao  | 篮球                 |
| qian  | 跑步                 |
| qian  | 编程                 |
| sun   | 绘画,篮球            |
| li    | 绘画,篮球,跑步       |
| zhou  | 编程                 |
| wu    | 编程                 |
| zheng | 编程,游泳            |
| wang  | 编程,绘画,游泳       |
| yang  | 篮球                 |
| yang  | 跑步                 |
+-------+----------------------+
12 rows in set (0.00 sec)

mysql> select * from stu2 where find_in_set('编程', hobby);
+-------+----------------------+
| name  | hobby                |
+-------+----------------------+
| qian  | 编程                 |
| zhou  | 编程                 |
| wu    | 编程                 |
| zheng | 编程,游泳            |
| wang  | 编程,绘画,游泳       |
+-------+----------------------+
5 rows in set (0.00 sec)

mysql> select * from stu2 where find_in_set('编程',hobby) and find_in_set('绘画', hobby);
+------+----------------------+
| name | hobby                |
+------+----------------------+
| wang | 编程,绘画,游泳       |
+------+----------------------+
1 row in set (0.00 sec)

五. 总结

• MySQL中，数值类型主要包括整形类型、bit类型和小数类型，其中整形类型包括tinyint、smallint、mediumint、int和bigint，每种类型又分为有符号和无符号，占用不同字节的存储空间；bit类型类似于位段，通过二进制位个数确定占用空间的能够表示的数据范围；小数类型包括float、double和deciaml，这几种类型的不同体现在能够表示的数据精度上。
• 字符串类型保留定长字符串char和变长字符串varchar，char相对与varchar，空间利用率低，但是效率更高。
• 日期类型包括date、datetime和timestamp，分别表示日期、日期+时间、时间戳。
• 枚举类型enum为单选类型，一个enum类型数据必须包含一个选项且只能包含一个选项；set为多选类型，一个set类型数据可以包含一个或多个选项，也可以不包括任何一个选项。