search

JChehe / blog

🌈 原创&翻译 🌈
719 stars 111 forks source link

【译】MySQL 数据类型【8.0 中文版】 #47

Open JChehe opened 4 years ago

JChehe commented 4 years ago

阅读体验更佳：https://jchehe.gitbook.io/mysql_data_types_cn/

原文：MySQL 8.0 官方文档的第十一章

目录（译者注：目录跳转可能无效，请直接搜索文字）

  1. 数值数据类型
  2. “日期和时间”数据类型
  3. 字符串数据类型
  4. 空间数据类型
  5. JSON 数据类型
  6. 数据类型的默认值
  7. 数据类型的存储要求
  8. 为列选择正确的类型
  9. 使用其他数据库引擎的数据类型

概述

MySQL 支持几种类别的 SQL 数据类型：数值类型、“日期和时间”类型、字符串（字符和字节）类型、空间类型和 JSON 数据类型。本章阐述了每个类别所含类型的属性，以及总结了数据类型的存储要求。

数据类型的描述使用了以下约定：

数值数据类型

概述

MySQL 支持 SQL 标准中所有数值数据类型，其中包括精确数值数据类型（INTEGER、SMALLINT、DECIMAL 和 NUMERIC），也包括近似数值数据类型（FLOAT、REAL 和 DOUBLE PRECISION）。关键字 INT 是 INTEGER 的代名词，关键字 DEC 和 FIXED 是 DECIMAL 的代名词。MySQL 将 DOUBLE 视为 DOUBLE PRECISION 的代名词（一种非标准扩展）。除非启用 SQL 的 REAL_AS_FLOAT 模式，否则 MySQL 也把 REAL 视为 DOUBLE PRECISION 的代名词（一种非标准变体）。

BIT 数据类型用于存储 bit 值，MyISAM、MEMORY、InnoDB 和 NDB 数据库表支持该数据类型。

关于 MySQL 如何处理超出范围和溢出的数值的更多信息，请查看 “Out-of-Range and Overflow Handling”。

关于数值数据类型的存储要求的信息，请查看 Section 11.7, “Data Type Storage Requirements”。

关于处理数值函数的说明，请查看 Section 12.5, “Numeric Functions and Operators”。数值运算结果的数据类型是取决于运算值的类型及对它们执行的操作。详情请看 Section 12.5.1, “Arithmetic Operators”。

数值数据类型的句法

对于整型数据类型，M 表示最大的显示宽度，其最大值为 255。显示宽度与类型可存储的值的范围无关，如 Section 11.1.6, “Numeric Type Attributes” 中所述。

对于浮点型和定点型数据类型，M 表示可存储数字的总个数。

从 MySQL 8.0.17 开始，不建议使用整型数据类型的“显示宽度”属性，并将在未来的 MySQL 版本中移除它。

若为一个数值类型的列指定 ZEROFILL，MySQL 将会为该列自动添加 UNSIGNED 属性。

从 MySQL 8.0.17 开始，不建议使用数值数据类型的 ZEROFILL 属性，并将在未来的 MySQL 版本中移除它。可考虑使用替代方法实现同样的效果。例如，在程序中使用 LPAD() 函数将数字 0 填充至所需宽度，或存储格式化后的数值到 CHAR 类型的列中。

数值数据类型即允许 UNSIGNED 属性，也允许 SIGNED 属性。然而，SIGNED 是默认值，因此 SIGNED 并没有什么用。

从 MySQL 8.0.17 开始，不建议数据类型为 FLOAT、DOUBLE 和 DECIMAL（及它们的代名词）的列使用 UNSIGNED 属性，并将在未来的 MySQL 版本中移除它。可考虑对这些列使用简单的 CHECK 进行约束。 （译者注：UNSIGNED 并不影响它们的取值范围，而是表明该列不接受负数）

SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名。

在整型列的定义中， SERIAL DEFAULT VALUE 是 NOT NULL AUTO_INCREMENT UNIQUE 的别名。

注意：当两个整型进行减法运算，且其中一个是 UNSIGNED，则结果是无符号的（unsigned），除非启用 SQL 的 NO_UNSIGNED_SUBTRACTION 模式。详情请看 Section 12.10, “Cast Functions and Operators”。

整型类型（精确值）- INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

MySQL 支持 SQL 标准的整型类型：INTEGER（或 INT）和 SMALLINT。作为标准的扩展，MySQL 也支持这些整型类型：TINYINT、MEDIUMINT 和 BIGINT。以下表格展示了每种整型类型所需的存储空间和取值范围。

Table 11.1 Required Storage and Range for Integer Types Supported by MySQL

Type Storage (Bytes) Minimum Value Signed Maximum Value Signed Minimum Value Unsigned Maximum Value Unsigned
TINYINT 1 -128 127 0 255
SMALLINT 2 -32768 32767 0 65535
MEDIUMINT 3 -8388608 8388607 0 16777215
INT 4 -2147483648 2147483647 0 4294967295
BIGINT 8 -2^63 2^63-1 0 2^64-1

定点型类型（精确值）- DECIMAL, NUMERIC

DECIMAL 和 NUMERIC 类型能存储精确的数值数据。这些类型适用于重视精确度的需求，如货币数据。在 MySQL 中，NUMERIC 被实现为 DECIMAL，因此以下有关 DECIMAL 的说明同样适用于 NUMERIC。

MySQL 以二进制格式存储 DECIMAL。详情请看 See Section 12.25, “Precision Math”。

在 DECIMAL 列的声明中，通常都会指定 M（the precision）和 D（the scale）。如：

salary DECIMAL(5, 2)

以上案例，M 是 5，D 是 2。M 表示可存储多少个有效数字，D 表示可存储小数点后多少个数字。

SQL 标准要求 DECIMAL(5, 2) 能存储任何由 5 个数字（其中 2 个是小数）组成的数值。因此，salary 列的存储范围是 [-999.99, 999.99]。

在 SQL 标准中，DECIMAL(M) 等同于 DECIMAL(M, 0)。类似地，DECIMAL 等同于 DECIMAL(M, 0)，其具体表现则取决于 M 值。MySQL 支持以上 DECIMAL 的两种不同形式，且 M 的默认值为 10。

若 D 为 0，则 DECIMAL 值不包含小数点（或小数部分）。

DECIMAL 支持最多 65 个数字，但 DECIMAL 列的实际存储范围受 M 和 D 的约束。当值的小数部分的数字个数大于指定的 D，则会将小数部分的数字个数限制为 D 个。（这种明确的行为是特定于操作系统，但通常的处理方式为裁剪至 D 长度。【译者注：FLOAT 等近似值数据类型一般采取四舍五入】）

浮点型类型（近似值）- FLOAT, DOUBLE

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

对于 FLOAT，SQL 标准允许 FLOAT 关键字后的小括号() 内指定一个可选的、以 bit 为单位的精度参数—— FLOAT(p)。MySQL 也支持此可选精度的语法，但该精度参数仅用于确定存储大小。当 p 位于 [0, 23]，则该列是 4 字节的单精度 FLOAT 列；当 p 位于 [24, 53]，则该列是 8 字节的双精度 DOUBLE 列。

MySQL 支持一种非标准句法：FLOAT(M, D) 或 REAL(M, D) 或 DOUBLE PRECISION(M, D)。在这里，(M, D) 表示值可以最大存储 M 个数字，其中 D 个数字位于小数点后。例如，一个定义为 FLOAT(7, 4) 的列，其值显示为 -999.9999。MySQL 会在存储值时执行四舍五入操作，所以当你插入 999.00009 到 FLOAT(7, 4) 列时，其实际结果为近似值 999.0001。

从 MySQL 8.0.17 开始，不建议使用非标准句法 FLOAT(M, D) 和 DOUBLE(M, D)，并将在未来的 MySQL 版本中移除它们。

由于浮点数是近似值，不能作为精确值存储，当将它们视为精确值进行比较时，可能会出现问题。另外，它们还受平台或实现依赖的约束。详情请看 Section B.4.4.8, “Problems with Floating-Point Values”。

为了获得最大可移植性，存储近似值数据时，应使用无 M 和 D 的 FLOAT 或 DOUBLE PRECISION 类型。

Bit 值类型 - BIT

BIT 数据类型用于存储 bit 值。BIT(M) 表示可允许存储 M-bit 大小的值。M 的取值范围是 [1, 64]。

要指定 bit 值，可使用 b'value' 表示法。value 是由 0 和 1 组成的二进制值。如：b'111' 和 b'10000000' 分别表示 7 和 128。详情请看 Section 9.1.5, “Bit-Value Literals”。

如果指定的值的长度小于 BIT(M) 列要求的 M 位，则该值会在左侧填充 0。例如，向 BIT(6) 列指定 b'101' 值，实际相当于指定 b'000101'。

NBD 集群。一个 NDB 表中所有 BIT 列的总大小不能超过 4096 bit。

数值类型的属性

MySQL 支持在整型数据类型后的括号内指定一个可选的显示宽度属性。如：INT(4) 指定 INT 列的显示宽度为 4 个数字。应用程序可使用此可选的显示宽度来显示整型数据，当该数值的宽度小于指定宽度时，会在其左侧填充空格。（也就是说，此宽度存在于返回结果集的元数据中，是否使用该宽度则取决于应用程序。）

显示宽度不会限制值的存储范围，也不会影响比显示宽度长的值的正确显示。例如，数据类型为 SMALLINT(3) 的列，通常其存储范围是 [-32768, 32767]，对于大于显示宽度的值也能显示完整。

当同时使用可选（非标准）的 ZEROFILL 属性时，填充左侧的空格将会被替换为 0。例如，数据类型为 INT(4) ZEROFILL 的列，检索值 5 时会得到 0005。

注意：对于表达式或 UNION 查询中涉及的列，会忽略 ZEROFILL 属性。

如果将大于显示宽度的值存储在具有 ZEROFILL 属性的整型列中，则当 MySQL 为某些复杂的联结（join）生成临时表时，你可能会遇到问题。在这种情况下，MySQL 会假定值已满足列的显示宽度。

从 MySQL 8.0.17 开始，不建议数值数据类型使用 ZEROFILL 属性，对于整型数据类型，则不建议使用显示宽度属性，并将在未来的 MySQL 版本中移除整型数据类型的 ZEROFILL 和显示宽度属性。可考虑使用替代方法实现同样的效果。例如，在程序中使用 LPAD() 函数将数字 0 填充至所需宽度，或存储格式化后的数值到 CHAR 类型的列中。

所有整型数据类型均拥有可选的（非标准）UNSIGNED 属性。一个 unsigned 类型可实现非负数的存储，或者你希望拥有更大的数值范围。例如，如果 INT 列是 UNSIGNED 的，虽然范围的长度不变，但端点是往正数方向移动，即从 [-2147483648, 2147483647] 变到 [0, 4294967295]。

浮点型和定点型也可使用 UNSIGNED 属性，和整型一样，unsigned 属性能实现非负数的存储。但与整型不一样的地方是，它并不能改变取值范围，即保持不变。从 MySQL 8.0.17 开始，不建议 FLOAT、DOUBLE 和 DECIMAL（及其代名词） 数据类型的列使用 UNSIGNED 属性，并将在未来的 MySQL 版本中移除它。可考虑对这些列使用简单的 CHECK 进行约束。

若为数值类型的列指定 ZEROFILL，MySQL 则自动为其添加 UNSIGNED 属性。

整型和浮点型数据类型的列可使用 AUTO_INCREMENT 属性。当你向 AUTO_INCREMENT 索引列插入 NULL 值时，该列会将其设为下一个序列值。通常为 value+1，其中 value 是列目前的最大值。（AUTO_INCREMENT 序列从 1 开始。）

向 AUTO_INCREMENT 列存储 0 时，效果和存储 NULL 一样，除非启用 SQL 的 NO_AUTO_VALUE_ON_ZERO 模式。

插入 NULL 以生成 AUTO_INCREMENT 值的前提是该列已声明为 NOT NULL。如果该列声明为 NULL，那么在插入 NULL 时就会存为 NULL。当向 AUTO_INCREMENT 列插入其他值，那么就会直接存储该值，并重置序列值，以使得下次能自动生成以该插入值为起始值的序列值。

AUTO_INCREMENT 列不支持负值。

CHECK 约束不能引用具有 AUTO_INCREMENT 属性的列，也不能将 AUTO_INCREMENT 属性添加到 已使用 CHECK 约束的列。

从 MySQL 8.0.17 开始，不建议为 FLOAT、DOUBLE 列使用 AUTO_INCREMENT，并将在未来的 MySQL 版本中移除它。可考虑移除这些列的 AUTO_INCREMENT 属性，或将它们转为整型。

超出取值范围和溢出处理

当 MySQL 向数值类型的列存储超出列数据类型允许范围的值时，处理结果取决于当时的 SQL 模式。

假设表 t1 定义如下：

CREATE TABLE t1 (i1 TINYINT, i2 TINYINT UNSIGNED);

在启用 SQL 严格模式下，会报超出范围的错误：

mysql> SET sql_mode = 'TRADITIONAL';
mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);
ERROR 1264 (22003): Out of range value for column 'i1' at row 1
mysql> SELECT * FROM t1;
Empty set (0.00 sec)

当未启用 SQL 严格模式，会进行裁剪并产生警告（warning）：

mysql> SET sql_mode = '';
mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i1' at row 1 |
| Warning | 1264 | Out of range value for column 'i2' at row 1 |
+---------+------+---------------------------------------------+
mysql> SELECT * FROM t1;
+------+------+
| i1   | i2   |
+------+------+
|  127 |  255 |
+------+------+

如果未启用 SQL 严格模式，列赋值转换（column-assignment conversion）会因裁剪而发生，并会产生警告（warning），以上这些会发生在 ALTER TABLE、LOAD DATA、UPDATE 和多行 INSERT 语句中。在严格模式下，这些语句均会执行失败，并且某些、甚至所有值都不会被插入或更改，这取决于该表是否为事务表或其他一些因素。详情请看 Section 5.1.11, “Server SQL Modes”。

数值表达式求值期间可能会产生溢出而导致错误。例如，有符号 BIGINT 的最大值是 9223372036854775807，则以下表达式会报错：

mysql> SELECT 9223372036854775807 + 1;
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'

要想以上表达式顺利运行，需要将值转为无符号：

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
+-------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) + 1 |
+-------------------------------------------+
|                       9223372036854775808 |
+-------------------------------------------+

是否发生溢出是取决于操作数的取值范围，因此处理上面表达式的另一种方法是使用精确算术法，因为 DECIMAL 的取值范围比整型大。

mysql> SELECT 9223372036854775807.0 + 1;
+---------------------------+
| 9223372036854775807.0 + 1 |
+---------------------------+
|     9223372036854775808.0 |
+---------------------------+

在整型的减法中，当其中一个操作数是 UNSIGNED，则默认会产生一个 UNSIGNED 结果值。如果结果是负数值，则会报错：

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

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

如果启用 SQL 的 NO_UNSIGNED_SUBTRACTION 模式，则结果为负数：

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+

如果将这个表达式的结果值用于更新一个 UNSIGNED 整型列，则会被裁剪为该列数据类型的取值范围的最大值，或当启用 NO_UNSIGNED_SUBTRACTION 时，会裁剪为 0。如果开启 SQL 严格模式，则会报错，且该列的值保持不变。

“日期和时间”数据类型

译者注：

  • 时间值：包含日期、时分秒、“日期+时分秒”等关于“日期和时间”的值。
  • 小数：从 MySQL 5.6.4 开始支持小数，精度最高至微秒（6 位小数）

概述

用于表示时间值的“日期和时间”数据类型有：DATE、TIME、DATETIME、TIMESTAMP、YEAR。每个时间类型均拥有各自合法的取值范围，以及一个“零”值，即当你指定一个 MySQL 无法表达的非法值时会被替换为该值。TIMESTAMP 和 DATETIME 数据类型拥有特殊的自动更新能力。详情请看 Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”。

关于时间值的存储要求，请看 Section 11.7, “Data Type Storage Requirements”。

关于对时间值进行运算的函数说明，请看 Section 12.6, “Date and Time Functions”。

使用“日期和时间”数据类型时，请记住以下一般注意事项：

下面表格展示了每种数据类型对应的“零”值。“零”值是特殊的，但可以用表中展示的值显式地存储或引用它们。另外，你也可以使用更容易编写的值——'0' 或 0 来实现同样的操作。对于含有日期的时间类型（DATE、DATETIME 和 TIMESTAMP），使用这些值可能会产生警告（warning）或错误（error）。这种明确的行为取决于严格模式的启用与否和 NO_ZERO_DATE 模式中的哪一个。详情请看 Section 5.1.11, “Server SQL Modes”。

Data Type "Zero" Value
DATE '0000-00-00'
TIME '00:00:00'
DATETIME '0000-00-00 00:00:00'
TIMESTAMP '0000-00-00 00:00:00'
YEAR 0000

“日期和时间”数据类型的句法

用于表示时间值的“日期和时间”数据类型有：DATE、TIME、DATETIME、TIMESTAMP 和 YEAR。

对于 DATE 和 DATETIME 取值范围的描述，“支持（supported）”表示尽管较早的值可能有效，但并不保证。

MySQL 允许 TIME、DATETIME 和 TIMESTAMP 拥有小数，精度最大为微秒（6 位数）。为了定义拥有小数的列，需要使用 type_name(fsp) 句法，其中 type_name 是 TIME、DATETIME 或 TIMESTAMP 中的一个，fsp 是小数的精度。例如：

CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));

fsp 的取值范围是 [0, 6]。0 表示没有小数部分。默认值是 0（缺省时）。（与 SQL 标准的默认值 6 不同，主要为了兼容 MySQL 旧版本。）

表中任意 TIMESTAMP 和 DATETIME 列都可具有自动初始化和更新的属性，详情请看 Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”。

聚集函数 SUM() 和 AVG() 不适用于时间值（它们将时间值转换为数值时，会丢失掉自第一个非数值字符后的所有信息）。为了解决该问题，可先将时间值转为数值单位后再执行聚集操作，最后再转回为时间值。如：

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

DATE、DATETIME 和 TIMESTAMP 类型

DATE、DATETIME 和 TIMESTAMP 三者是相关的。本节描述了它们的特征，包括它们的相似与不同。MySQL 能以几种形式识别 DATE、DATETIME 和 TIMESTAMP 的值，于 Section 9.1.3, “Date and Time Literals” 进行了详细描述。对于 DATE 和 TIMESTAMP 的取值范围描述，“支持（supported）”表示尽管较早的值可能有效，但并不保证。

DATE 类型适用于仅有日期但无时间的值。MySQL 以 'YYYY-MM-DD' 格式进行检索和显示。支持（supported）的取值范围是 ['1000-01-01', '9999-12-31']。

DATETIME 类型适用于同时含日期和时间的值。MySQL 以 'YYYY-MM-DD hh:mm:ss' 格式进行检索和显示。支持（supported）的取值范围是 ['1000-01-01 00:00:00', '9999-12-31 23:59:59']。

TIMESTAMP 类型适用于同时含有日期和时间的值。TIMESTAMP 的取值范围是 ['1970-01-01 00:00:01' UTC, '2038-01-19 03:14:07' UTC]。

DATETIME 和 TIMESTAMP 值能拥有小数部分，精度达到微秒（6 位数）。插入到 DATETIME 或 TIMESTAMP 列中的值的小数部分都会被存储而不是被丢弃。包括小数部分在内，这些值的格式是 'YYYY-MM-DD hh:mm:ss[.fraction]'。相应地，DATETIME 的取值范围 ['1000-01-01 00:00:00.000000', '9999-12-31 23:59:59.999999']；TIMESTAMP 的取值范围是 ['1970-01-01 00:00:01.000000', '2038-01-19 03:14:07.999999']。小数部分应始终与时间的其它部分用小数点分隔。除小数点外，任何标识符都不能作为小数的分隔符。关于 MySQL 对小数秒的支持信息，请看 see Section 11.2.6, “Fractional Seconds in Time Values”。

TIMESTAMP 和 DATETIME 数据类型提供了自动初始化和更新为当前日期和时间的特性。详情请看 Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”。

MySQL 会将 TIMESTAMP 的值从当前时区转化为 UTC 进行存储。相应地也会在检索时从 UTC 转化为当前时区。（对于其他类型，如 DATETIME，则不会发生这种情况）。默认情况下，每个连接（connection）的当前时区就是服务器的时间。可以在每个预连接（pre-connection）的基础上设置时区。只要时区设置保持不变，你就能获得与存储相同的值。如果存储了一个 TIMESTAMP 值，然后在改变时区后再检索该值，则检索的值就与存储的值不同。发生这种情况是因为存储与检索的时区不一致。当前时区可用作 time_zone 系统变量的值。详情请看 Section 5.1.14, “MySQL Server Time Zone Support”。

从 MySQL 8.0.19 开始，你可以在插入 TIMESTAMP 或 DATETIME 值时，指定时区偏移量。该偏移量追加在日期时间字面量的日期部分后，且中间没有空格。另外，同样格式也可用在 time_zone 系统变量。时区需要注意以下几点：

插入带有时区偏移量的日期时间到 TIMESTAMP 和 DATETIME 列时，使用不同的 time_zone 设置，会造成什么影响呢？（观察检索结果）：

mysql> CREATE TABLE ts (
    ->     id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col TIMESTAMP NOT NULL
    -> ) AUTO_INCREMENT = 1;

mysql> CREATE TABLE dt (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col DATETIME NOT NULL
    -> ) AUTO_INCREMENT = 1;

mysql> SET @@time_zone = 'SYSTEM';

mysql> INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql> SET @@time_zone = '+00:00';

mysql> INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql> SET @@time_zone = 'SYSTEM';

mysql> INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql> SET @@time_zone = '+00:00';

mysql> INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql> SET @@time_zone = 'SYSTEM';

mysql> SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| EST                |
+--------------------+

mysql> SELECT col, UNIX_TIMESTAMP(col) FROM dt ORDER BY id;
+---------------------+---------------------+
| col                 | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-01-01 10:10:10 |          1577891410 |
| 2019-12-31 23:40:10 |          1577853610 |
| 2020-01-01 13:10:10 |          1577902210 |
| 2020-01-01 10:10:10 |          1577891410 |
| 2020-01-01 04:40:10 |          1577871610 |
| 2020-01-01 18:10:10 |          1577920210 |
+---------------------+---------------------+

mysql> SELECT col, UNIX_TIMESTAMP(col) FROM ts ORDER BY id;
+---------------------+---------------------+
| col                 | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-01-01 10:10:10 |          1577891410 |
| 2019-12-31 23:40:10 |          1577853610 |
| 2020-01-01 13:10:10 |          1577902210 |
| 2020-01-01 05:10:10 |          1577873410 |
| 2019-12-31 23:40:10 |          1577853610 |
| 2020-01-01 13:10:10 |          1577902210 |
+---------------------+---------------------+

检索时并不会显示日期时间值的偏移量，尽管在插入时有一个值带有偏移量。

偏移量支持（supported）的取值范围是 [-14:00, +14:00]。

带有时区偏移量的日期时间字面量可通过参数化查询（prepared statements）指定参数。

非法的 DATE、DATETIME 和 TIMESTAMP 值会被转化为相应类型的“零”值（'0000-00-00' 或 '0000-00-00 00:00:00'），但前提是 SQL 模式允许此转换。这种明确的行为取决于严格模式的启用是否和 NO_ZERO_DATE 模式中的哪一个。详情请看 Section 5.1.11, “Server SQL Modes”。

注意 MySQL 对日期值的各个组成部分的解析规则：

TIME 类型

MySQL 以 'hh:mm:ss'（或 hhh:mm:ss 格式，用于小时数较大的值）格式进行检索和显示。TIME 的取值范围是 ['-838:59:59', '838:59:59']。小时部分可能会很大，因为 TIME 不仅用于存储一天内的时间（这肯定小于 24 小时），而且还能存储消耗的时间或两事件之间的时间间隔（这可能远大于 24 小时，甚至是负数）。

MySQL 可识别几种格式的 TIME 值，其中包括小数部分，其精度最大达到微秒（6位数）。详情请看 Section 9.1.3, “Date and Time Literals”。有关 MySQL 对小数的支持信息，请看 Section 11.2.6, “Fractional Seconds in Time Values”。插入到 TIME 列中的值的小数部分都会被存储而不是被丢弃。当含有小数部分时，TIME 的取值范围是 ['-838:59:59.000000', '838:59:59.000000']。

将省略值赋值给 TIME 列时要小心。因为 MySQL 解析带有冒号（:）的省略 TIME 值时，会优先作为一天中的时间进行解析。也就是说，'11:12' 表示的是 '11:12:00'，而不是 '00:11:12'。MySQL 解析不带有冒号（:）的简写 TIME 值时，最右侧的两位数会被解析为秒（也就是说，会优先解析为消耗的时间，而不是一天中的时间）。例如，你可能认为 '1112' 和 1112 表示 '11:12:00'（11 时 12 分），其实 MySQL 将它们解析为 '00:11:12'（11 分 12 秒）。如此类推，'12' 和 12 会被解析为 '00:00:12'。

时间与小数之间唯一能被识别的分隔符是小数点。

默认情况下，位于 TIME 取值范围之外，但有效（译者注：指格式）的值将会被裁剪为最靠近端点的值。例如，'-850:00:00' 和 '850:00:00' 会被分别转为 '-838:59:59' 和 '838:59:59'。非法的 TIME 值会被转为 '00:00:00'。需要注意的是，因为 '00:00:00' 本身就是 TIME 的有效值，所以无法判断表中的 '00:00:00' 值是主动存储还是非法值转化而来的。

为了更严格地处理非法的 TIME 值（对此会产生一个错误），可启动 SQL 的严格模式。详情请看 Section 5.1.11, “Server SQL Modes”。

YEAR 类型

YEAR 是用来表示年份值的类型，其大小为 1 字节。可隐式声明为显示宽度为 4 字符的 YEAR，即与显式声明的 YEAR(4) 等效。

注意：从 MySQL 8.0.19 开始，不建议使用明确设定显示宽度的 YEAR(4) 数据类型，并将在未来的 MySQL 版本中移除它。而且，无显示宽度的 YEAR 与它的含义相同。

MySQL 8.0 不支持旧版 MySQL 中允许的 2 位数 YEAR(2)。有关转换为 4 位数 YEAR 的说明，请看 MySQL 5.7 Reference Manual 的 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR 。

MySQL 以 YYYY 的形式显示 YEAR 值。其取值范围是 [1901, 2155] 和 0000。

YEAR 接受以下几种格式的输入值：

若未开启 SQL 严格模式，MySQL 会将非法的 YEAR 值转为 0000。反之，插入一个非法的 YEAR 值时会报错。

另请查看 Section 11.2.8, “2-Digit Years in Dates”。

TIMESTAMP 和 DATETIME 的自动初始化与更新

TIMESTAMP 和 DATETIME 列拥有自动初始化和更新到当前日期和时间（即当前时间戳）的能力。

表中任意 TIMESTAMP 和 DATETIME 列都可以将当前时间戳作为默认值和自动更新的值（两者可同时存在）：

此外，若系统变量 explicit_defaults_for_timestamp 被禁用，那么可通过为 TIMESTAMP 列（不包括 DATETIME 类型）赋值 NULL，实现初始化或更新为当前日期和时间。除非该列声明时允许 NULL 值。

要指定自动化属性，需要在列定义中使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句。这两个子句的顺序无关紧要。如果定义中同时存在这两者，则任意一个都可以先出现。与 CURRENT_TIMESTAP 等价的有：CURRENT_TIMESTAMP()、NOW()、LOCALTIME、LOCALTIME()、LOCALTIMESTAMP 和 LOCALTIMESTAMP()。

DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 只能用于 TIMESTAMP 和 DATETIME。DEFAULT 子句可指定一个常量（即保持不变）作为默认值（如 DEFAULT 0 或 DEFAULT '2000-01-01 00:00:00'）。

注意：以下案例使用 DEFAULT 0，该默认值会产生警告或错误，这取决于是否开启严格模式或 NO_ZERO_DATE。需要注意的是，SQL 模式 TRADITIONAL 是包含严格模式和 NO_ZERO_DATE 的。详情请看 Section 5.1.11, “Server SQL Modes”。

在 TIMESTAMP 或 DATETIME 列的定义中，可指定当前时间戳（同时、任一、均不）作为默认值和自动更新值。不同的列能指定不同的自动属性组合。以下案例展示了可能性：

除非明确指定，否则 TIMESTAMP 和 DATETIME 列不具备自动属性。唯一例外是：如果禁用 explicit_defaults_for_timestamp 系统变量，且第一个 TIMESTAMP 列未明确声明两者，那么该列就会同时拥有 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性。要想取消第一个 TIMESTAMP 列拥有自动属性的特性，可使用以下策略之一：

请考虑以下表定义：

CREATE TABLE t1 (
  ts1 TIMESTAMP DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
  ts1 TIMESTAMP NULL,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
  ts1 TIMESTAMP NULL DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP);

这些表具有以下属性：

如果 TIMESTAMP 和 DATETIME 列定义中的任意地方含有明确的小数秒精度参数，那么该列定义中所有该参数的值都必须一致。这是允许的情况：

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

这是不允许的情况：

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);

TIMESTAMP 初始化和 NULL 属性

若禁用 explicit_defaults_for_timestamp 系统变量，则 TIMESTAMP 列默认为 NOT NULL，即不能保存 NULL 值，当赋值 NULL 时，实际赋值为当前时间戳。为了允许 TIMESTAMP 列能保存 NULL 值，需要明确声明 NULL 属性。这种情况下，默认值变成了 NULL，除非通过指定不同值的 DEFAULT 子句进行覆盖。DEFAULT NULL 用于明确指定 NULL 为默认值（对于不声明 NULL 属性的 TIMESTAMP 列来说，NULL 是非法值）。如果 TIMESTAMP 列允许 NULL 值，那么赋值 NULL 就是 NULL，而不会转为当前时间戳。

以下表包含多个允许为 NULL 的 TIMESTAMP 列：

CREATE TABLE t
(
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT 0,
  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

允许为 NULL 的 TIMESTAMP 列不会在插入时使用当前时间戳，除非符合以下任一条件：

换句话说，dui'yu允许为 NULL 的 TIMESTAMP 列仅在其定时中包含 DEFAULT CURRENT_TIMESTAMP 时才会被自动初始化：

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

如果 TIMESTAMP 列允许为 NULL，但定义时不包含 DEFAULT CURRENT_TIMESTAMP，那么在插入时必须明确为当前日期和时间。假设表 t1 和 t2 的定义如下：

CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT NULL);

要让任一表中的 TIMESTAMP 列设置为插入时的当前时间戳，需要明确赋值。如：

INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
INSERT INTO t1 VALUES (NOW());

如果启用 explicit_defaults_for_timestamp 系统变量，那么只有在声明时包含 NULL 属性，TIMESTAMP 列才允许为 NULL。此外，无论声明 NULL 或 NOT NULL 属性，TIMESTAMP 列均不会因赋值为 NULL 而被转为当前时间戳。只有赋值为 CURRTENT_TIMESTAMP 或其代名词（如 NOW()）时，该列才为当前时间戳。

时间的小数

MySQL 的 TIME、DATETIME 和 TIMESTAMP 均支持小数，精度最高可达 6 位数（微秒）：

“日期和时间”类型之间的转换

在某种程度上，你能将值从一种时间类型转换为另一种时间类型。但是，值可能会有所变化或丢失信息。在所有情况下，时间类型之间的转换都取决于目标类型的有效值范围。例如，尽管 DATE、DATETIME 和 TIMESTAMP 的值可以使用相同格式的值指定，但这些类型的取值范围并不相同。TIMESTAMP 的值不能早于 1970 UTC 或晚于 '2038-01-19 03:14:07' UTC。这意味着日期 '1968-01-01' 对 DATE 和 DATETIME 来说是合法的，但对 TIMESTAMP 来说是不合法的，并会转换为 0。

转换 DATE 值：

转换 DATETIME 和 TIMESTAMP 值：

转换 TIME 值到其他时间类型时，会将 CURRENT_DATE() 的值作为日期部分。TIME 类型作为消耗时间（而不是一天中的时间）进行解析（译者注：即不局限于 24 小时内，且有可能是负数），然后与日期相加。这意味着，如果时间不在 ['00:00:00', '23:59:59'] 范围内时，则转换结果的日期与当前时间的日期是不同的。

假设，此时日期为 '2012-01-01'。TIME 类型的值分别是 '12:00:00'、'24:00:00' 和 '-12:00:00'，当它们转换为 DATETIME 或 TIMESTAMP 值时，得到结果分别是：'2012-01-01 12:00:00'、'2012-01-02 00:00:00' 和 '2011-12-13 12:00:00'。

从 TIME 转换为 DATE 时也与之类似，但会丢掉时间部分：'2012-01-01'、'2012-01-02' 和 '2011-12-31'。

可用显式转换替代隐式转换。例如，对于 DATE 和 DATETIME，DATE 值可通过添加时间部分——'00:00:00' 强行得到 DATETIME 类型。相反地，可通过忽略 DATETIME 的时间部分得到 DATE 类型，这可通过 CAST() 函数实现：

date_col = CAST(datetime_col AS DATE)

将 TIME 和 DATETIME 转换为数值形式（如通过加 +0）时，得到的结果类型取决于值是否含有小数部分。对于 TIME(N) 或 DATETIME(N)，当 N 为 0（或缺省）时，会转换为整型；当 N 大于 0 时，则转换为 DECIMAL 型：

mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;
+-----------+-------------+--------------+
| CURTIME() | CURTIME()+0 | CURTIME(3)+0 |
+-----------+-------------+--------------+
| 09:28:00  |       92800 |    92800.887 |
+-----------+-------------+--------------+
mysql> SELECT NOW(), NOW()+0, NOW(3)+0;
+---------------------+----------------+--------------------+
| NOW()               | NOW()+0        | NOW(3)+0           |
+---------------------+----------------+--------------------+
| 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
+---------------------+----------------+--------------------+

2 位数年份的日期

2 位数年份的日期是不明确的，因为世纪未知。由于 MySQL 内部使用 4 位数字存储年份，因此必须将这些解析为 4 位数字格式。

对于 DATETIME、DATE 和 TIMESTAMP，MySQL 会使用以下规则解析 2 位数的年份值：

对于 YEAR 类型，规则与上面一致，但有一个例外：当将数值 00 插入到 YEAR 时，会得到 0000 而不是 2000。要想 YEAR 将 0 解析为 2000，则需要指定为字符串类型的 '0' 或 '00' 。

请记住，这些规则只是对你想要的值进行合理猜测。如果 MySQL 使用这些规则未能产生你所需的值，那么请你提供明确的 4 位数年份值。

ORDER BY 可以正确排序 2 位数年份的 YEAR 值。

诸如 MIN() 和 MAX() 这些会将 YEAR 转为数值的函数，它们不能正确处理 2 位数年份。在这种情况下，需要将 YEAR 转为 4 位数的形式。

字符串数据类型

概述

字符串数据类型有：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET。

有关字符串数据类型的存储要求的信息，请看 Section 11.7, “Data Type Storage Requirements”。

关于处理字符串函数的说明，请查看 Section 12.7, “String Functions and Operators”。

字符串数据类型的句法

字符串数据类型有：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET。

在某些情况下，MySQL 可能会对 CREATE TABLE 和 ALTER TABLE 语句中的字符串类型列进行类型更改。有关信息请看 Section 13.1.20.7, “Silent Column Specification Changes”。

对于字符类字符串列（CHAR、VARCHAR 和 TEXT 类型），MySQL 以字符为单位解析长度规格。对于二进制类字符串列（BINARY、VARBINARY 和 BLOB 类型），MySQL 以字节为单位解析长度规格。

定义字符类字符串列使，可指定列的字符集和排序规则：

字符列的比较排序是基于分配给该列的排序规则。对于 CHAR、VARCHAR、TEXT、ENUM 和 SET 数据类型，你可以在声明时带上二进制（_bin）排序规则或 BINARY 属性，让比较排序是基于 underlying character code（字符编码） 而不是 lexical ordering（字典序）。

有关在 MySQL 中使用字符集的更多信息，请看 Chapter 10, Character Sets, Collations, Unicode。

MySQL 将 VARCHAR 值存储为：1 字节或 2 字节长度前缀 + 实际数据。长度前缀是用来表示值的字节数。如果 VARCHAR 列需要少于或等于 255 字节时使用 1 字节，如果值可能需要超过 255 字节时使用 2 字节。

注意：MySQL 遵循 SQL 标准规范，并不会移除 VARCHAR 值的尾随空格。

VARCHAR 是 CHARACTER VARYING 的缩写，NATIONAL VARCHAR 是使用某些预定义字符集定义 VARCHAR 列的标准 SQL 方式。MySQL 使用 utf8 作为预定义字符集。Section 10.3.7, “The National Character Set”。NVARCHAR 是 NATIONAL VARCHAR 的缩写。

CHAR 和 VARCHAR 类型

CHAR 和 VARCHAR 类型是类似的，不同点在于存储和检索。另外，它们支持的最大长度和是否保留尾随空格也不同。

CHAR 和 VARCHAR 类型均可在声明时指定长度，该长度表示打算存储的最大字符数。例如， CHAR(30) 最多可放 30 个字符。

CHAR 列长度是固定的，该长度是在表声明时指定。长度的取值为 [0, 255]。当存储 CHAR 值时，会在右侧填充空格至指定长度。当检索 CAHR 值时，尾随空格会被移除，除非启用 SQL 模式 PAD_CHAR_TO_FULL_LENGTH。

VARCAR 列是可变长字符串。长度的取值范围为 [0, 65535]。VARCHAR 的有效最大长度是取决于行的最大大小（65535 字节，所有列组成）和所使用的字符集。详情请看 Section 8.4.7, “Limits on Table Column Count and Row Size”。

与 CHAR 不同，VARCHAR 值存储为： 1 字节或 2 字节长度前缀 + 实际数据。长度前缀是用来表示实际值的字节数。如果值需要少于或等于 255 字节时使用 1 字节，如果值可能需要大于 255 字节时使用 2 字节。

如果未启用 SQL 严格模式，并且为 CHAR 或 VARCHAR 列分配的值超过了该列的最大长度，则该值会被裁剪至合适长度（译者注：裁剪末端）并产生警告。如果开启 SQL 严格模式，那么当裁剪非空格字符时（译者注：若裁剪末端空格后符合长度要求，则不报错），会导致报错（而不是警告），并插入值失败。详情请看 Section 5.1.11, “Server SQL Modes”。

对于 VARCHAR 列，无论使用哪种 SQL 模式，插入前都会裁剪超出列长度的尾随空格并产生警告。对于 CHAR 列，无论使用哪种 SQL 模式，插入前都会静默地裁剪超出列长度的尾随空格（译者注：即不会产生警告）。

存储 VARCHAR 值时不会对其进行填充。根据 SQL 标准，在存储和检索时都会保留尾随空格。

下表通过显示将各种字符串值存储到 CHAR(4) 和 VARCHAR(4) 列中的结果来说明 CHAR 和 VARCHAR 之间的不同（假设该列使用单字节字符集，如 latin1）：

Value CHAR(4) Storage Required VARCHAR(4) Storage Required
'' ' ' 4 bytes '' 1 byte
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

表格最后一行的值仅在不启用严格模式时适用；若开启严格模式，超出列长度的值都不会被存储，并报错。

InnoDB 会将大于或等于 768 字节的定长字段（Field）编码为可存在 off-page 的可变长字段。例如，CHAR(255) 列可能会超过 768 字节，因为存在最大字节长度大于 3 的字符集，如 utf8mb4。

当将一个值分别存在 CHAR(4) 和 VARCHAR(4) 列时，检索出的值并不总是一样的。因为 CHAR 列在检索时会删除尾随空格。以下案例说明了这种差异：

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab  )              | (ab)                |
+---------------------+---------------------+
1 row in set (0.06 sec)

CHAR(4) 和 VARCHAR(4) 列的比较排序是基于列设置的字符集排序规则。

大多数 MySQL 排序规则拥有填充属性 PAD SPACE。例外是基于 UCA 9.0.0 或更高版本的 Unicode 排序规则，其填充属性为 NO PAD。详情请看 Section 10.10.1, “Unicode Character Sets”。

通过 INFORMATION_SCHEMA 的 COLLATIONS 表可查看特定排序规则的填充属性。

填充属性决定了非二进制字符串（CHAR、VARCHAR 和 TEXT 值）在比较时如何处理尾随空格。NO PAD 排序规则将尾随空格视为与其他字符一样。而 PAD SPACE 排序规则是不在意尾随空格的。即在字符串比较时，会忽略任何尾随空格。在此上下文中的“比较”，是不包括 LIKE 模式匹配操作符，即它是在意尾随空格的。案例如下：

mysql> CREATE TABLE names (myname CHAR(10));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO names VALUES ('Jones');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT myname = 'Jones', myname = 'Jones  ' FROM names;
+--------------------+--------------------+
| myname = 'Jones'   | myname = 'Jones  ' |
+--------------------+--------------------+
|                  1 |                  1 |
+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT myname LIKE 'Jones', myname LIKE 'Jones  ' FROM names;
+-----------------------+-----------------------+
| myname LIKE 'Jones'   | myname LIKE 'Jones  ' |
+-----------------------+-----------------------+
|                     1 |                     0 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)

对于所有 MySQL 版本均是如此，并且不受服务器 SQL 模式影响。

注意：关于 MySQL 字符集和排序规则的更多信息，请查看 Chapter 10, Character Sets, Collations, Unicode。 关于存储要求的其他信息，请查看 Section 11.7, “Data Type Storage Requirements”。

对于移除尾随字符或在比较时忽略它们的情况，如果列具有唯一索引（要求值唯一），则将仅尾随字符数不同的值插入列时，会出现 duplicate-key 错误。例如，如果表已有 'a' 的情况下，再尝试存储 'a ' 时，会导致 duplicate-key 错误。

BINARY 和 VARBINARY 类型

“BINARY 和 VARBINARY 类型”与“CAHR 和 VARCHAR 类型”类似，不同点在于前者存储的是二进制字符串，而后者存储的是非二进制字符串。换句话说，存的是字节字符串而不是字符类字符串。这意味着他们拥有 binary 字符集和排序规则，且比较排序是基于字节的字符编码（numeric values of the bytes in the values）。

“BINARY 和 VARBINARY”的最大长度与“CHAR 和 VARCHAR”相同，但测量单位是字节而不是字符。

BINARY 和 VARBINARY 数据类型与 CHAR BINARY 和 VARCHAR BINARY 数据类型不同。后者的 BINARY 属性不会导致该列被视为二进制字符串列，而是让该列使用列字符集的二进制（_bin）排序规则（若未指定列字符集，则采用表的默认字符集），并且该列本身不会存储二进制字符串，而是依然会存储非二进制字符串。例如，如果默认字符集是 utf8mb4，CHAR(5) BINARY 就会被视为 CHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin。这显然与 binary 字符集和排序规则的，且能存储 5 字节二进制字符串的 BINARY(5) 不同。关于 binary 字符集的 binary 排序规则与非二进制字符集的 _bin 排序规则不同之处，请查看 Section 10.8.5, “The binary Collation Compared to _bin Collations”。

如果未启用 SQL 严格模式，并且为 BINARY 和 VARBINARY 列分配的值超过了该列的最大长度，则该值会被裁剪至合适长度并产生警告。反之，裁剪会导致报错（而非警告），并且插入值失败。详情请看 Section 5.1.11, “Server SQL Modes”。

存储 BINARY 值时，会在右侧填充指定的值至指定长度。填充值是 0x00（the zero byte）。插入时会右侧填充 0x00，且在检索时不会删除尾随字节。所有字节在比较（含 ORDER BY 和 DISTINCT）时均有效。0x00 与空格在比较操作中是不同的，其中 0x00 排在空格之前。

案例：对于 BINARY(3) 列，插入 'a ' 时，实际变成插入 'a \0'；同样地，'a\0' 变成 'a\0\0'。在检索时，插入的两个值均保持不变。

对于 VARBINARY，插入不会填充，检索不会移除字节。所有字节在比较（含 ORDER BY 和 DISTINCT）时均有效。0x00 与空格在比较操作中是不同的，0x00 排在空格之前。

对于移除尾随填充字节或在比较时忽略它们的情况，如果列具有唯一索引（要求值唯一），则将仅尾随填充字节数不同的值插入列时，会出现 duplicate-key 错误。例如，如果表已有 'a' 的情况下，再尝试存储 'a\0' 时，会导致 duplicate-key 错误。

如果打算使用 BINARY 数据类型存储二进制数据，并且要求检索的值与存储的值完全相同，则应仔细考虑上述填充和移除的特性。以下案例说了 BINARY 值填充的 0x00 如何影响列值的比较：

mysql> CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t SET c = 'a';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;
+--------+---------+-------------+
| HEX(c) | c = 'a' | c = 'a\0\0' |
+--------+---------+-------------+
| 610000 |       0 |           1 |
+--------+---------+-------------+
1 row in set (0.09 sec)

若想检索值与存储值一致（即无填充），则最好使用 VARBINARY 或 BLOB 之类的数据类型。

BLOB 和 TEXT 类型

BLOB 是一个二进制大对象，能承载可变数量的数据。有 4 种 BLOB 类型，分别是：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。它们仅在值的最大长度上有所不同。TEXT 类型也有 4 种，分别是：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。它们分别对应 4 种 BLOB 类型，并具有相同的最大长度和存储要求。详情请看 Section 11.7, “Data Type Storage Requirements”。

BLOB 值被视为二进制字符串（字节字符串），拥有 binary 字符集和排序规则，比较排序均基于字节的数值（the numeric values of the bytes）；TEXT 指被视为非二进制字符串（字符类字符串），拥有二进制以外的字符集，比较排序基于字符集的排序规则。

若未开启 SQL 严格模式，向 BLOB 或 TEXT 列指定超过列最大长度的值，那么会将值裁剪至合适长度，并产生一个警告。如果开启严格的 SQL 模式，那么裁剪非空格字符时（译者注：若裁剪末端空格后符合长度要求则不报错），会导致报错（而不是警告），并插入值失败。详情请看 Section 5.1.11, “Server SQL Modes”。

对于 TEXT 列裁剪超出范围的尾随空格时，总会产生一个警告，而不管 SQL 模式。

对于 TEXT 和 BLOB 列，插入不会填充，检索不会移除字节。

如果对 TEXT 列建立索引，则索引条目的比较会在末尾填充空格。这意味着，如果索引要求值唯一，则将仅尾随空格数不同的值插入列时，则会导致 duplicate-key 错误。例如，如果表已有 'a' 的情况下，再尝试存储 'a ' 时，会导致 duplicate-key 错误。

在大部分情况下，你可将 BLOB 列视为能设置任意大小 VARBINARY 列。类似地，你可将 TEXT 列视为 VARCHAR 列。“BLOB 和 TEXT”与“VARBINARY 和 VARCHAR”不同的点如下：

若对 TEXT 数据类型使用 BINARY 属性，则该列会被指定为当前字符集的二进制排序规则（_bin）。

LONG 和 LONG VARCHAR 映射为 MEDIUMTEXT 数据类型，这是一项兼容性功能。

MySQL Connector/ODBC 将 BLOB 值定义为 LONGVARBINARY，将 TEXT 值定义为 LONGVARCHAR。

因为 BLOB 和 TEXT 值可能会非常长，因此在使用时可能会遇到一些限制：

每个 BLOB 和 TEXT 值在内部都由单独分配的对象表示。这与所有其他数据类型不同，其他数据类型都在表打开时为每列分配一次存储空间。

在某些情况下，你可能希望将二进制数据（例如媒体文件）存储在 BLOB 或 TEXT 列中。你可能发现 MySQL 的字符串处理函数对处理此类数据很有用。详情请看 Section 12.7, “String Functions and Operators”。出于安全和其他原因，通常建议使用应用程序代码完成这样的功能而不是为程序用户提供 FILE 权限。你可以在 MySQL 论坛 中讨论各种语言和平台的细节。

ENUM 类型

ENUM 是一个字符串对象，其值是从允许值列表中选择，而允许值列表是在创建表的列定义中明确枚举。该类型具有以下优点：

同时，也要考虑以下潜在问题：

本章目录

创建和使用 ENUM 列

枚举值必须是带引号的字符串字面量。创建一个含有 ENUM 列的表：

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 |
+---------+--------+
UPDATE shirts SET size = 'small' WHERE size = 'large';
COMMIT;

向该表插入 1 百万行 'medium' 值，需要 1 百万字节存储空间。而如果存储实际字符串 'medium' 到 VARCHAR 列，则需要 600 百万字节。（译者注：应该是 700 百万字节？因为 VARCHAR 的值有 1 或 2 字节长度前缀）

枚举字面量的索引值

每个枚举值都有一个索引值：

例如，指定为 ENUM('Mercury'，'Venus'，'Earth') 的列具有如下值，每个值对应的索引值也有展示。

Value Index
NULL NULL
'' 0
'Mercury' 1
'Venus' 2
'Earth' 3

ENUM 列最多拥有 65535 个不同值。

若以数值上下文检索 ENUM 值，则返回该值的索引值。例如，你这样检索 ENUM 列的索引值：

mysql> SELECT enum_col+0 FROM tbl_name;

期望参数为数值的函数，如 SUM() 或 AVG()，会在必要时将参数转为数值。对于 ENUM 值，在计算中会使用索引值。

枚举字面量的处理

在创建表时，会自动删除 ENUM 成员值的尾随空格。

检索时，ENUM 列的值会以列定义中的大小字母进行显示。注意，ENUM 列可指定字符集和排序规则。对于二进制或大小写敏感的排序规则，在为列赋值时会考虑字母大小写。

若向 ENUM 列存储数值时，该数值会被看作索引值，并且存储的值是该索引值对应的枚举成员。（然而，这不适用于将所有输入都视为字符串的 LOAD_DATA）。如果数值被引号包起来，则在没有与枚举值列表匹配的情况下，依然会被解析为索引值。出于这个原因，不建议在 ENUM 列定义中使用与数值相近的枚举值，毕竟很容易造成混淆。例如，以下列拥有枚举成员 '0'、'1'、'2'，但对应的索引值是 1、2、3：

numbers ENUM('0','1','2')

当存储 2 时，会被解析为索引值，即实际存储为 '1' （对应索引值为 2）。当存储 '2' 时，因匹配枚举值，所以存为 '2'。当存储 '3' 时，因未匹配任何枚举值，所以会被视为索引值，实际存储为 '2'（对应索引值为 3）。

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql> SELECT * FROM t;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+

要确定 ENUM 列所有可能值，请使用 SHOW COLUMNS FROM tbl_name LIKE 'enum_col'，并在输出的 Type 列中分析 ENUM 定义。

在 C API 中，ENUM 值以字符串形式返回。有关使用结果集元数据区分它与其他字符串的信息，请看 Section 28.7.4, “C API Data Structures”。

空或 NULL 枚举值

在某些情况下，枚举值可为空字符串（''）或 NULL：

枚举排序

ENUM 值根据其索引值进行排序，该索引值取决于在列声明中枚举成员列表的顺序。例如，对于 ENUM('b', 'a')，'b' 排在 'a' 前。空字符串排在非空字符串前，NULL 排在所有其它枚举值前。

为了防止在 ENUM 列上使用 ORDER BY 子句时出现意外结果，请使用以下技术之一：

枚举的限制

枚举值不能是表达式，即使求值结果是字符串。

例如，以下 CREATE TABLE 语句会执行失败，因为 CONCAT 函数不能用于构建枚举值：

CREATE TABLE sizes (
    size ENUM('small', CONCAT('med','ium'), 'large')
);

而且不能将用户变量作为枚举值。以下语句会执行失败：

SET @mysize = 'medium';

CREATE TABLE sizes (
    size ENUM('small', @mysize, 'large')
);

强烈建议不要使用数值作为枚举值，因为它不会在适当的 TINYINT 或 SMALLINT 类型上节省存储空间，并且很容易造成字符串与对应索引值的混淆（两者可能不相等）。如果确实要使用数值作为枚举值，请始终将其用引号包起来。若省略引号，则会将该数值视为索引值。关于如何将一个数值字符串被错误地用作索引值，请查看 Handling of Enumeration Literals。

定义中存在重复值会导致产生警告。若开启 SQL 严格模式，则会导致错误。

SET 类型

SET 是字符串对象，能拥有 0 个或多个值，每个值都必须从创建表时指定的允许值列表中选取。由多个 set 成员组成的 SET 的值，须用逗号（,）分隔成员值。SET 成员值本身不应该包含逗号。

例如，SET('one', 'two') NOT NULL 列的值可以为以下任意一个：

''
'one'
'two'
'one,two'

SET 列最多能拥有 64 个不同的成员。

若定义中含有重复值时，则会产生警告。若开启严格模式，则会报错。

在创建表时，会自动删除 SET 成员值的尾随空格。

检索时，显示的 SET 列值就是定义时的大小写字母。注意，SET 列可指定字符集和排序规则。对于二进制或大小写敏感的排序规则，在为列分配值时会考虑字母大小写。

MySQL 以数值形式存储 SET 的值，而存储值的最低位（bit）对应第一个 set 成员。若在数值上下文中检索 SET 值，则检索到的值为组成该列值的 set 成员相对应的 bit 集合。例如，这样可以从 SET 列中检索出数值：

mysql> SELECT set_col+0 FROM tbl_name;

如果将数值存储到 SET 列中，则用数值的二进制表示形式的位（bit）确定列值中的 set 成员。对于指定为 SET('a','b','c','d') 的列，set 成员具有以下十进制和二进制的值。

SET Member Decimal Value Binary Value
'a' 1 0001
'b' 2 0010
'c' 4 0100
'd' 8 1000

若向该列赋值为 9（对应二进制是 1001），那么第一和第四个 SET 值成员会被选中，所以最终结果值为 'a,d'。

插入包含多个 SET 元素的值时，这些元素可以以任意顺序排列，另外也不会关心元素的出现次数。而检索该值时，值的每个元素均只会出现一次，并且元素是根据创建表时指定的顺序进行排序。假设列指定为 SET('a', 'b', 'c', 'd')：

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

如果你插入这些值 'a,d'、'd,a'、'a,d,d'、'a,d,a' 和 'd,a,d'：

mysql> INSERT INTO myset (col) VALUES 
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

所有这些值在检索时都会呈现为 'a,d'：

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.04 sec)

当向 SET 列插入一个不支持的值，这个值会被忽略，并产生一个警告：

mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.04 sec)

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
6 rows in set (0.01 sec)

若开启 SQL 严格模式，则尝试插入一个非法 SET 值时会报错。

SET 列值按数值排序。NULL 值排在所有非 NULL 的 SET 值前。

期望参数为数值的函数，如 SUM() 或 AVG()，会在必要时将参数转为数值。对于 SET 值，会将其转换为数值后使用。

通常，使用 FIND_IN_SET() 函数或 LIKE 操作符搜索 SET 值：

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

第一句是查找那些 set_col 包含 set 成员 value 的行。第二句相似，但不相同：查找那些 set_col 任意地方包含 value，甚至作为一个 set 成员的子字符串的行。

也允许使用以下语句：

mysql> SELECT * FROM tbl_name WHERE set_col & 1;
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';

第一句查找的是包含第一个 set 成员的值。第二句是精确匹配。注意第二句的比较，将 'var1,var2' 与 'var2,var1' 分别与 set 值进行比较，会得到不同的比较结果。应该按照列定义中列出的顺序指定值。

要确定 SET 列所有可能值，请使用 SHOW COLUMNS FROM tbl_name LIKE 'set_col'，并在输出的 Type 列分析 SET 定义。

在 C API 中，SET 值以字符串形式返回。有关使用结果集元数据区分它与其他字符串的信息，请看 Section 28.7.4, “C API Data Structures”。

空间数据类型

暂未涉及该领域数据，故暂不翻译。

JSON 数据类型

目录

MySQL 支持 RFC 7159 定义的原生 JSON 数据类型，该类型可以有效访问 JSON（JavaScript Object Notation）文档中的数据。与存储在字符串列的 JSON 序列化字符串相比，JSON 数据类型具有以下优点：

MySQL 8.0 还支持 RFC 7396 定义的 JSON Merge Path 格式，可通过 JSON_MERGE_PATH() 函数进行使用。有关示例和更多信息，请查看此函数的描述以及 Normalization, Merging, and Autowrapping of JSON Values。

注意：本讨论中，使用 monotype 字体的 JSON 是专门表示 JSON 数据类型的，而使用常规字体的 “JSON” 通常表示 JSON 数据。（译者注：前者用代码标识符 `` 表示）

暂未涉及该领域数据，故暂不翻译。

数据类型的默认值

数据类型的默认值可以是显式或隐式的。

可通过 DEFAULT value 子句显式指定列的默认值。例如：

CREATE TABLE t1 (
  i     INT DEFAULT -1,
  c     VARCHAR(10) DEFAULT '',
  price DOUBLE(16,2) DEFAULT 0.00
);

SERIAL DEFAULT VALUE 是一种特殊情况。在整型列的定义中，它是 NOT NULL AUTO_INCREMENT UNIQUE 的别名。

显式 DEFAULT 子句处理的某些方面受版本影响，如下所述。

MySQL 8.0.13 起的显式默认值的情况

DEFAULT 子句指定的默认值可以是字面量常量或表达式。后者需要放在括号内，以将其与字面量常量默认值进行区分。例如：

CREATE TABLE t1 (
  -- literal defaults
  i INT         DEFAULT 0,
  c VARCHAR(10) DEFAULT '',
  -- expression defaults
  f FLOAT       DEFAULT (RAND() * RAND()),
  b BINARY(16)  DEFAULT (UUID_TO_BIN(UUID())),
  d DATE        DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR),
  p POINT       DEFAULT (Point(0,0)),
  j JSON        DEFAULT (JSON_ARRAY())
);

特例情况是：对于 TIMESTAMP 和 DATETIME 列，你可以将无括号包围的 CURRENT_TIMESTAMP 函数作为默认值。详情请看 Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”。

仅当值作为表达式编写时，才可为 BLOB、TEXT、GEOMETRY 和 JSON 数据类型指定默认值，即使表达式值是字面量：

表达式默认值必须遵守以下规则。如果表达式包含不允许的部分，则会报错。

对于 CREATE TABLE ... LIKE 和 CREATE TABLE ... SELECT，目标表会保留原始表的表达式默认值。

如果表达式默认值引用一个非确定性函数，那么任何能导致表达式求值的语句对于基于语句复制来说都是不安全的。这包括 INSERT、UPDATE 和 ALTER TABLE 等语句。在这种情况下，如果禁用了二进制日志记录，则语句将正常执行。如果启用了二进制日志记录，且将 binlog_format 设置为 STATEMENT，则会记录并执行该语句，但会向错误日志写入一条警告消息，因为复制从属节点可能会出现分歧。当 binlog_format 设置为 MIXED 或 ROW 时，将不执行该语句，并向错误日志写入一条错误消息。

插入新行时，可通过省略列名或将列指定为 DEFAULT 来插入具有表达式默认值的列的默认值：

mysql> CREATE TABLE t4 (uid BINARY(16) DEFAULT (UUID_TO_BIN(UUID())));
mysql> INSERT INTO t4 () VALUES();
mysql> INSERT INTO t4 () VALUES(DEFAULT);
mysql> SELECT BIN_TO_UUID(uid) AS uid FROM t4;
+--------------------------------------+
| uid                                  |
+--------------------------------------+
| f1109174-94c9-11e8-971d-3bf1095aa633 |
| f110cf9a-94c9-11e8-971d-3bf1095aa633 |
+--------------------------------------+

但是，仅允许对具有字面量默认值的列使用 DEFAULT(col_name) 指定指名列的默认值，而不允许对具有表达式默认值的列使用。

并非所有存储引擎都支持表达式默认值，对于那些不支持的，会出现 ER_UNSUPPORTED_ACTION_ON_DEFAULT_VAL_GENERATED 错误。

如果默认值的数据类型与列声明的类型不同，则会根据 MySQL 常规的类型转换规则将其隐式转换为声明类型。详情请看 Section 12.2, “Type Conversion in Expression Evaluation”。

MySQL 8.0.13 前的显式默认值的情况

DEFAULT 子句定义的默认值必须是字面量常量，即不能是函数或表达式。例如，不能为日期列的默认值设置为函数，如 NOW() 或 CURRENT_DATE。唯一例外是：对于 TIMESTAMP 和 DATETIME 列，可以指定 CURRENT_TIMESTAMP 作为默认值。详情请看 Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”。

无法为 BLOB、TEXT GEOMETRY 和 JSON 数据类型指定默认值。

如果默认值的数据类型与列声明的类型不同，则会根据 MySQL 常规的类型转换规则将其隐式转换为声明类型。详情请看 Section 12.2, “Type Conversion in Expression Evaluation”。

隐式默认值的情况

如果无显式的 DEFAULT 值，则 MySQL 按照以下方式确定默认值：

如果列可接受 NULL 值，则使用显式 DEFAULT NULL 子句定义该列。

如果列不接受 NULL 值，则 MySQL 定义该列时不使用显式 DEFAULT 子句（译者注：不理解这句话）。例外：若列定义为 PRIMARY KEY 但无显式声明 NOT NULL，则 MySQL 将其创建为 NOT NULL 列（因为 PRIMARY KEY 必须为 NOT NULL）。

向没有显式 DEFAULT 子句的 NOT NULL 列输入数据时，如果 INSERT 或 REPLACES 语句不包含该列的值，或 UPDATE 语句设置该列为 NULL，则 MySQL 会根据当时的 SQL 模式处理该列：

假设表 t 定义如下：

CREATE TABLE t (i INT NOT NULL);

在这种情况下，i 没有明确的默认值，因此在严格模式下，下面的每条语句均会报错，并且不会插入行。当不开启严格模式，前两句会插入隐式默认值，第三句会报错，因为 DEFAULT(i) 无法产生值：

INSERT INTO t VALUES();
INSERT INTO t VALUES(DEFAULT);
INSERT INTO t VALUES(DEFAULT(i));

详情请看 Section 5.1.11, “Server SQL Modes”。

SHOW CREATE TABLE 语句能显示指定表的哪些列具有显式的 DEFAULT 子句。

隐式默认值的定义如下：

数据类型的存储要求

目录：

磁盘上表数据的存储要求取决于几个因素。不同存储引擎表示的数据类型和存储的原始数据均不同。表数据可能会被压缩，无论是针对列还是整行，都会使得表或列的存储要求的计算复杂化。

尽管磁盘上的存储布局有所不同，但 MySQL 内部 API（用于通信和交换表行信息） 使用的是适用于所有存储引擎的一致性数据结构。

本节讲述 MySQL 支持的每种数据类型的存储要求的信息，包括使用固定大小表示的数据类型在存储引擎的内部格式和大小。这些信息按类别或存储引擎列出。

表的内部表示形式的最大行大小是 65535 字节，即使存储引擎能够支持更大的行。以上表述不包括 BLOB 和 TEXT 列，它们仅占用该大小的 9 到 12 字节。对于 BLOB 和 TEXT 数据，该信息存储在与行缓冲区不同的内存区域中。不同存储引擎以不同方式处理此数据的分配和存储。有关更多信息，请查看 Chapter 16, Alternative Storage Engines 和 Section 8.4.7, “Limits on Table Column Count and Row Size”。

InnoDB 表的存储要求

关于 InnoDB 表的存储要求，请看 Section 15.10, “InnoDB Row Formats”。

NDB 表的存储要求

重要：NDB 表使用 4 字节对齐；所有 NBD 数据存储均以 4 字节的倍数完成。因此，通常需要 15 字节的列值在 NDB 表中则需要 16 字节。例如，在 NDB 表中，由于对齐的因素，TINTINT、SMALLINT、MEDIUMINT 和 INTEGER(INT) 类型列的每条记录都需要 4 字节。

每个 BIT(M) 列需要 M bit 的存储空间。尽管单个 BIT 列无需对齐 4 字节，但是 NDB 为每行所有的 BIT 列的前 1-32 bit 保留 4 字节（32 bit），然后为 33-64 bit 保留另外的 4 字节，以此类推。

尽管 NULL 本身不需要任何存储空间，但如果表定义包含允许为 NULL 的任意列（最多 32 个 NULL 列），则 NDB 为每行保留 4 字节。（如果定义的 NDB 集群表拥有超过 32 个 NULL 列（最多 64 个 NULL 列），则为每行保留 8 字节。）

使用 NDB 存储引擎的每个表都需要一个主键。若未定义主键，则 NDB 会创建一个“隐藏”主键。该隐藏主键消耗每行 31 - 35 字节。

可使用 ndb_size.pl Perl 脚本来估计 NDB 的存储要求。它会连接到当前的 MySQL 数据库（不是 NDB 集群），并创建有关使用 NDB 存储引擎时数据库需要多少空间的报告。有关更多信息，请看 Section 22.4.28, “ndb_size.pl — NDBCLUSTER Size Requirement Estimator”。

数值类型的存储要求

Data Type Storage Required
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT, INTEGER 4 bytes
BIGINT 8 bytes
FLOAT(p) 4 bytes（p [0, 24]）, 8 bytes（p [25, 53]）
FLOAT 4 bytes
DOUBLE [PERCISION], REAL 8 bytes
DECIMAL(M,D),NUMERIC(M,D) 不定，看下面讨论
BIT(M) 大约 (M+7)/8 bytes

DECIMAL（和 NUMERIC）列值使用二进制格式表示，它将 9 个十进制（基数 10）数字打包为 4 字节。每个值的整数和小数部分（的存储）需要分别独立确定。每 9 位数字需要 4 字节，而“剩余”数字需要小于或等于 4 字节。下表给出了多余数字的存储要求。

Leftover Digits Number of Bytes
0 0
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4

“日期和时间”类型的存储要求

从 MySQL 5.6.4 开始，TIME、DATETIME 和 TIMESTAMP 列允许拥有小数部分，这部分需要额外 0 到 3 字节。

Data Type Storage Required Before MySQL 5.6.4 Storage Required as of MySQL 5.6.4
YEAR 1 byte 1 byte
DATE 3 bytes 3 bytes
TIME 3 bytes 3 bytes + 小数部分的存储空间
DATETIME 8 bytes 5 bytes + 小数部分的存储空间
TIMESTAMP 4 bytes 4 bytes + 小数部分的存储空间

从 MySQL 5.6.4 开始，YEAR 和 DATE 的存储需求保存不变，但 TIME、DATETIME 和 TIMESTAMP 的表示形式变得不同。DATETIME 的存储效率更高，非小数部分从 8 字节变为 5 字节，并且这三者的小数部分需要 0 到 3 字节，这具体取决于存储值的小数精度。

小数部分的精度 Storage Required
0 0 bytes
1,2 1 byte
3,4 2 bytes
5,6 3 bytes

例如，TIME(0)、TIME(2)、TIME(4) 和 TIME(6) 分别需要 3、4、5 和 6 字节。TIME 和 TIME(0) 是等价的，故需要相同的存储空间。

有关时间值内部表示的详情信息，请看 MySQL Internals: Important Algorithms and Structures。

字符串的存储要求

在下表中，M 表示列声明的长度，对于非二进制字符串，以字符为单位；对于二进制字符串，以字节为单位。L 表示指定字符串值的实际字节长度。

Data Type Storage Required
CAHR(M) 紧凑的 InnoDB 行格式优化了拥有不同长度字符集的存储。详情请看 StartFragment COMPACT Row Format Storage CharacteristicsEndFragment。除此之外，就是 M x w 字节，M 的取值范围为 [0, 255]，w 是字符集中最长字符所需的字节数。
BINARY(M) M 字节，M 的取值范围为 [0,255]。
VARCHAR(M),VARBINARY(M) 若列值需要 [0,255] 字节，则共需要 L + 1 字节；若列值需要大于 255 字节，则共需要 L + 2 字节。
TINYCHAR,TINYTEXT L + 1 字节，其中 L < 2^8
BLOB,TEXT L + 2 字节，其中 L < 2^16
MEDIUMBLOB,MEDIUMTEXT L + 3 字节，其中 L < 2^24
LONGGLOB,LONGTEXT L + 4 字节，其中 L < 2^32
ENUM('value1','value2',...) 1 或 2 字节，取决于枚举值的数量（最多为 65535 个值）
SET('value1', 'value2',...) 1、2、3、4 或 8 字节，取决于 set 成员的数量（最多 64 个成员）

可变长字符串类型存储的是：长度前缀 + 实际数据。长度前缀需要 [1, 4] 字节，这具体取决于数据类型，并且前缀的值为 L（字符串的字节长度）。例如，存储 MEDIUMTEXT 值需要 L 字节存储实际值，再加上 3 字节存储实际值的长度。

要计算用于存储特定 CHAR、VARCHAR 或 TEXT 列值的字节数，必须要考虑该列的字符集以及该值是否含有多字节字符。特别是 utf8 Unicode 字符集，因为它并非所有字符都使用相同数量的字节。utf8mb3 和 utf8mb4 字符集的每个字符分别最多需要 3 和 4 字节。有关用于不同类别的 utf8mb3 或 utf8mb4 字符的存储的详细信息，请看 Section 10.9, “Unicode Support”。

VARCHAR、VARBINARY、BLOB 和 TEXT 类型都是可变长类型。对于各自的存储要求，取决于以下因素：

例如，VARCHAR(255) 列可容纳最大长度为 255 个字符的字符串。假设该列使用 latin1 字符集（每个字符均为 1 字节），则实际需要的存储空间是字符串长度（L）+ 用于记录字符串长度的 1 字节。对于字符串 'abcd'，L 是 4，所以存储要求为 5 字节。如果该列更改声明为 2 字节的 ucs2 字符集，则存储要求是 10 字节：'abcd' 的长度为 8 字节，并且需要 2 字节存储长度，因为最大长度大于 255 字节（最大为 510 个字节）。

VARCHAR 或 VARBINARY 列可存储的有效最大字节数取决于 65535 字节的最大行大小（row size），该大小是所有列之间共享。对于存储多字节字符的 VARCHAR 列，最大有效字符数会变少。例如，utf8mb4 字符集的每个字符最多需要 4 字节，因此使用 utf8mb4 字符集的 VARCHAR 列可声明为最多 16383 个字符。详情请看 Section 8.4.7, “Limits on Table Column Count and Row Size”。

InnoDB 会将大于或等于 768 字节的定长字段（Field）编码为可存储在 off-page 的可变长字段。例如，CHAR(255) 列可能会超过 768 字节，因为存在最大字节长度大于 3 的字符集，如 utf8mb4。

NDB 存储引擎支持可变宽列。这意味着 NDB 集群中的 VARCHAR 列和其他存储引擎一样，需要相同大小的存储空间，然而这些值是 4 字节对齐的。因此，对于存储在字符集为 latin1 的 VARCHAR(50) 列的 'abcd' 字符串，需要 8 字节（在相同条件下，MyISAM 表则需 5 字节）。

TEXT 和 BLOB 列在 NBD 中的实现方式有所不同；TEXT 列中的每一行（译者注：即 TEXT 列的每个值）都是由两个独立的部分组成。其中一部分是固定大小（256 字节），并且实际存储在原始表中，另一部分是存储在隐藏表中的，超出 256 字节的数据。第二个表的行大小始终为 2000 字节。这意味着，如果 size <= 256（size 表示行的大小），则 TEXT 列大小是 256；否则大小为 256 + size + (2000 × (size − 256) % 2000)。

ENUM 对象的大小由不同枚举值的数量决定。1 字节用于最多 255 个枚举值；2 字节用于 [256, 65535] 个枚举值。详情请看 See Section 11.3.5, “The ENUM Type”。

SET 对象的大小由不同 set 成员的数量决定。如果 set 大小为 N（译者注：要理解这个“大小”，需要先了解 SET 值的内部表示。1 个 set 成员占 1 bit，64 个成员，则占 64 bit），则对象占用 (N + 7)/8 字节，向上取整为 1、2、3、4 或 8 字节。一个 SET 最多可拥有 64 个成员。详情请看 Section 11.3.6, “The SET Type”。

空间类型的存储要求

暂未涉及，后续再翻译。

JSON 的存储要求

通常，JSON 列的存储需求与 LONGBLOB 或 LONGTEXT 列存储要求大致相同。也就是说，JSON 文档占用的空间与将该文档序列化后（译者注：即将 JSON 字符串表现形式）存储在这些类型中所用的空间大致相同。然而，存储在 JSON 文档的各个值的二进制编码会产生额外的开销，这其中包括需要查找的元数据和字典。例如，存储在 JSON 文档中的字符串需要 4 到 10 字节的额外存储空间，这具体取决于字符串的长度和存储该字符串的对象或数组的大小。

另外，MySQL 对存储在 JSON 列中的 JSON 文档的大小施加了限制，以使其不能大于 max_allowed_packet 的值。

为列选择正确的类型

为了获得最佳存储，在所有情况下都应该尽量使用最精确的类型。例如，如果一个整型列的取值范围是 [1, 99999]，那么 MEDIUMINT UNSIGNED 是最佳的类型。因为在所有符合要求的类型中，该类型占用存储空间最少。

在 DECIMAL 列中，所有基础计算（+、-、*、/）都以 65 个十进制数字的精度完成，详情请看 Section 11.1.1, “Numeric Data Type Syntax”。

如果精度不太重要，或速度是最高优先级，则 DOUBLE 类型可能就足够了。为了获得高精度，可以始终将其转换为存储在 BITINT 中的定点类型。这使得你能够使用 64 位整数进行所有计算，然后根据需要将结果转换回浮点数。

使用其他数据库引擎中的数据类型

为了便于使用为其他供应商的 SQL 实现编写的代码，MySQL 映射了数据类型，如下表所示。这些映射使得从其他数据库系统导入表定义到 MySQL 变得更加简单。

Other Vendor Type MySQL Type
BOOL TINYINT
BOOLEAN TINYINT
CHARACTER VARYING(M) VARCHAR(M)
FIXED DECIMAL
FLOAT4 FLOAT
FLOAT8 BOUBLE
INT1 TINYINT
INT2 SMALLINT
INT3 MEDIUMINT
INT4 INT
INT8 BIGINT
LONG VARBINARY MEDIUMBLOB
LONG VARCHAR MEDIUMTEXT
LONG MEDIUMTEXT
MIDDLEINT MEDIUMINT
NUMERIC DECIMAL

数据类型映射在创建表时发生，此后原始类型就会失效。如果使用其他供应商的类型创建表，然后执行 DESCRIBE tbl_name 语句，MySQL 会使用等效的 MySQL 类型报告表的结构。例如：

mysql> CREATE TABLE t (a BOOL, b FLOAT8, c LONG VARCHAR, d NUMERIC);
Query OK, 0 rows affected (0.00 sec)

mysql> DESCRIBE t;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a     | tinyint(1)    | YES  |     | NULL    |       |
| b     | double        | YES  |     | NULL    |       |
| c     | mediumtext    | YES  |     | NULL    |       |
| d     | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)