整数类型

类型介绍

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT

整数类型	字节	有符号数取值范围	无符号数取值范围
TINYINT	1	-2^7 ~ 2^7-1	0~2^8-1
SMALLINT	2	-2^15 ~ 2^15-1	0~2^16-1
MEDIUMINT	3	-2^23 ~ 2^23-1	0~2^24-1
INT、INTEGER	4	-2^31 ~ 2^31-1	0~2^32-1
BIGINT	8	-2^63 ~ 2^63-1	0~2^64-1

可选属性

M

M表示显示宽度（不是数据宽度），M的取值范围是(0, 255)。该项功能需要配合“ZEROFILL”使用，表示用“0”填满宽度，否则指定显示宽度无效。

注意

如果设置了显示宽度，存入的数据宽度超过宽度，还是按照类型的实际宽度进行保存。
当使用ZEROFILL时，会自动加上UNSIGNED（变为无符号数）

CREATE TABLE test_int1 (
    f1 INT(5),	# 显示宽度为5
    f2 INT(5) ZEROFILL	# 显示宽度为5，不足以0填充
);

INSERT INTO test_int1 (f1, f2)
# 插入数据超过宽度不会报错
VALUES (123, 123), (123456, 123456);

SELECT * FROM test_int1;
+--------+--------+
| f1     | f2     |
+--------+--------+
|    123 |  00123 |
| 123456 | 123456 |
+--------+--------+

UNSIGNED

UNSIGNED表示无符号类型

CREATE TABLE test_int2(
f1 INT UNSIGNED
);

DESC test_int2;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| f1    | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+

浮点类型

类型介绍

MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE

FLOAT：单精度浮点数，4字节
DOUBLE：双精度浮点数，8字节

MySQL中，浮点数不论有没有符号，都会存储符号部分（浮点数存时格式为：符号，阶码，尾数），所以浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半

数据精度说明

MySQL允许使用非标准语法。FLOAT(M,D)或DOUBLE(M,D)。这里，M称为精度，D称为标度。(M,D)中 M=整数位+小数位，D=小数位。 D<=M<=255，0<=D<=30。
FLOAT和DOUBLE不指定(M, D) 时，会按照默认精度显示
不管是否显式设置了精度(M,D)，这里MySQL的处理方案如下：
- 如果存储时，整数部分超出了范围，MySQL就会报错，不允许存这样的值
- 如果存储时，小数点部分若超出范围，就分以下情况：
  - 若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。
  - 若四舍五入后，整数部分超出范围，则MySQL报错，并拒绝处理。

定点数类型

在精度要求更高的场景中，可以使用定点数

类型介绍

MySQL中的定点数类型只有DECIMAL一种类型。

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

使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65，0<=D<=30，D<M。

DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。
定点数在MySQL内部是以字符串的形式进行存储，这就决定了它一定是精准的。
当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

浮点数 vs 定点数

浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景
定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景

推荐使用定点数

位类型

位类型BIT，存储的是二进制值

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

BIT类型，如果没有指定(M)，默认是1位二进制位。

日期与时间类型

MySQL 8.0版本支持的日期和时间类型主要有：YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。

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

类型	字节	日期格式	最小值	最大值
YEAR	1	YYYY	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

YEAR类型

YEAR类型标识年份，需要1个字节的存储空间

以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。

DATE类型

DATE类型表示日期，没有时间部分，格式为YYYY-MM-DD，其中，YYYY表示年份，MM表示月份，DD表示日期。需要3个字节的存储空间

以YYYY-MM-DD格式或者YYYYMMDD格式表示的字符串日期。YYYYMMDD格式会被转化为YYYY-MM-DD格式
使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期

TIME类型

TIME类型用来表示时间，不包含日期部分。在MySQL中，需要3个字节的存储空间来存储TIME类型的数据

可以使用HH:MM:SS格式来表示TIME类型，其中，HH表示小时，MM表示分钟，SS表示秒
使用NOW()，会插入当前系统的时间

DATATIME类型

DATETIME类型表示日期时间，总共需要8个字节的存储空间。

在格式上为DATE类型和TIME类型的组合，可以表示为YYYY-MM-DD HH:MM:SS，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒
使用函数NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间

TIMESTAMP类型

TIMESTAMP类型也可以表示日期时间，需要4个字节的存储空间。使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。

其显示格式与DATETIME类型相同，都是YYYY-MM-DD HH:MM:SS
但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间
使用函数NOW()，可以向TIMESTAMP类型的字段插入系统的当前日期和时间

DATATIME vs TIMESTAMP

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

文本字符串类型

CHAR和VARCHAR类型

CHAR和VARCHAR类型都可以存储比较短的字符串

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

注意：M最大65535字节，若使用UTF-8字符集，则M最大为65535/3

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类型的字段所占用的存储空间为字符串实际长度加1个字节

InnoDB存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于CHAR平均占用的空间多于VARCHAR，所以除了简短并且固定长度的，其他考虑VERCHAR。这样节省空间，对磁盘I/O和数据存储总量比较好

TEXT类型

TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT 类型。

在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和 VARCHAR类型相同。

文本字符串类型	特点	长度	长度范围	占用的存储空间
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类型的数据尾部的空格。

ENUM类型

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

其所需要的存储空间由定义ENUM类型时指定的成员个数决定

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

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

1
2
3

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

SET类型

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

成员个数范围（L表示实际成员个数）	占用的存储空间
1 <= L <= 8	1个字节
9 <= L <= 16	2个字节
17 <= L <= 24	3个字节
25 <= L <= 32	4个字节
33 <= L <= 64	8个字节

SET类型在存储数据时成员个数越多，其占用的存储空间越大

1
2
3

CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);

二进制字符串类型

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

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)**，否则报错。

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

BLOB类型

BLOB是一个二进制大对象，可以容纳可变数量的数据。

MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如图片、音频和视频等。

注意，在实际工作中，往往不会在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 个字节

JSON类型

JSON是一种轻量级数据交换格式。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。

它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。

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

CREATE TABLE test_json(
	js JSON
);

# 向表中插入JSON数据
INSERT INTO test_json (js) 
VALUES ('{"name":"DawnZz", "age":22, "address":{"province":"beijing", "city":"beijing"}}');

当需要检索JSON类型的字段中数据的某个具体值时，可以使用->符号

SELECT js -> '$.name' AS NAME,
	js -> '$.age' AS age ,
	js -> '$.address.province' AS province, 
	js -> '$.address.city' AS city
FROM test_json;
+----------+------+-----------+-----------+
| NAME     | age  | province  | city      |
+----------+------+-----------+-----------+
| "DawnZz" | 22   | "beijing" | "beijing" |
+----------+------+-----------+-----------+

小结

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

这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的，可靠性好，并不意味着高效。

Dawn's Blogs

MySQL基础 (13) 数据类型

整数类型

类型介绍

可选属性

M

UNSIGNED

浮点类型

类型介绍

数据精度说明

定点数类型

类型介绍

浮点数 vs 定点数

位类型

日期与时间类型

YEAR类型

DATE类型

TIME类型

DATATIME类型

TIMESTAMP类型

DATATIME vs TIMESTAMP

文本字符串类型

CHAR和VARCHAR类型

TEXT类型

ENUM类型

SET类型

二进制字符串类型

BINARY和VARBINARY

BLOB类型

JSON类型

小结