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

我们在设计数据库时需要为表中的每个字段指定一个数据类型。数据类型决定了字段中允许存储的数据以及支持的操作,例如字符串允许的最大长度、数字类型可以进行算术运算等;另一方面,不同的类型占用的存储空间和处理性能也不同。因此,本文给大家介绍一下 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 字节存储,剩余的位数需要的存储空间如下表所示:

剩余位数字节数
00
1–21
3–42
5–63
7–94

例如,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';
+------------+
| 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 pos
除了 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 支持许多包含各种几何和地理数据的空间数据类型,包括:

  • 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 中的各种数据类型以及选择数据类型时的一些通用的原则,使用任何数据类型之前都应该查看相关的数据库文档。

如果觉得文章对你有用,欢迎关注❤️、点赞👍、推荐🎁

发表评论

登录后才能评论
网站客服
网站客服
申请收录 侵权处理
分享本页
返回顶部