如何为 MySQL 表中的字段选择合适的数据类型

大家好，我是只谈技术不剪发的 Tony 老师。

在设计数据库时，需要为表中的每个字段指定一个数据类型。数据类型决定了字段中允许存储的数据以及支持的操作，例如字符串允许的最大长度、数字类型可以进行算术运算等；另一方面，不同的类型占用的存储空间和处理性能也不同。因此，本文给大家介绍一下 MySQL 支持的各种数据类型，以及设计表时如何选择合适的字段类型。

常见数据类型

MySQL 实现了 SQL 标准中定义的大部分数据类型，主要可以分为以下几类：数字类型、字符串类型、日期和时间类型、JSON 数据类型以及空间类型。

数字类型

MySQL 实现了 SQL 标准中的精确数字类型和近似数字类型，包括整数（INTEGER、SMALLINT）、定点数（DECIMAL、NUMERIC）和浮点数（FLOAT、REAL、DOUBLE PRECISION）。这些数字类型又可以分为有符号类型和无符号类型。

📝关于数字类型的算术运算和数值处理的函数，可以参考这篇文章。

整数数字

下表列出了 MySQL 中的所有整数类型：

整数类型的定义如下：

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
INT[(M)] [UNSIGNED] [ZEROFILL]
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
BIGINT[(M)] [UNSIGNED] [ZEROFILL]

其中，INT 是 INTEGER 的同义词。M 表示的是显示宽度，不会影响数据的存储；如果实际数据小于指定宽度，可以指定 ZEROFILL 在左侧使用 0 填充显示。UNSIGNED 表示无符号整数；如果指定了 ZEROFILL，MySQL 会自动加上 UNSIGNED。例如：

DROP TABLE IF EXISTS t;
CREATE TABLE t(id int(4) ZEROFILL);
INSERT INTO t VALUES (1), (99999);

mysql> SELECT * FROM t;
+-------+
| id    |
+-------+
|  0001 |
| 99999 |
+-------+
2 rows in set (0.00 sec)

mysql> desc t;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id    | int(4) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

⚠️从 MySQL 8.0.17 开始，整数类型的显示宽度 M 和 ZEROFILL 选项已经被弃用，将来的版本中会删除。可以考虑使用其他方法实现相同的效果，例如使用 LPAD() 函数将数字填充至指定宽度，或者使用 CHAR 类型存储格式化的数字。

对于 BIGINT 类型，需要注意以下内容：

• SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的同义词；
• 所有的算术运算都使用有符号的 BIGINT 或者 DOUBLE 数据进行运算，所以不要使用大于 9223372036854775807 的无符号大整数参与运算，除非使用位函数和运算符。如果参与了运算，由于将 BIGINT 转换为 DOUBLE 时存在舍入错误，可能会导致结果中的最后几位数字出错。MySQL 会在以下情况中使用 BIGINT：使用整数类型存储 BIGINT 字段中的无符号大整数；针对 BIGINT 字段的 MIN() 或者 MAX() 函数；两个整数的算术运算（+、-、* 等）；
• 可以将 BIGINT 数据存储为字符串， MySQL 在运算时会执行字符串到数字的类型转换，而不会涉及到双精度格式的中间结果。
• 当两个操作数都是整数类型时，-、+ 和 * 运算符使用 BIGINT 算术。这意味着如果将两个大整数（或者返回整数值的函数）相乘，如果结果大于 9223372036854775807，可能会得到一个异常的结果。

定点数字

MySQL 使用 DECIMAL 和 NUMERIC 类型存储精确的数字值，通常用于需要保留完整精确的字段，例如财务系统中的货币余额。MySQL 中的 NUMERIC 和 DECIMAL 时同义词。

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

其中，DEC 和 FIXED 是 DECIMAL 的同义词。M 表示可以存储的总位数（精度），最大取值为 65，默认值为 10，小数点和负号（-）不算位数；D 是小数点后的位数（刻度），最大取值为 30 并且小于等于 M，默认值为 0（表示整数）。ZEROFILL 表示如果实际数据小于指定宽度，显示时在左侧使用 0 填充。UNSIGNED 表示无符号数字；如果指定了 ZEROFILL，MySQL 会自动加上 UNSIGNED。例如：

DROP TABLE IF EXISTS t;
CREATE TABLE t(salary DECIMAL(5,2));
INSERT INTO t VALUES (-999.99), (0), (999.99);

mysql> INSERT INTO t VALUES (1000);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1

mysql> INSERT INTO t VALUES (0.001);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1265 | Data truncated for column 'salary' at row 1 |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t;
+---------+
| salary  |
+---------+
| -999.99 |
|    0.00 |
|  999.99 |
|    0.00 |
+---------+
4 rows in set (0.00 sec)

其中，salary 字段的精度为 5，刻度为 2。因此，该字段可以存储 -999.99 到 -999.99 之间的数值。此时，插入超过精度的数据返回错误，插入超过刻度的数据会进行四舍五入。

MySQL 中的 DECIMAL 和 DECIMAL(M) 等价于 DECIMAL(M,0)，M 的默认值为 10。

⚠️从 MySQL 8.0.17 开始，DECIMAL 类型的 UNSIGNED 选项已经被弃用，将来的版本中会删除。可以考虑使用 CHECK 约束实现相同的功能。

DECIMAL 数据使用二进制格式进行存储，9 位十进制数字存储为 4 个字节，整数部分和小数部分的数值分别存储。每 9 位数字需要 4 字节存储，剩余的位数需要的存储空间如下表所示：

例如，DECIMAL(18,9) 在小数点两边各有 9 位，所以整数部分和小数部分各需要 4 字节存储；DECIMAL(20,6) 拥有 14 位整数和 6 位小数，整数部分需要 4 字节（9 位数字）加上 3 字节（7 位数字），6 位小数部分需要 3 字节存储。

DECIMAL 字段不会存储前置的 +、- 字符或者 0。如果为 DECIMAL(5,1) 字段插入 +0003.1，最终会存储 3.1。对于负数，不会存储负号。

浮点数字

MySQL 使用 FLOAT 和 DOUBLE 类型表示近似数字，对于单精度使用 4 字节存储，双精度使用 8 字节存储。

FLOAT(p) [UNSIGNED] [ZEROFILL]

其中，p 是以比特为单位的精度。MySQL 中的 p 只用于决定最终类型为 FLOAT 还是 DOUBLE，如果 p 的值为 0 到 24 则为 FLOAT，如果 p 的值为 25 到 53 则为 DOUBLE；ZEROFILL 表示如果实际数据小于指定宽度，显示时在左侧使用 0 填充。UNSIGNED 表示无符号数字；如果指定了 ZEROFILL，MySQL 会自动加上 UNSIGNED。

对于单精度 FLOAT 类型，理论上支持 -3.402823466E+38 到 -1.175494351E-38、0、以及 1.175494351E-38 到 3.402823466E+38 之间的数值，实际上取决于硬件和操作系统。

对于双精度 DOUBLE 类型，理论上支持 -1.7976931348623157E+308 到 -2.2250738585072014E-308、0 以及 2.2250738585072014E-308 到 1.7976931348623157E+308 之间的数值，实际上取决于硬件和操作系统。

📝DOUBLE 是 DOUBLE PRECISION 的同义词。如果指定了 SQL 模式 REAL_AS_FLOAT，REAL 是 FLOAT 的同义词；否则它是 DOUBLE PRECISION 的同义词。

由于浮点类型存储的是近似数值而不是精确数值，如果尝试将它们进行比较可能会返回错误的结果，而且结果和平台或者具体实现相关。例如：

mysql> SELECT (1.0/3)*3, (1.0/3)*3=1.0;
+-----------+---------------+
| (1.0/3)*3 | (1.0/3)*3=1.0 |
+-----------+---------------+
|   1.00000 |             0 |
+-----------+---------------+
1 row in set (0.00 sec)

虽然 (1.0/3)*3 的结果显示为 1.00000，但是在系统内部它并不等于 1.0。

📝为了方便移植，存储近似数值的数据类型应该使用 FLOAT 或者 DOUBLE PRECISION 定义，而不需要指定精度或者位数。

除了以上定义之外，MySQL 还提供了一些非标准的语法：

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]
REAL[(M,D)] [UNSIGNED] [ZEROFILL]

其中，M 表示可以存储的总位数（精度），D 是小数点后的位数（刻度）；如果省略 M 和 D，具体的范围取决于硬件实现，单精度浮点数大概支持 7 位小数，双精度浮点数大概支持 15 位小数。例如，FLOAT(7,4) 类型显示时的内容可能为 -999.9999。MySQL 存储数据值会进行舍入，如果插入 999.00009，近似的结果为 999.0001。

⚠️从 MySQL 8.0.17 开始，FLOAT、DOUBLE 类型的 UNSIGNED 属性已经被弃用，将来的版本中会删除。可以考虑使用 CHECK 约束实现相同的功能。从 MySQL 8.0.17 开始，非标准的 FLOAT(M,D) 和 DOUBLE(M,D) s语法已经被弃用，将来的版本中会删除。

BIT 类型

MySQL 中的 InnoDB、MyISAM、MEMORY 以及 NDB 存储引擎支持特殊的位类型（BIT），用于存储比特数据值。

BIT(M)

其中，M 表示比特个数，范围从 1 到 64，默认为 1；BIT(M) 大概需要 (M+7)/8 字节的存储。例如：

CREATE TABLE work_days(
    year INT,
    week INT,
    days BIT(7),
    PRIMARY KEY(year, week)
);
INSERT INTO work_days
VALUES(2020, 1, b'0110110');

其中，days 表示这一周中的每一天是否是工作日，1 表示工作日，0 表示周末或者假期。INSERT 语句表示 2020 年第一周的周日、周三和周六是休息日。

位类型的常量可以使用以下格式指定：

b'111'
B'111'
0b111

以上数据表示十进制中的 3。查询时可以使用 BIN 函数进行转换显示：

mysql> select year, week, bin(days) from work_days;
+------+------+-----------+
| year | week | bin(days) |
+------+------+-----------+
| 2020 |    1 | 110110    |
+------+------+-----------+
1 row in set (0.00 sec)

布尔类型

MySQL 没有提供内置的 BOOLEAN 或者 BOOL 数据类型，而是使用 TINYINT(1) 进行表示。以下三种语法等价：

BOOL
BOOLEAN
TINYINT(1)

在 MySQL 中，0 被看作 false，非 0 的数值被看作 true。布尔类型的常量可以使用 TRUE 和 FALSE 表示，结果分别为 1 和 0。

mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false                  |
+------------------------+

mysql> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

mysql> SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

不过， TRUE 和 FALSE 仅仅是 1 和 0 的别名。例如：

mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true                           |
+--------------------------------+

mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true                          |
+-------------------------------+

mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false                         |
+-------------------------------+

mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false                          |
+--------------------------------+

最后两个语句都返回了 false，因为 2 即不等于 1，也不等于 0。

字符串类型

MySQL 字符串类型用于存储字符和字符串数据，包括二进制数据，例如图片或者文件。字符串数据可以支持比较运算符和模式匹配运算符，例如 LIKE、正则表达式匹配以及全文检索。

MySQL 支持的字符串类型包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 以及 SET，其中 CHAR、VARCHAR、TEXT、ENUM 以及 SET 包含字符集（Charset）和排序规则（Collation）属性，默认继承表的字符集和排序规则。MySQL 8.0 默认使用 utf8mb4 字符集。

📝关于字符串处理和分析的函数和操作符，可以参考这篇文章。

CHAR

CHAR(n) 和 CHARACTER(n) 类型表示长度固定的字符串，其中 n 表示字符串中字符的最大数量，取值范围从 0 到 255。例如：

CREATE TABLE t (c1 CHAR, c2 CHAR(5));
INSERT INTO t VALUES ('a','a');

其中，c1 只能存储 1 个字符；c2 最多能够存储 5 个字符。对于定长字符串，如果输入的字符串长度不够，将会使用空格进行填充。因此，字段 c2 中实际存储的内容为“a”加上 4 个空格。

mysql> select concat(c1, '!'), concat(c2, '!') from t;
+-----------------+-----------------+
| concat(c1, '!') | concat(c2, '!') |
+-----------------+-----------------+
| a!              | a!              |
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select concat(c1, '!'), concat(c2, '!') from t;
+-----------------+-----------------+
| concat(c1, '!') | concat(c2, '!') |
+-----------------+-----------------+
| a!              | a    !          |
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = default;
Query OK, 0 rows affected (0.00 sec)

默认情况下没有设置 SQL 模式 PAD_CHAR_TO_FULL_LENGTH，MySQL 读取 CHAR 字段时自动截断了尾部的空格。

MySQL 使用比较运算符（=、<>、>、< 等）和 LIKE 操作符比较和匹配 CHAR 字段数据时不考虑尾部的空格。例如：

mysql> select c2 from t where c2='a';
+------+
| c2   |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> select c2 from t where c2 like 'a    ';
Empty set (0.00 sec)

📝通常来说，只有存储固定长度的数据时，才会考虑使用定长字符串类型。例如 18 位身份证，6 位邮政编码等。

VARCHAR

VARCHAR(n) 和 CHARACTER VARYING(n) 类型表示长度不固定的字符串，其中 n 表示字符串中字符的最大数量，取值范围从 0 到 65535。例如：

DROP TABLE IF EXISTS t;
CREATE TABLE t (c VARCHAR(5));
INSERT INTO t VALUES ('a    '), ('abcde');

字段 c 最多存储 5 个字符。

📝VARCHAR 字段的实际最大长度受限于最大的行大小（65536 字节，所有字段长度之和）以及字符集。例如，utf8mb4 字符集中的一个字符最多占用 4 个字节，因此这种字符集的 VARCHAR 字段可以声明的最大长度为 16383。

对于变长字符串，如果输入的字符串长度不够，存储实际的内容。例如类型为 VARCHAR(5) 的字段，如果输入值为“a”，实际存储的内容为“a”。SQL 模式 PAD_CHAR_TO_FULL_LENGTH 对 VARCHAR 字段没有影响，MySQL 读取 VARCHAR 字段时不会截断尾部的空格。

mysql> select c,length(c) from t;
+-------+-----------+
| c     | length(c) |
+-------+-----------+
| a     |         5 |
| abcde |         5 |
+-------+-----------+
2 rows in set (0.00 sec)

MySQL 存储的 VARCHAR 数据包括 1 字节或 2 字节的长度信息前缀加上具体数据，长度前缀标识了数据的字节数。如果 VARCHAR 字段的数据小于等于 255 字节，使用 1 个字节存储长度；如果数据可能大于等于 256 字节，使用 2 个字节存储长度。

📝通常来说，变长字符串类型一般用于存储长度不固定的内容，例如名字、电子邮箱、产品描述等。

TEXT

MySQL 提供了 4 种形式的 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 以及 LONGTEXT。

• TINYTEXT，最大长度为 255 个字节，类似于 VARCHAR(255)。每个 TINYTEXT 值需要 1 字节额外的存储表示长度；
• TEXT，最大长度为 65535 个字节，类似于 VARCHAR(65535)。每个 TEXT 值需要 2 字节额外的存储表示长度；
• MEDIUMTEXT，最大长度为 16777215 个字节。每个 MEDIUMTEXT 值需要 3 字节额外的存储表示长度；
• LONGTEXT，最大长度为 4294967295 个字节。每个 LONGTEXT 值需要 4 字节额外的存储表示长度。

TEXT 类型可以用于存储长文本字符串，长度支持 1 字节到 4 GB；但是 MySQL 不会在服务器内存中缓存 TEXT 数据，而是从磁盘中读取，所有访问时比 CHAR 和 VARCHAR 类型更慢一些。MySQL 插入或者查询时不会对 TEXT 数据尾部空格进行任何处理。

📝TEXT 数据类型通常用于存储文章内容、产品描述等信息。

二进制字符串

BINARY(M) 和 VARBINARY(M) 类型与 CHAR 和 VARCHAR 类型类似，但是存储的内容为二进制字节串，而不是普通字符串。其中 M 表示最大的字节长度，分别为 255 和 65535。这两种类型使用 binary 字符集和排序规则，基于字节数值进行比较和排序。

存储 BINARY 数据时，在尾部使用 0x00（字节 0）填充到指定长度，查询时不会删除尾部的 0 字节。所有的字节对应比较操作都有意义，包括 ORDER BY 和 DISTINCT 操作，0x00 和空格比较的结果不相等，0x00 的排序在空格前面。

DROP TABLE IF EXISTS t;
CREATE TABLE t (c BINARY(3));
INSERT INTO t VALUES ('a');

mysql> SELECT * FROM t where c='a';
Empty set (0.00 sec)

mysql> SELECT * FROM t where c='a\0\0';
+------------+
| c          |
+------------+
| 0x610000   |
+------------+
1 row in set (0.00 sec)

存储 VARBINARY 数据时，不会使用 0x00（字节 0）填充，查询时不会删除尾部的 0 字节。所有的字节对应比较操作都有意义，包括 ORDER BY 和 DISTINCT 操作，0x00 和空格比较的结果不相等，0x00 的排序在空格前面。

BINARY(M) 和 VARBINARY(M) 类型与 CHAR 和 VARCHAR 类型的存储需求也类似，但是以字节为单位。

BLOB

MySQL 提供了 4 种形式的 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 以及 LONGBLOB。

• TINYBLOB，最大长度为 255 个字节，类似于 VARBINARY(255)。每个 TINYBLOB 值需要 1 字节额外的存储表示长度；
• BLOB，最大长度为 65535 个字节，类似于 VARBINARY(65535)。每个 BLOB 值需要 2 字节额外的存储表示长度；
• MEDIUMBLOB，最大长度为 16777215 个字节。每个 MEDIUMBLOB 值需要 3 字节额外的存储表示长度；
• LONGBLOB，最大长度为 4294967295 个字节。每个 LONGBLOB 值需要 4 字节额外的存储表示长度。

BLOB 类型可以用于存储二进制大对象，长度支持 1 字节到 4 GB；BLOB 类型使用 binary 字符集和排序规则，基于字节数值进行比较和排序。MySQL 不会在服务器内存中缓存 BLOB 数据，而是从磁盘中读取，所有访问时比 BINARY 和 VARBINARY 类型更慢一些。MySQL 插入或者查询时不会对 BLOB 数据尾部空格进行任何处理。

📝BLOB 数据类型通常用于存储图片、文档、视频等信息。

ENUM

ENUM(‘value1’,‘value2’,…) 类型定义了一个枚举，即取值限定为 ‘value1’、‘value2’、…、NULL 或者 ‘’ 之一的字符串对象。ENUM 数据在内部使用整数表示，最多包含 65535 个不同的值。

每个枚举元素最大的长度为 M <= 255 并且 (M x w) <= 1020，其中 M 是元素的字面长度，w 是字符集中字符可能占用的最大字节数。

使用枚举类型的优势在于：

• 在字段的取值有限时提供紧凑的数据存储，枚举在内部使用整数表示，需要 1 字节或 2 字节存储；
• 查询结果的可读性，内部整数在 查询结果中显示为相应的字符串。

例如：

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'), ('polo shirt','small');

SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt | medium |
+---------+--------+

如果插入 100 万条 ‘medium’ 数据，需要 100 万字节存储；如果直接使用 VARCHAR 类型，需要 6 倍存储。

另一方面，使用枚举类型时需要注意枚举值的排序使用内部的索引数字，而不是字符串。例如，对于 ENUM(‘b’, ‘a’) 字符 b 排在 a 之前。

SET

SET(‘value1’,‘value2’,…) 类型定义了一个集合，即取值限定为 ‘value1’、‘value2’、…中零个或多个的字符串对象。SET 数据在内部使用整数表示，最多包含 64 个不同的成员。

每个集合元素最大的长度为 M <= 255 并且 (M x w) <= 1020，其中 M 是元素的字面长度，w 是字符集中字符可能占用的最大字节数。

例如：

DROP TABLE IF EXISTS t;
CREATE TABLE t (c SET('a','b'));
INSERT INTO t VALUES (''),('a'),('b'),('a,b');

mysql> SELECT c+0 FROM t;
+------+
| c+0  |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
+------+
4 rows in set (0.00 sec)

SET 对象的存储空间由集合成员的个数决定；如果个数为 N，对象占用 (N+7)/8 字节，向上取整为 1、2、3、4 或者 8 字节。

日期时间类型

MySQL 提供了以下存储时间值的数据类型：DATE、TIME、DATETIME、TIMESTAMP 以及 YEAR。其中，TIME、DATETIME、TIMESTAMP 支持小数秒，最多 6 位小数（微秒）。

📝关于日期时间数据处理的函数和操作符，可以参考这篇文章。

日期类型

DATE 表示日期类型，支持的范围从 ‘1000-01-01’ 到 ‘9999-12-31’，占用 3 个字节。DATE 数据的显示格式为 ‘YYYY-MM-DD’。例如：

DROP TABLE IF EXISTS t;
CREATE TABLE t (birth_date date);
INSERT INTO t VALUES ('2020-10-01');

mysql> SELECT * FROM t;
+------------+
| birth_date |
+------------+
| 2020-10-01 |
+------------+
1 row in set (0.00 sec)

MySQL 使用 4 位数字存储日期数据中的年份，如果输入 2 位年份，将会使用以下规则：

• 00-69 之间的年份转换为 2000-2069；
• 70-99 之间的年份转换为 1970 – 1999。

例如：

INSERT INTO t VALUES ('01-10-31'), ('81-10-31');

mysql> SELECT * FROM t;
+------------+
| birth_date |
+------------+
| 2020-10-01 |
| 2001-10-31 |
| 1981-10-31 |
+------------+
3 rows in set (0.00 sec)

以上规则同样适用于其他数据类型中的年份信息，包括 DATETIME、TIMESTAMP 以及 YEAR。

时间类型

MySQL 使用 TIME 类型表示一天中的时间，格式为 ‘HH:MM:SS’，范围小于 24 小时。另外，也可以使用 TIME 表示两个事件之间的时间间隔，格式为 ‘hhh:mm:ss’，范围从 ‘-838:59:59’ 到 ‘838:59:59’。TIME 类型需要 3 字节的存储。例如：

DROP TABLE IF EXISTS t;
CREATE TABLE t (start_time time, end_time time);
INSERT INTO t VALUES ('09:00:00', '10:00:00');

mysql> SELECT * FROM t;
+------------+----------+
| start_time | end_time |
+------------+----------+
| 09:00:00   | 10:00:00 |
+------------+----------+
1 row in set (0.00 sec)

MySQL 使用 TIME(N) 表示包含小数部分的时间，最多包含 6 位小数（微秒），默认为 0 位。如果包含了小数秒，TIME 需要额外的存储，TIME(1) 和 TIME(2) 需要 4 字节，TIME(3) 和 TIME(3) 需要 5 字节，TIME(5) 和 TIME(6) 需要 6 字节存储。

时间戳类型

DATETIME(N) 和 TIMESTAMP(N) 类型可以同时存储日期（DATE）和时间（TIME）信息，也就是时间戳。

DATETIME 类型使用 ‘YYYY-MM-DD hh:mm:ss[.fraction]’ 格式显示，支持范围 ‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’，默认 0 位小数秒，需要 5 字节存储。如果支持小数秒，额外的存储和 TIME(N) 类似。

TIMESTAMP 类型使用 UTC 时区进行存储，支持范围 ‘1970-01-01 00:00:01.000000’ UTC 到 ‘2038-01-19 03:14:07.999999’ UTC，默认 0 位小数秒，需要 4 字节存储。如果支持小数秒，额外的存储和 TIME(N) 类似。

例如：

DROP TABLE IF EXISTS t;
CREATE TABLE t (dt DATETIME, ts TIMESTAMP);

SET time_zone = '+00:00';
INSERT INTO t VALUES (now(), now());

mysql> SELECT * FROM t;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2020-09-10 14:09:57 | 2020-09-10 14:09:57 |
+---------------------+---------------------+
1 row in set (0.00 sec)

两者在 UTC 时区相同，然后修改会话的时区：

mysql> SET time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2020-09-10 14:09:57 | 2020-09-10 22:09:57 |
+---------------------+---------------------+
1 row in set (0.00 sec)

结果显示，TIMESTAMP 类型会随着当前时区进行调整。

DATETIME 和 TIMESTAMP 类型支持自动初始化或者更新为当前日期时间，在字段定义时分别使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性进行设置。例如：

DROP TABLE IF EXISTS t;
CREATE TABLE t (
  id int, 
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO t(id) VALUES (1);

mysql> SELECT * FROM t;
+------+---------------------+---------------------+
| id   | dt                  | ts                  |
+------+---------------------+---------------------+
|    1 | 2020-09-10 14:33:25 | 2020-09-10 22:33:25 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

YEAR

如果只需要存储年份信息，可以使用 YEAR 类型。YEAR 类型占用 1 个字节，显示格式位 ‘YYYY’，范围从 1901 到 2155，以及 0000。

JSON 数据类型

MySQL 5.7.8 开始支持原生 JSON 数据类型，可以支持更加高效的 JSON 文档存储和管理。原生 JSON 数据类型提供了自动的格式验证以及优化的存储格式，可以快速访问文档中的元素节点。例如：

CREATE TABLE employee_json(
  emp_id    INTEGER NOT NULL PRIMARY KEY,
  emp_info  JSON NOT NULL
);
INSERT INTO employee_json 
VALUES (1, '{"emp_name": "刘备", "sex": "男", "dept_id": 1, "manager": null, "hire_date": "2000-01-01", "job_id": 1, "income": [{"salary":30000}, {"bonus": 10000}], "email": "liubei@shuguo.com"}');

mysql> INSERT INTO employee_json VALUES (2,'{"emp_name":  "刘备" ');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 23 in value for column 'employee_json.emp_info'.

除了 JSON 数据类型之外，MySQL 还提供了许多 JSON 处理函数和操作符，例如构造 JSON 对象的 JSON_OBJECT、JSON_ARRAY，查询指定元素的 ->（JSON_EXTRACT）、->> （JSON_UNQUOTE + JSON_EXTRACT），将 JSON 数据转换为 SQL 数据的 JSON_TABLE、更新 JSON 数据的 JSON_SET、JSON_INSERT、JSON_REPLACE、JSON_REMOVE 以及格式验证的 JSON_VALID 函数。例如：

SELECT emp_id, jt.*
  FROM employee_json,
       JSON_TABLE(emp_info, '$'
         COLUMNS (emp_name  VARCHAR(50) PATH '$.emp_name',
                  sex       VARCHAR(10) PATH '$.sex',
                  dept_id   INTEGER PATH '$.dept_id',
                  manager   INTEGER PATH '$.manager',
                  hire_date DATE PATH '$.hire_date',
                  job_id    INTEGER PATH '$.job_id',
                  salary    INTEGER PATH '$.income[0].salary',
                  bonus     INTEGER PATH '$.income[1].bonus',
                  email     VARCHAR(100) PATH '$.email')
       ) jt;
emp_id|emp_name|sex|dept_id|manager|hire_date |job_id|salary|bonus|email                   |
------|--------|---|-------|-------|----------|------|------|-----|------------------------|
     1|刘备     |男 |      1|       |2000-01-01|     1| 30000|10000|liubei@shuguo.com       |

一般来说，JSON 字段所需的存储和 LONGBLOB 或者 LONGTEXT 差不多。不过，JSON 文档的二进制编码需要额外的存储，包括元数据和字典信息。举例来说，JSON 文档中的字符串需要额外的 4 到 10 个字节存储。除此之外，JSON 文档的最大长度不能超过系统变量 max_allowed_packet 的限制。

📝关于 MySQL 中的文档存储功能，可以参考这篇文章。

空间数据类型

MySQL 支持许多包含各种几何和地理数据的空间数据类型，包括：

• GEOMETRY，任何类型的空间数据值；
• POINT，X-Y 坐标系中的点；
• LINESTRING，曲线，一个或多个 POINT 数据值；
• POLYGON，多边形；
• GEOMETRYCOLLECTION，GEOMETRY 数据集合；
• MULTILINESTRING，LINESTRING 数据集合；
• MULTIPOINT，POINT 数据集合；
• MULTIPOLYGON，POLYGON 数据集合。

MySQL 存储空间数据时使用 4 字节标识 SRID（空间参照标识符），然后存储 Well-Known Binary 格式的数据。LENGTH() 函数可以返回数据占用的字节数。

MySQL 中的 MyISAM、InnoDB、NDB 以及 ARCHIVE 存储引擎支持空间数据类型的存储和处理函数，MyISAM 和 InnoDB 存储引擎支持空间字段的 SPATIAL 索引。

关于 MySQL 空间数据扩展，具体参考官方文档。

选择合适的数据类型

最后我们来看看如何选择合适的数据类型。首先，应该满足存储业务数据的需求；其次，还需要考虑性能和使用方便。一般来说，先确定基本的类型：

• 文本数据，使用字符串类型；
• 数值数据，尤其是需要进行数学运算的数据，选择数字类型；
• 日期和时间信息，最好使用原生的日期时间类型，也可以考虑数字类型；
• 文档、图片、音频和视频等，使用二进制类型；或者可以考虑存储在文件服务器上，然后在数据库中存储文件的路径。

接下来需要进一步确定具体的数据类型。在满足数据存储和扩展的前提下，尽量使用更小的数据类型，可以节省一些存储，通常性能也会更好。例如，对于一个小型公司而言，员工编号通常不会超过几百，使用 SMALLINT 已经足够。对于 MySQL 而言，不需要支持负数的话可以考虑 UNSIGNED 类型。

对于字符数据，一般使用 VARCHAR 类型；如果数据长度能够确保一致，可以使用 CHAR；指定最大长度时，满足存储需求的前提下尽量使用更小的值。只有在普通字符串类型长度无法满足时才考虑 TEXT 字段类型。

如果需要存储精确的数字，不要使用浮点数类型。对于金额，可以使用 DECIMAL(p, s)；或者将数据乘以 10 的 N 次方，例如将 10.35 元存储为整数 103500，然后在应用程序中进行处理和前端显示转换。

只需要年月日信息时使用 DATE 类型，例如出生日期；只需要时间信息时使用 TIME 类型，例如发车时间；选择时间戳类型时需要注意 DATETIME 和 TIMESTAMP 的区别。

可以考虑将日期时间拆成多个数字类型存储，或者使用 UNIX 时间戳表示；但是不要使用字符串存储日期时间数据，它们无法支持数据的运算。例如获得两个日期之间的间隔，需要依赖应用程序进行转换和处理。最好也不要使用整数类型存储当前时间距离 1970 年 1 月 1 日的毫秒数来表示时间，这种方式在显示时需要进行转换，不是很方便。

对于特殊应用场景，考虑使用其他类型。例如电商的产品信息可以考虑使用 JSON 数据类型，处理地理位置信息使用空间数据类型等。

另外，如果一个字段同时出现在多个表中，使用相同的数据类型。例如，员工表中的部门编号（dept_id）字段与部门表的编号（dept_id）字段保持类型一致。

总结

本文详细分析了 MySQL 中的各种数据类型以及选择数据类型时的一些通用的原则，使用任何数据类型之前都应该查看相关的数据库文档。

如果觉得文章对你有用，欢迎关注❤️、评论📝、点赞👍！

转载：https://blog.csdn.net/horses/article/details/100834528