【MySQL】数据类型

---

一、MySQL中的数据类型

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值类型：GEOMETRY、POINT、LINESTRING、POLYGON； 集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

常见数据类型的属性，如下：

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

二、整数类型

整数类型	字节	有符号数取值范围	无符号数取值范围
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT、INTEGER	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

可选属性：

2.1. M

表示显示宽度，M的取值范围是(0, 255)。

显示宽度与类型可以存储的值范围无关
需要配合“ZEROFILL”使用，表示用“0”填满宽度，否则指定显示宽度无效。
不指定每种类型都会有默认的
从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。

举例：MySQL5.7中显式如下，MySQL8中不再显式范围

CREATE TABLE test_int2(
f1 INT,
f2 INT(5),
f3 INT(5) ZEROFILL
)

mysql> SELECT * FROM test_int2;
+--------+--------+--------+
| f1     | f2     | f3     |
+--------+--------+--------+
|      1 |    123 |  00123 |
| 123456 | 123456 |   NULL |
| 123456 | 123456 | 123456 |
+--------+--------+--------+
3 rows in set (0.00 sec)

2.2 UNSIGNED

UNSIGNED 无符号类型（非负）

所有的整数类型都有一个可选的属性UNSIGNED（无符号属性）
无符号整数类型的最小取值为0
需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型

int类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)。因为负号占一个数字位。

CREATE TABLE test_int3(
f1 INT UNSIGNED
);

mysql> desc test_int3;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| f1    | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

2.3 ZEROFILL

zerofill 补零（即0填充）
某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性

---

2.4 总结与适用场景

int(M)，必须和UNSIGNED ZEROFILL一起使用才有意义

如果整数值超过M位，就按照实际位数存储，只是无须再用字符 0 进行填充
M 的值跟 int(M) 所占多少存储空间并无任何关系

TINYINT：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。

SMALLINT：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。

MEDIUMINT：用于较大整数的计算，比如车站每日的客流量等。

INT、INTEGER：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。

BIGINT：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

系统故障产生的成本远远超过增加几个字段存储空间所产生的成本

三、浮点类型

浮点数和定点数类型的特点是可以处理小数

• FLOAT 表示单精度浮点数；
• DOUBLE 表示双精度浮点数；
  

FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。
无论有没有符号，MySQL 的浮点数都会存储表示符号的部分。

浮点数类型有个缺陷，就是不精准。
（因为是使用二进制的方式来进行存储，无法用一个二进制数来精确表达）
只好在取值允许的范围内进行四舍五入
因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等

四、定点数 DECIMAL

数据类型	字节数	含义
DECIMAL(M,D),DEC,NUMERIC	M+2字节	有效范围由M和D决定

使用 DECIMAL(M,D) 的方式表示高精度小数。
其中，M被称为精度，D被称为标度。
0<=M<=65，0<=D<=30，D<M。
例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。

• DECIMAL(M,D)的最大取值范围与DOUBLE类型一样
• 定点数在MySQL内部是以字符串的形式进行存储，这就决定了它一定是精准的。
• 不指定精度和标度时，其默认为DECIMAL(10,0)
• 浮点数 vs 定点数
  • 浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）
  • 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景 （比如涉及金额计算的场景）

五、日期与时间类型

• YEAR类型通常用来表示年
• DATE类型通常用来表示年、月、日
• TIME类型通常用来表示时、分、秒
• DATETIME类型通常用来表示年、月、日、时、分、秒
• TIMESTAMP类型通常用来表示带时区的年、月、日、时、分、秒

类型	名称	字节	日期格式	最小值	最大值
YEAR	年	1	YYYY或YY	1901	2155
TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

为什么时间类型 TIME 的取值范围不是 -23:59:59～23:59:59 呢？
原因是 MySQL 设计的 TIME 类型，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。

1. YEAR

• MySQL5.5.27开始 , 2位格式的YEAR已经不推荐使用，默认格式为YYYY，
• 最小值为1901，最大值为2155
• 从MySQL 8.0.19开始，不推荐使用指定显示宽度，直接 YEAR

• 当取值为01到69时，表示2001到2069；
• 当取值为70到99时，表示1970到1999；
• 当取值整数的0或00添加的话，那么是0000年；
• 当取值是日期/字符串的’0’添加的话，是2000年。

举例：

INSERT INTO test_year
VALUES('45','71');

INSERT INTO test_year
VALUES(0,'0');

mysql> SELECT * FROM test_year;
+------+------+
| f1   | f2   |
+------+------+
| 2020 | 2021 |
| 2045 | 1971 |
| 0000 | 2000 |
+------+------+
3 rows in set (0.00 sec)

2. TIME

• 可以使用带有冒号的字符串，比如’D HH:MM:SS'、‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、'D HH’或’SS’格式，都能被正确地插入TIME类型的字段中。
  • 其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH。
  • 当使用带有冒号并且不带D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10。
• 可以使用不带有冒号的字符串或者数字，格式为’HHMMSS’或者HHMMSS。
  • 如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储。
  • 比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10，而不是12:10:00。
• 使用CURRENT_TIME()或者NOW()，会插入当前系统的时间

举例：

INSERT INTO test_time1
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');

INSERT INTO test_time1
VALUES ('123520'), (124011),(1210);

INSERT INTO test_time1
VALUES (NOW()), (CURRENT_TIME());

3. DATE

• 以YYYY-MM-DD格式或者YYYYMMDD格式表示的字符串日期，
  • 其最小取值为1000-01-01，最大取值为9999-12-03。
  • YYYYMMDD格式会被转化为YYYY-MM-DD格式。
• 以YY-MM-DD格式或者YYMMDD格式表示的字符串日期，
  • 此格式中，年份为两位数值或字符串满足YEAR类型的格式条件为：
    • 当年份取值为00到69时，会被转化为2000到2069；
    • 当年份取值为70到99时，会被转化为1970到1999。
• 使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期

举例：

INSERT INTO test_date1
VALUES ('2020-10-01'), ('20201001'),(20201001);

INSERT INTO test_date1
VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'), ('99-01-01'), ('990101');

INSERT INTO test_date1
VALUES (000301), (690301), (700301), (990301); 

INSERT INTO test_date1
VALUES (CURRENT_DATE()), (NOW());

4. DATETIME

• 以YYYY-MM-DD HH:MM:SS格式或者YYYYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
  • 以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式。
• 以YY-MM-DD HH:MM:SS格式或者YYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则，00到69表示2000到2069；70到99表示1970到1999。
• 使用函数CURRENT_TIMESTAMP()和NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间

举例：

INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');

INSERT INTO test_datetime1
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'), ('200101000000');

INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
 
INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()), (NOW());

5. TIMESTAMP

TIMESTAMP存储的时间范围比DATETIME要小很多，
只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。

其中，UTC表示世界统一时间，也叫作世界标准时间。
存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。
因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间

#修改当前的时区
SET time_zone = '+9:00';

---

总结

TIMESTAMP和DATETIME的区别：

• TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
• 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
• TIMESTAMP和时区有关。
  • TIMESTAMP会根据用户的时区不同，显示不同的结果。
  • 而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

INSERT INTO temp_time VALUES('2021-9-2 14:45:52','2021-9-2 14:45:52');

INSERT INTO temp_time VALUES(NOW(),NOW());

mysql> SELECT * FROM temp_time;
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 2021-09-02 14:45:52 | 2021-09-02 14:45:52 |
| 2021-11-03 17:38:17 | 2021-11-03 17:38:17 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

使用场景：
用的最多的日期时间类型，就是 DATETIME

此外，一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用时间戳，因为DATETIME虽然直观，但不便于计算。

mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1635932762 |
+------------------+
1 row in set (0.00 sec)

六、文本字符串类型

CHAR与VARCHAR类型

字符串(文本)类型	特点	长度	长度范围	占用的存储空间
CHAR(M)	固定长度	M	0 <= M <= 255	M个字节
VARCHAR(M)	可变长度	M	0 <= M <= 65535	(实际长度 + 1) 个字节

CHAR类型

• CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
• 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
• 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。

VARCHAR类型：

• VARCHAR(M) 定义时，必须指定长度M，否则报错。
• MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。
• 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

对比：

类型	特点	空间上	时间上	适用场景
CHAR(M)	固定长度	浪费存储空间	效率高	存储不大，速度要求高
VARCHAR(M)	可变长度	节省存储空间	效率低	非CHAR的情况

情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的，结果得不偿失。

情况2：固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。

情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

情况4：具体存储引擎中的情况

TEXT类型

文本字符串类型	特点	长度	长度范围	占用的存储空间
TINYTEXT	小文本、可变长度	L	0 <= L <= 255	L + 2 个字节
TEXT	文本、可变长度	L	0 <= L <= 65535	L + 2 个字节
MEDIUMTEXT	中等文本、可变长度	L	0 <= L <= 16777215	L + 3 个字节
LONGTEXT	大文本、可变长度	L	0 <= L<= 4294967295（相当于4GB）	L + 4 个字节

由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。
遇到这种情况，只能采用 CHAR(M)，或者 VARCHAR(M)。

总结：

TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR，VARCHAR来代替。
还有TEXT类型不用加默认值，加了也没用。
而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，
所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表。

七、二进制字符串类型

MySQL中的二进制字符串类型主要存储一些二进制数据，
比如可以存储图片、音频和视频等二进制数据。

二进制字符串类型	特点	值的长度	占用空间
BINARY(M)	固定长度	M （0 <= M <= 255）	M个字节
VARBINARY(M)	可变长度	M（0 <= M <= 65535）	M+1个字节

BINARY与VARBINARY类型
BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。

BINARY (M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。
如果未指定(M)，表示只能存储1个字节。
例如BINARY (8)，表示最多能存储8个字节，
如果字段值不足(M)个字节，将在右边填充’\0’以补齐指定长度。

VARBINARY (M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，
另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，
还需要1或2个字节来存储数据的字节数。
VARBINARY类型必须指定(M)，否则报错。

举例：

CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3),
# f3 VARBINARY,
f4 VARBINARY(10)
);

INSERT INTO test_binary1(f1,f2)
VALUES('a','a');

INSERT INTO test_binary1(f1,f2)
VALUES('尚','尚');#失败

---

BLOB类型
BLOB是一个二进制大对象，可以容纳可变数量的数据。
通常不会在MySQL数据库中使用BLOB类型存储大对象数据，而是会将图片、音频和视频文件存储到服务器的磁盘上，并将图片、音频和视频的访问路径存储到MySQL中

二进制字符串类型	值的长度	长度范围	占用空间
TINYBLOB	L	0 <= L <= 255	L + 1 个字节
BLOB	L	0 <= L <= 65535（相当于64KB）	L + 2 个字节
MEDIUMBLOB	L	0 <= L <= 16777215 （相当于16MB）	L + 3 个字节
LONGBLOB	L	0 <= L <= 4294967295（相当于4GB）	L + 4 个字节

八、ENUM类型

ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。
设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。

文本字符串类型	长度	长度范围	占用的存储空间
ENUM	L	1 <= L <= 65535	1或2个字节

• 当ENUM类型包含1～255个成员时，需要1个字节的存储空间；
• 当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。
• ENUM类型的成员个数的上限为65535个。

举例：

CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);

INSERT INTO test_enum
VALUES('春'),('秋');

# 忽略大小写
INSERT INTO test_enum
VALUES('UNKNOW');

# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum
VALUES('1'),(3);

# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');

# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);

九、JSON 类型

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式。

JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。

举例：

CREATE TABLE test_json(
js json
);

INSERT INTO test_json (js) 
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');

十、BIT 位类型

BIT类型中存储的是二进制值，类似010110。

二进制字符串类型	长度	长度范围	占用空间
BIT(M)	M	1 <= M <= 64	约为(M + 7)/8个字节

BIT类型，如果没有指定(M)，默认是1位。这个1位，表示只能存1位的二进制值。
这里(M)是表示二进制的位数，位数最小值为1，最大值为64。

十一、SET类型

SET表示一个字符串对象，可以包含0个或多个成员，但成员个数的上限为64。
设置字段值时，可以取取值范围内的 0 个或多个值。

十二、空间类型

MySQL 空间类型扩展支持地理特征的生成、存储和分析
MySQL中使用Geometry（几何）来表示所有地理特征。Geometry指一个点或点的集合，代表世界上任何具有位置的事物。

---

总结

在定义数据类型时：

• 如果确定是整数，就用 INT；
• 如果是小数，一定用定点数类型 DECIMAL(M,D)；
• 如果是日期与时间，就用 DATETIME。

这样做的好处是，首先确保你的系统不会因为数据类型定义出错。
不过，凡事都是有两面的，可靠性好，并不意味着高效。
比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。

关于字符串的选择，参考阿里巴巴的《Java开发手册》规范：

阿里巴巴《Java开发手册》之MySQL数据库：

• 任何字段如果为非负数，必须是 UNSIGNED
• 【强制】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。
  • 说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并分开存储。
• 【强制】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。
• 【强制】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。