MySQL :: MySQL 8.4 参考手册 :: 12.9.8 3 字节和 4 字节 Unicode 字符集之间的转换

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 12 种字符集、排序规则、Unicode / 12.9 统一码支持 / 12.9.8 3 字节和 4 字节 Unicode 字符集之间的转换

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

12.9.8 在 3 字节和 4 字节 Unicode 字符集之间的转换12.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets

本节描述在 utf8mb3 和 utf8mb4 字符集之间转换字符数据时可能遇到的问题。This section describes issues that you may face when converting character data between the utf8mb3 and utf8mb4 character sets.

Note

本讨论主要集中在 utf8mb3 和 utf8mb4 之间的转换，但类似原理也适用于 ucs2 字符集与 utf16 或 utf32 等字符集之间的转换。This discussion focuses primarily on converting between utf8mb3 and utf8mb4, but similar principles apply to converting between the ucs2 character set and character sets such as utf16 or utf32.

utf8mb3 和 utf8mb4 字符集的区别如下：The utf8mb3 and utf8mb4 character sets differ as follows:

utf8mb3 仅支持基本多文种平面（BMP）中的字符。utf8mb4 还支持位于 BMP 之外的补充字符。utf8mb3 supports only characters in the Basic Multilingual Plane (BMP). utf8mb4 additionally supports supplementary characters that lie outside the BMP.
utf8mb3 每个字符最多使用三个字节。utf8mb4 每个字符最多使用四个字节。utf8mb3 uses a maximum of three bytes per character. utf8mb4 uses a maximum of four bytes per character.

Note

此讨论提到 utf8mb3 和 utf8mb4 字符集名称，是为了明确指代 3 字节和 4 字节的 UTF-8 字符集数据。This discussion refers to the utf8mb3 and utf8mb4 character set names to be explicit about referring to 3-byte and 4-byte UTF-8 character set data.

将 utf8mb3 转换为 utf8mb4 的一个优势是，这使应用程序能够使用附加字符。一个权衡是，这可能会增加数据存储空间的需求。One advantage of converting from utf8mb3 to utf8mb4 is that this enables applications to use supplementary characters. One tradeoff is that this may increase data storage space requirements.

就表内容而言，从 utf8mb3 转换为 utf8mb4 没有任何问题：In terms of table content, conversion from utf8mb3 to utf8mb4 presents no problems:

对于 BMP 字符，utf8mb4 和 utf8mb3 的存储特性相同：相同的代码值、相同的编码、相同的长度。For a BMP character, utf8mb4 and utf8mb3 have identical storage characteristics: same code values, same encoding, same length.
对于补充字符，utf8mb4 需要四个字节来存储，而 utf8mb3 根本无法存储该字符。在将 utf8mb3 列转换为 utf8mb4 时，无需担心补充字符的转换，因为根本不存在。For a supplementary character, utf8mb4 requires four bytes to store it, whereas utf8mb3 cannot store the character at all. When converting utf8mb3 columns to utf8mb4, you need not worry about converting supplementary characters because there are none.

在表结构方面，这些是主要的潜在不兼容性：In terms of table structure, these are the primary potential incompatibilities:

对于可变长度字符数据类型（VARCHAR 和 TEXT 类型），utf8mb4 列允许的最大字符长度比 utf8mb3 列少。For the variable-length character data types (VARCHAR and the TEXT types), the maximum permitted length in characters is less for utf8mb4 columns than for utf8mb3 columns.
对于所有字符数据类型（CHAR、VARCHAR 以及 TEXT 类型），utf8mb4 列可以索引的最大字符数比 utf8mb3 列少。For all character data types (CHAR, VARCHAR, and the TEXT types), the maximum number of characters that can be indexed is less for utf8mb4 columns than for utf8mb3 columns.

因此，要将表从 utf8mb3 转换为 utf8mb4，可能需要更改某些列或索引定义。Consequently, to convert tables from utf8mb3 to utf8mb4, it may be necessary to change some column or index definitions.

表可以使用 utf8mb3 从 utf8mb4 转换为 ALTER TABLE。假设一个表的定义如下：Tables can be converted from utf8mb3 to utf8mb4 by using ALTER TABLE. Suppose that a table has this definition:

CREATE TABLE t1 (
  col1 CHAR(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  col2 CHAR(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL
) CHARACTER SET utf8mb3;

下面的语句将 t1 转换为使用 utf8mb4：The following statement converts t1 to use utf8mb4:

ALTER TABLE t1
  DEFAULT CHARACTER SET utf8mb4,
  MODIFY col1 CHAR(10)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  MODIFY col2 CHAR(10)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

将 utf8mb3 转换为 utf8mb4 时的难点在于，列或索引键的最大长度在字节方面没有变化。因此，从字符的角度来看，它实际上更小，因为一个字符的最大长度是四个字节，而不是三个。对于 CHAR、VARCHAR 和 TEXT 数据类型，在转换 MySQL 表时请注意这些问题：The catch when converting from utf8mb3 to utf8mb4 is that the maximum length of a column or index key is unchanged in terms of bytes. Therefore, it is smaller in terms of characters because the maximum length of a character is four bytes instead of three. For the CHAR, VARCHAR, and TEXT data types, watch for these issues when converting your MySQL tables:

检查所有 utf8mb3 列的定义，并确保它们不超过存储引擎的最大长度。Check all definitions of utf8mb3 columns and make sure they do not exceed the maximum length for the storage engine.
检查所有 utf8mb3 列的索引，并确保它们不超过存储引擎的最大长度。有时由于存储引擎的增强，最大值可能会发生变化。Check all indexes on utf8mb3 columns and make sure they do not exceed the maximum length for the storage engine. Sometimes the maximum can change due to storage engine enhancements.

如果前述条件适用，您必须要么减少列或索引的定义长度，要么继续使用 utf8mb3 而不是 utf8mb4。If the preceding conditions apply, you must either reduce the defined length of columns or indexes, or continue to use utf8mb3 rather than utf8mb4.

以下是一些可能需要结构性变动的例子：Here are some examples where structural changes may be needed:

TINYTEXT 列最多可以存储 255 字节，因此它最多可以存储 85 个 3 字节字符或 63 个 4 字节字符。假设你有一个使用 utf8mb3 的 TINYTEXT 列，但必须能够包含超过 63 个字符。如果不将数据类型更改为更长的类型，例如 TEXT，则不能将其转换为 utf8mb4。A TINYTEXT column can hold up to 255 bytes, so it can hold up to 85 3-byte or 63 4-byte characters. Suppose that you have a TINYTEXT column that uses utf8mb3 but must be able to contain more than 63 characters. You cannot convert it to utf8mb4 unless you also change the data type to a longer type such as TEXT.
同样，如果你想将一个非常长的VARCHAR列从utf8mb3转换为utf8mb4，可能需要将其更改为更长的TEXT类型之一。Similarly, a very long VARCHAR column may need to be changed to one of the longer TEXT types if you want to convert it from utf8mb3 to utf8mb4.
InnoDB 对使用 COMPACT 或 REDUNDANT 行格式的表的最大索引长度为 767 字节，因此对于 utf8mb3 或 utf8mb4 列，您最多可以索引 255 或 191 个字符。如果您当前有 utf8mb3 列的索引长度超过 191 个字符，则必须索引更少的字符数。InnoDB has a maximum index length of 767 bytes for tables that use COMPACT or REDUNDANT row format, so for utf8mb3 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8mb3 columns with indexes longer than 191 characters, you must index a smaller number of characters.
在使用 COMPACT 或 REDUNDANT 行格式的 InnoDB 表中，这些列和索引定义是合法的：In an InnoDB table that uses COMPACT or REDUNDANT row format, these column and index definitions are legal:
```
col1 VARCHAR(500) CHARACTER SET utf8mb3, INDEX (col1(255))
```
要改用 utf8mb4，索引必须更小：To use utf8mb4 instead, the index must be smaller:
```
col1 VARCHAR(500) CHARACTER SET utf8mb4, INDEX (col1(191))
```
Note
对于使用 COMPRESSED 或 DYNAMIC 行格式的 InnoDB 表，允许使用超过 767 字节（最多 3072 字节）的索引键前缀。使用这些行格式创建的表允许你分别对 utf8mb3 或 utf8mb4 列索引最多 1024 或 768 个字符。有关相关信息，请参见第 17.21 节，“InnoDB 限制”，以及 DYNAMIC 行格式。For InnoDB tables that use COMPRESSED or DYNAMIC row format, index key prefixes longer than 767 bytes (up to 3072 bytes) are permitted. Tables created with these row formats enable you to index a maximum of 1024 or 768 characters for utf8mb3 or utf8mb4 columns, respectively. For related information, see Section 17.21, “InnoDB Limits”, and DYNAMIC Row Format.

之前提到的这些类型的更改很可能只在您有非常长的列或索引时才需要。否则，您应该能够使用前面所述的 ALTER TABLE，将您的表从 utf8mb3 转换为 utf8mb4，而不会遇到问题。The preceding types of changes are most likely to be required only if you have very long columns or indexes. Otherwise, you should be able to convert your tables from utf8mb3 to utf8mb4 without problems, using ALTER TABLE as described previously.

以下项目总结了其他潜在的不兼容情况：The following items summarize other potential incompatibilities:

SET NAMES 'utf8mb4' 会导致连接字符集使用 4 字节字符集。只要服务器没有发送 4 字节字符，就不应该有问题。否则，那些期望每个字符最多接收三字节的应用程序可能会出现问题。相反，那些期望发送 4 字节字符的应用程序必须确保服务器能理解它们。SET NAMES 'utf8mb4' causes use of the 4-byte character set for connection character sets. As long as no 4-byte characters are sent from the server, there should be no problems. Otherwise, applications that expect to receive a maximum of three bytes per character may have problems. Conversely, applications that expect to send 4-byte characters must ensure that the server understands them.
对于复制，如果源端使用支持补充字符的字符集，则所有副本也必须能够识别这些字符。For replication, if character sets that support supplementary characters are to be used on the source, all replicas must understand them as well.
此外，请记住一个通用原则：如果表在源端和副本端的定义不同，这可能会导致意外结果。例如，最大索引键长度的差异使得在源端使用utf8mb3而在副本端使用utf8mb4具有风险。Also, keep in mind the general principle that if a table has different definitions on the source and replica, this can lead to unexpected results. For example, the differences in maximum index key length make it risky to use utf8mb3 on the source and utf8mb4 on the replica.

如果您已将编码转换为utf8mb4、utf16、utf16le或utf32，然后决定转换回utf8mb3或ucs2（例如，为了降级到较旧版本的 MySQL），以下考虑仍适用：If you have converted to utf8mb4, utf16, utf16le, or utf32, and then decide to convert back to utf8mb3 or ucs2 (for example, to downgrade to an older version of MySQL), these considerations apply:

utf8mb3和ucs2的数据应该不会有问题。utf8mb3 and ucs2 data should present no problems.
服务器必须足够新，才能识别引用您正在转换的字符集的定义。The server must be recent enough to recognize definitions referring to the character set from which you are converting.
对于引用 utf8mb4 字符集的对象定义，您可以在降级之前使用 mysqldump 导出它们，编辑转储文件，将所有 utf8mb4 实例改为 utf8，然后在旧版本服务器中重新加载文件，只要数据中没有 4 字节字符即可。旧服务器在转储文件对象定义中看到 utf8，并创建使用（3 字节）utf8 字符集的新对象。For object definitions that refer to the utf8mb4 character set, you can dump them with mysqldump prior to downgrading, edit the dump file to change instances of utf8mb4 to utf8, and reload the file in the older server, as long as there are no 4-byte characters in the data. The older server sees utf8 in the dump file object definitions and create new objects that use the (3-byte) utf8 character set.

首页上一级下一节