MySQL 数据类型详解，巨细！（上）

披着狼皮的羊_
1,568 阅读9分钟

本文正在参加「金石计划」。

MySQL 支持多种数据类型，主要可分成以下 5 大块：

  1. 数值
  2. 日期与时间
  3. 字符与字符串
  4. 空间几何
  5. JSON

这篇文章将跟大家讲讲 MySQL 的数据类型，并给出选择数据类型的参考及规范。

文章以 MySQL 8.0 为标准，部分弃用属性或功能将不在这里提及。

数值

整数类型 —— TINYINT（BOOL、BOOLEAN、INT1）、SMALLINT（INT2）、MEDIUMINT（MIDDLEINT、INT3）、INT（INTEGER、INT4）、BIGINT（INT8）

数据类型大小（字节）最小值最大值最小值（UNSIGNED）最大值（UNSIGNED）别称
TINYINT1-1281270255BOOL、BOOLEAN（但二者不支持 UNSIGNED）、INT1
SMALLINT2-3276832767065535INT2
MEDIUMINT3-833886088388607016777215MIDDLEINT、INT3
INT4-2147483648214748364704294967295INTEGER、INT4
BIGINT8-92233720368547758089223372036854775807018446744073709551615INT8
注意事项：

由上可见，BOOL（BOOLEAN）类型等价于 TINYINT 类型，也就是说 BOOL（BOOLEAN）的存储范围是从 -128 到 127，而不是 0 或 1（这是 BIT 类型，下面会提到）。

SIGNED 与 UNSIGNED 属性

UNSINGED 属性表示的是仅允许非负数整数值，可扩大无符号数的范围（见上）。

SIGNED 属性表示的是有符号整数，是默认属性，可写可不写。

浮点数类型 —— FLOAT（FLOAT4）、DOUBLE（REAL、FLOAT8）

数据类型大小（字节）最小值最大值别称
FLOAT4−3.402823466×1038-3.402823466\times10^{38}3.402823466×10383.402823466\times10^{38}FLOAT4
DOUBLE8−1.7976931348623157×10308-1.7976931348623157\times10^{308}1.7976931348623157×103081.7976931348623157\times10^{308}FLOAT8
备注：

准确地说，

  • FLOAT 类型的允许值为 −3.402823466×1038-3.402823466\times10^{38} 到 −1.175494351×10−38-1.175494351\times10^{-38}、0 和 1.175494351×10−381.175494351\times10^{-38} 到 3.402823466×10383.402823466\times10^{38}。
  • DOUBLE 类型的允许值为 −1.7976931348623157×10308-1.7976931348623157\times10^{308} 到 −2.2250738585072014×10−308-2.2250738585072014\times10^{-308}、0 和 2.2250738585072014×10−3082.2250738585072014\times10^{-308} 到 1.7976931348623157×103081.7976931348623157\times10^{308}。

这些值是基于 IEEE 标准的理论限制，实际范围可能会稍微小一些（视硬件或操作系统的情况而定）。

关于 REAL 类型

REAL 类型默认就是 DOUBLE 类型。​我们也可以更改 SQL 模式，将 REAL 类型设定为 FLOAT 类型：

SET sql_mode = "REAL_AS_FLOAT";
建议：

小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE 类型。

在存储的时候，FLOAT 和 DOUBLE 类型都存在精度损失的问题；这很可能导致在比较值的时候得到不正确的结果。

UNSIGNED 属性适不适用？

浮点数类型不推荐使用 UNSIGNED 属性，MySQL 未来版本中可能删除该支持。

这是因为就算设定了 UNSIGNED 属性，可表示的最大值仍不会改变（受限于 IEEE 标准），只是“屏蔽”了负数的写入而已。

作为 UNSIGNED FLOAT/DOUBLE 的替代方案，官方建议使用 CHECK 约束：

CREATE TABLE demo (
    c1 FLOAT CHECK ( c1 >= 0 ),
    c2 DOUBLE CHECK ( c2 >= 0 ) 
);

定点数类型 —— DECIMAL（DEC、NUMERIC、FIXED）

数据类型大小（字节）备注别称
DECIMAL(M, D)有点复杂，见下M 的范围是 1 ~ 65，默认为 1；D 的范围是 0 ~ 30 且 D 不大于 M，默认为 0DEC、NUMERIC、FIXED

DECIMAL 类型的大小问题

DECIMAL 值使用二进制格式存储，将 9 个十进制数字打包成 4 个字节。值的整数部分和小数部分分开存储。对于剩余的数字，所需的存储空间如下：

剩余数字大小（字节）
00
1 或 21
3 或 42
5 或 63
7 或 84
例子：

DECIMAL(29,6) 字段有 23 个整数位和 6 个小数位。整数位中，9 个数字一组，各占 4 个字节，剩余 5 个数字占 3 个字节。另外还有 6 个小数位需要 3 个字节。这样一来，总共是 ⌊239⌋×4+3+3=14\lfloor \frac{23}{9} \rfloor \times 4 + 3 + 3 = 14 个字节。

UNSIGNED 属性适不适用？

定点数类型不推荐使用 UNSIGNED 属性，MySQL 未来版本中可能删除该支持。

这是因为就算设定了 UNSIGNED 属性，可表示的最大值仍不会改变，只是“屏蔽”了负数的写入而已。

作为 UNSIGNED DECIMAL 的替代方案，官方建议使用 CHECK 约束：

CREATE TABLE demo (
    c1 DECIMAL CHECK ( c1 >= 0 ),
);
建议：

如果存储的数据范围超过 DECIMAL 类型的表达范围，建议将数据拆成整数和小数并分开存储。

位类型 —— BIT

数据类型大小（位）备注
BIT(M)M (1 ~ 64)M 默认为 1

要想指定一个位值字面值，可以用：

（以 1 为例子）

  • b'1' 或
  • B'1' 或
  • 0b1
  • 0B1（错误写法，会报错）

来表示。

自增序列号类型 —— SERIAL

BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别称。

整数列定义中的 SERIAL DEFAULT VALUE 是 NOT NULL AUTO_INCREMENT UNIQUE 的别称。

日期与时间

年类型 —— YEAR

数据类型大小（字节）最小值最大值
YEAR119012155

MySQL 以 YYYY 格式显示 YEAR 值。YEAR 类型接受多种格式的输入值：

  • YYYY 格式的字符串，如：'2022'；
  • YYYY 格式的数字，如：2022；
  • '0' 到 '99' 范围内的 1 位或 2 位数字字符串：
    '0' ~ '69' 将转换为 2000 ~ 2069，
    '79' ~ '99' 将转换为 1970 ~ 1999；
  • 0 到 99 范围内的 1 位或 2 位数字：
    '1' ~ '69' 将转换为 2001 ~ 2069，
    '79' ~ '99' 将转换为 1970 ~ 1999。

MySQL 会将无效的 YEAR 值转换为 0000。如果启用了严格模式，插入无效的 YEAR 值会报错。

注意事项：

数字和字符串格式的输入值不一定等价！插入数字 0 的结果为 0000。如果要将其解释为 2000，必须以字符串 '0' 或 '00' 表示。

为了避免不必要的麻烦，建议统一使用 YYYY 格式插入数据。

日期类型 —— DATE

数据类型大小（字节）最小值最大值
DATE31000-01-019999-12-31

MySQL 以 YYYY-MM-DD 格式显示 DATE 值。DATE 类型接受的格式：

  • 'YYYY-MM-DD'
  • 'YY-MM-DD'
  • 'YYYYMMDD'
  • 'YYMMDD'
  • YYYYMMDD
  • YYMMDD

年的部分遵循 YEAR 类型的格式。任何符号都可以用作年、月和日部分之间的分隔符（例：'2012-12-31'、'2022/12/31'、'2022^12^31'、2022:12:31 和 2022^12&31 等价）。

备注：

从 MySQL 8.0.29 开始，使用短横线（-）以外的任何字符作为分隔符都会引发警告，如下所示：

mysql> SELECT DATE'2012@12@31';
+------------------+
| DATE'2012@12@31' |
+------------------+
| 2012-12-31       |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 4095
Message: Delimiter '@' in position 4 in datetime value '2012@12@31' at row 1 is
deprecated. Prefer the standard '-'. 
1 row in set (0.00 sec)

MySQL 会将无效的 DATE 值转换为 '0000-00-00'。如果启用了 NO_ZERO_DATE 格式或严格模式，插入无效的 DATE 值会报错。设置 ALLOW_INVALID_DATES 模式可以允许非法 DATE 值的插入。

时间类型 —— TIME

数据类型大小（字节）最小值最大值备注
TIME(fsp)3 + 秒的精度（fsp）所需的存储大小-838:59:59.000000838:59:59.000000fsp 取值为 0 ~ 6，默认为 0
小数位数大小（字节）
00
1 或 21
3 或 42
5 或 63

MySQL 以 hh:mm:ss[.fraction] 格式显示 TIME 值。

例子：

TIME(3) 字段可以存储带 3 位小数的 TIME 值，如：03:20:17.347。TIME 字段的基本大小是 3 个字节，3 位小数占 2 个字节，所以 TIME(3) 一共占 3+2=53+2=5 个字节。

TIME 类型接受的格式：

  • 'D hh:mm:ss' * **
  • 'D hh' *
  • 'D hh:mm' *
  • 'hh:mm:ss' * **
  • 'hhmmss' * **
  • hhmmss
  • 'hh:mm' *
  • mmss
  • 'ss' *
  • ss

*：小时、分钟和秒的值不一定要是两位数，'8:3:2' 和 '08:03:02' 等价。
**：秒的值可以精确到微秒，也就是 6 位小数（例：'10:11:12.996007'）。

时、分和秒的分隔符只能是冒号（：）（如有）。

MySQL 会将无效的 TIME 值转换为 '00:00:00'。如果启用了严格模式，插入无效的 TIME 值会报错。

备注：

根据上方的规则，'11:12' 和 1112 表示的是 11:12:00，而不是 00:11:12。

注意事项：

'00:00:00' 本身是一个有效的 TIME 值。因此，我们无法从表中的 '00:00:00' 判断原始值是 '00:00:00' 还是插入的值是无效的。

日期时间类型 —— DATETIME、TIMESTAMP

数据类型大小（字节）最小值最大值备注
DATETIME(fsp)5 + 秒的精度（fsp）所需的存储大小1000-01-01 00:00:00.0000009999-12-31 23:59:59.000000fsp 取值为 0 ~ 6，默认为 0
TIMESTAMP(fsp)4 + 秒的精度（fsp）所需的存储大小1970-01-01 00:00:01.000000 UTC2038-01-19 03:14:07.999999 UTCfsp 取值为 0 ~ 6，默认为 0
小数位数大小（字节）
00
1 或 21
3 或 42
5 或 63

MySQL 以 YYYY-MM-DD hh:mm:ss[.fraction] 格式显示 DATETIME/TIMESTAMP 值。DATETIME/TIMESTAMP 类型接受的格式：

  • 'YYYY-MM-DD hh:mm:ss' *
  • 'YY-MM-DD hh:mm:ss' *
  • 'YYYYMMDDhhmmss'
  • 'YYMMDDhhmmss'
  • YYYYMMDDhhmmss
  • YYMMDDhhmmss

*：日期和时间中间可以是符号、大写字母 T 或者一个或多个空白字符。从 MySQL 8.0.29 开始，使用一个空格以外的任何字符作为日期和时间的分隔符都会引发警告。

YEAR 类型、DATE 类型和 TIME 类型的规则/格式都能套用于这里。唯一不同的是，时、分和秒之间的分隔符可以是任何符号。

MySQL 会将无效的 DATETIME 值转换为 '0000-00-00 00:00:00'。如果启用了 NO_ZERO_DATE 格式或严格模式，插入无效的 DATETIME/TIMESTAMP 值会报错。

设置 ALLOW_INVALID_DATES 模式可以允许非法 DATETIME 值的插入，但对 TIMESTAMP 类型不起作用。

时区处理

TIMESTAMP 类型以 UTC 时区储存，它会自动检索当前时区并进行转换。

例子：

插入一条数据（我所在的地区是 +08:00 时区）：

INSERT INTO demo (ts, dt)
VALUES (now(), now());

查询结果：

mysql> select * from demo;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2022-01-01 10:11:00 | 2022-01-01 10:11:00 |
+---------------------+---------------------+
1 rows in set (0.00 sec)

现在我将我的时区修改为 +07:00：

SET time_zone = "+07:00";

再次查询：

mysql> select * from demo;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2022-01-01 09:11:00 | 2022-01-01 10:11:00 |
+---------------------+---------------------+
1 rows in set (0.00 sec)

可以看到 TIMESTAMP 类型显示的时间比 DATETIME 类型慢了 1 个小时。

默认值与自动更新

在创建相关类型的字段时，我们可以使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句，将当前时间戳作为默认值，并在更新数据时将其自动更新为当前的时间戳。

CREATE TABLE demo (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

……

篇幅太长了~~~ 下章继续~~~

参考资料

  1. dev.mysql.com/doc/refman/…
  2. www.bilibili.com/video/BV1iq… 及其课件
  3. 00fly.online/upload/alib…
  4. dev.mysql.com/doc/refman/…
avatar
文章
阅读
粉丝