字符串数据类型

概述

字符串数据类型有：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 以字节为单位解析长度规格。

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

CHARACTER SET 指定字符集。若需要，也可通过 COLLATE 属性指定字符集的排序规则。如：
```
 CREATE TABLE t
 (
     c1 VARCHAR(20) CHARACTER SET utf8,
     c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
 );
```
以上表定义创建了名为 c1 的列，该列的字符集为 utf8，并采用默认的字符集排序规则。c2 列的字符集为 latin1，并采用大小写敏感（_cs）的排序规则。
Section 10.3.5, “Column Character Set and Collation” 介绍了在缺少 CHARACTER SET 和 COLLATE 属性（之一或全部）时，如何分配字符集和排序规则。
CHARSET 是 CHARACTER SET 的代名词。
为字符类字符串列指定 CHARACTER SET binary 属性时，会使该列创建为相应的二进制类字符串类型：CHAR 变为 BINARY，VARCHAR 变为 VARBINARY，TEXT 变成 BLOB。对于 ENUM 和 SET 数据类型，这种情况不会发生，它们依然按声明语句进行创建。假设这样定义表：
```
 CREATE TABLE t
 (
   c1 VARCHAR(10) CHARACTER SET binary,
   c2 TEXT CHARACTER SET binary,
   c3 ENUM('a','b','c') CHARACTER SET binary
 );
```
实际表定义变成：
```
 CREATE TABLE t
 (
   c1 VARBINARY(10),
   c2 BLOB,
   c3 ENUM('a','b','c') CHARACTER SET binary
 );
```
BINARY 属性是一种非标准的 MySQL 扩展，它是列字符集（若列未指定字符集，则取表的默认字符集）的二进制（_bin）排序规则的简写。这种情况下，比较排序是基于 numeric character code 值（译者注：应该是字符编码值？）。假设这样定义表：
```
 CREATE TABLE t
 (
   c1 VARCHAR(10) CHARACTER SET latin1 BINARY,
   c2 TEXT BINARY
 ) CHARACTER SET utf8mb4;
```
实际表定义变成：
```
 CREATE TABLE t (
   c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,
   c2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
 ) CHARACTER SET utf8mb4;
```
在 MySQL 8.0，由于 utf8mb4 字符集具有多个 _bin 排序规则，因此这种对 BINARY 属性的非标准用法是不明确的。从 MySQL 8.0.17 开始，不建议这样使用 BINARY 属性，并将在未来的 MySQL 版本中移除它。应用程序应该调整为一个明确的 _bin 排序规则。
使用 BINARY 指定数据类型或字符集的用法保持不变。
ASCII 属性是 CHARACTER SET latin1 的缩写。
UNICODE 属性是 CHARACTER SET ucs2 的缩写。

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

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

[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] 定长字符串。在存储时总是会在右侧填充空格至指定长度。M 表示列的字符长度，其取值范围为 [0, 255]。若 M 缺省，则默认为 1。
注意：检索时会移除尾随空格，除非启用 SQL 模式 PAD_CHAR_TO_FULL_LENGTH。 CHAR 是 CHARACTER 的缩写。NOTIONAL CHAR（或 NCHAR 缩写）是使用某些预定义字符集定义 CHAR 列的标准 SQL 方式。MySQL 使用 utf8 作为预定义字符集。Section 10.3.7, “The National Character Set”。 CHAR BYTE 数据类型是 BINARY 数据类型是别名。这是一项兼容性功能。 MySQL 允许创建 CHAR(0) 列。这主要用于你必须兼容“依赖该列存在但实际无用的旧应用程序”的场景。CHAR(0) 也可用于只能接受两个值的列：对于 CHAR(0) 列，NULL 只占用 1 bit 空间，它也只能接受 NULL 和 ''（空字符串）。
[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name] 可变长字符串。M 表示列的最大字符长度，其取值范围为 [0, 65535]。VARCHAR 的有效最大长度是取决于行的最大大小（65535 字节，所有列组成）和所使用的字符集。例如，字符集 utf8 的每个字符最多需要 3 字节，因此使用 utf8 字符集的 VARCHAR 列可声明的最大值为 21844 个字符。详情请看 Section 8.4.7, “Limits on Table Column Count and Row Size”。

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 的缩写。

BINARY[(M)] BINARY 类型与 CHAR 类型类似，不同的地方在于：它是存储二进制字节字符串，而不是非二进制的字符类字符串。可选的 M 表示以字节为单位的列长。若 M 缺省，则默认为 1。
VARBINARY(M) VARBINARY 类型与 VARCHAR 类型类似，不同的地方在于：它是存储二进制字节字符串，而不是非二进制的字符类字符串。M 表示以字节为单位的最大列长。
TINYBLOB BLOB 列的最大长度为 255 字节（2^8 - 1）。存储每个 TINYBLOB 值时，都会带有 1 字节长度前缀，它表示这个值的长度为多少字节。
TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name] TEXT 列的最大长度为 255 字符（2^8 - 1）。若值包含多字节字符，则有效的最大长度会变小。存储每个 TINYTEXT 值时，都会带有 1 字节长度前缀，它表示这个值的长度为多少字节。
BLOB[(M)] BLOB 列的最大长度为 65535 字节（2^16 - 1）。存储每个 BLOB 值时，都会带有 2 字节长度前缀，它表示这个值的长度为多少字节。该类型有可选的长度参数 M。若指定该参数，则 MySQL 会将列创建为最小的 BLOB 类型，但其大小足以存放 M 字节长的值。
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] TEXT 列的最大长度为 65535 字符（2^16 - 1）。若值包含多字节字符，则有效的最大长度会变小。存储每个 TEXT 值时，都会带有 2 字节长度前缀，它表示这个值的长度为多少字节。该类型有可选的长度参数 M。若指定该参数，则 MySQL 会将列创建为最小的 TEXT 类型，但其大小足以存放 M 字符长的值。
MEDIUMBLOB BLOB 列的最大长度为 16777215 字节（2^24 - 1）。存储每个 MEDIUMBLOB 值时，都会带有 3 字节长度前缀，它表示这个值的长度为多少字节。
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name] TEXT 列的最大长度为 16777215 字符（2^24 - 1）。若值包含多字节字符，则有效的最大长度会变小。存储每个 MEDIUMTEXT 值时，都会带有 3 字节长度前缀，它表示这个值的长度为多少字节。
LONGBLOB LONGBLOB 列的最大长度为 4294967295 或 4GB（2^32 - 1）字节。LONGBLOB 列的有效最大长度是取决于客户端/服务器协议中配置的最大数据包大小和可用内存。存储每个 LONGBLOB 值时，都会带有 4 字节长度前缀，它表示这个值的长度为多少字节。
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name] TEXT 列的最大长度为 4294967295 或 4GB（2^32 - 1）字符。若值包含多字节字符，则有效的最大长度会变小。LONGTEXT 列的有效最大长度也取决于客户端/服务器协议中配置的最大数据包大小和可用内存。存储每个 LONGTEXT 值时，都会带有 4 字节长度前缀，它表示这个值的长度为多少字节。
ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name] 枚举。只能拥有一个值的字符串对象，且该值可从 'value1'、'value2'、...、NULL 或特殊错误值 '' 中选择。ENUM 类型在内部以整型表示。
一个 ENUM 列最多可包含 65535 个不同元素。
单个 ENUM 元素支持（supported）的最大长度是 M <= 255 和 (M x w) <= 1020，其中 M 是元素字面量长度，w 是字符集中最长字符所需的字节数。
SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name] set。能拥有 0 个或以上值的字符串对象，其中每个值须从 'value1', 'value2', ... 中选择。SET 类型在内部以整型表示。
SET 列最多可包含 64 个不同元素。
单个 SET 元素支持（supported）的最大长度是 M <= 255 和 (M x w) <= 1020，其中 M 是元素字面量长度，w 是字符集中最长字符所需的字节数。

CHAR 和 VARCHAR 类型

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

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

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

VARCHAR 列是可变长字符串。长度的取值范围为 [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）：

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

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”不同的点如下：

BLOB 和 TEXT 的索引必须指定索引前缀长度（index prefix length）。而对 CHAR 和 VARCHAR 来说，前缀长度是可选的。详情请看 Section 8.3.5, “Column Indexes”。
BLOB 和 TEXT 不能拥有默认值（DEFAULT）。

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

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

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

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

排序时仅使用该列的前 max_sort_length 个字节。max_sort_length 的默认值为 1024。可通过在服务器启动或运行时增大 max_sort_length 的值，使得在排序或分组时有更多字节参与。任意客户端都可以更改其 session 的 max_sort_length 变量值：
```
 mysql> SET max_sort_length = 2000;
 mysql> SELECT id, comment FROM t
     -> ORDER BY comment;
```
使用临时表处理的查询结果中，若含有 BLOB 或 TEXT 实例，则会导致服务器使用磁盘上的表而不是内存中的表，因为 MEMORY 存储引擎不支持这些数据类型（详情请看 Section 8.4.4, “Internal Temporary Table Use in MySQL”）。使用磁盘会导致性能下降，因此仅在查询结果确实需要 BLOB 或 TEXT 列时才包含它们。例如，避免使用 SELECT *，它会选择所有列。
BLOB 和 TEXT 对象的最大大小由其类型决定，但实际上可以在客户端和服务器之间传输的最大值是由可用内存和通信缓冲区的大小决定。可以通过更改 max_allowed_packet 变量的值来更改通信缓冲区，且服务器和客户端程序均须设置。例如，mysql 和 mysqldump 均允许更改客户端的 max_allowed_packet 值。详情请看 Section 5.1.1, “Configuring the Server”、Section 4.5.1, “mysql — The MySQL Command-Line Client” 和 Section 4.5.4, “mysqldump — A Database Backup Program”。你可能还需要将数据包大小与要存储的数据对象（的存储要求）进行比较，详情请看 Section 11.7, “Data Type Storage Requirements”

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

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

ENUM 类型

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

有限个允许值，利于压缩数据存储空间。作为输入值的字符串会自动编码为数值。关于 ENUM 类型的存储要求，请看 Section 11.7, “Data Type Storage Requirements”。
可读的查询和输出。在查询结果中，数值会转换回为对应的字符串。

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

如果枚举值与数值相近，则很容易将字面量值和相应的内部索引值混淆，如 Enumeration Limitations 所述。
在 ORDER BY 子句中使用 ENUM 列时，要格外小心，如 Enumeration Sorting 所述。

本章目录

创建和使用 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 字节长度前缀）

枚举字面量的索引值

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

列上的元素列表均分配有索引值，从 1 开始。
空字符串（错误值）的索引值为 0。这意味着可以使用以下 SELECT 语句找出所有分配了无效 ENUM 值的行：
```
 mysql> SELECT * FROM tbl_name WHERE enum_col=0;
```
NULL 的索引值为 NULL。
术语“index”在这里是指枚举值列表的位置，与表的索引无关。

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

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 插入一个非法值（即允许值列表里不存在的字符串）时，会被替换为空字符串（作为一个特殊错误值）。该字符串与“常规”的空字符串不同，因为它拥有索引值 0。想更详细了解枚举值对应的索引值，请看 Index Values for Enumeration Literals。
若开启严格模式，则尝试插入非法枚举值会报错。
如果声明 ENUM 列允许 NULL，那么 NULL 就是该列的合法值，且默认值为 NULL。若声明 ENUM 列为 NOT NULL，则默认值为允许值列表的第一个元素。

枚举排序

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

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

以字母顺序指定 ENUM 列表。
通过 ORDER BY CAST(col AS CHAR) 或 ORDER BY CONCAT(col) 子句确保该列的排序是基于字典而不是索引值。

枚举的限制

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

例如，以下 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 成员具有以下十进制和二进制的值。

若向该列赋值为 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”。

上一页“日期和时间”数据类型下一页空间数据类型

最后更新于1年前