8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

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

打开官方英文原文 ↗

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

MySQL 8.4 参考手册 / A MySQL 8.4 Frequently Asked Questions / A.11 MySQL 8.4 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 54%

A.11 MySQL 8.4 常见问题:MySQL 中的中文、日文和韩文字集A.11 MySQL 8.4 FAQ: MySQL Chinese, Japanese, and Korean Character Sets

这组常见问题来源于 MySQL 支持和开发团队在处理大量有关 CJK(中文-日文-韩文)问题咨询时的经验。This set of Frequently Asked Questions derives from the experience of MySQL's Support and Development groups in handling many inquiries about CJK (Chinese-Japanese-Korean) issues.

A.11.1. MySQL 中有哪些 CJK 字符集可用? A.11.1. What CJK character sets are available in MySQL?
A.11.2. 我已将 CJK 字符插入到表中。为什么 SELECT 显示为“?”字符? A.11.2. I have inserted CJK characters into my table. Why does SELECT display them as “?” characters?
A.11.3. 使用 Big5 中文字符集时应注意哪些问题? A.11.3. What problems should I be aware of when working with the Big5 Chinese character set?
A.11.4. 为什么日文字符集转换会失败? A.11.4. Why do Japanese character set conversions fail?
A.11.5. 如果我想将 SJIS 81CA 转换为 cp932,我应该怎么做? A.11.5. What should I do if I want to convert SJIS 81CA to cp932?
A.11.6. MySQL 如何表示日元 (¥) 符号? A.11.6. How does MySQL represent the Yen (¥) sign?
A.11.7. 在 MySQL 中使用韩文字集时,我应该注意哪些问题? A.11.7. Of what issues should I be aware when working with Korean character sets in MySQL?
A.11.8. 为什么我会收到“字符串值不正确”错误信息? A.11.8. Why do I get Incorrect string value error messages?
A.11.9. 为什么我的 GUI 前端或浏览器在使用 Access、PHP 或其他 API 的应用程序中显示 CJK 字符不正确? A.11.9. Why does my GUI front end or browser display CJK characters incorrectly in my application using Access, PHP, or another API?
A.11.10. 我已升级到 MySQL 8.4。如何恢复到类似 MySQL 4.0 的字符集行为? A.11.10. I've upgraded to MySQL 8.4. How can I revert to behavior like that in MySQL 4.0 with regard to character sets?
A.11.11. 为什么某些包含 CJK 字符的 LIKE 和 FULLTEXT 搜索会失败? A.11.11. Why do some LIKE and FULLTEXT searches with CJK characters fail?
A.11.12. 我如何知道字符 X 是否在所有字符集都可用? A.11.12. How do I know whether character X is available in all character sets?
A.11.13. 为什么 CJK 字符串在 Unicode 中排序不正确?(I) A.11.13. Why do CJK strings sort incorrectly in Unicode? (I)
A.11.14. 为什么 CJK 字符串在 Unicode 中排序不正确?(II) A.11.14. Why do CJK strings sort incorrectly in Unicode? (II)
A.11.15. 为什么我的补充字符会被 MySQL 拒绝? A.11.15. Why are my supplementary characters rejected by MySQL?
A.11.16. “CJK”应该是“CJKV”吗? A.11.16. Should “CJK” be “CJKV”?
A.11.17. MySQL 允许在数据库和表名中使用 CJK 字符吗? A.11.17. Does MySQL permit CJK characters to be used in database and table names?
A.11.18. 我可以在哪里找到 MySQL 手册的中文、日文和韩文翻译? A.11.18. Where can I find translations of the MySQL Manual into Chinese, Japanese, and Korean?
A.11.19. 我可以在哪里获得关于 MySQL 中 CJK 及相关问题的帮助? A.11.19. Where can I get help with CJK and related issues in MySQL?

A.11.1.

MySQL 提供哪些 CJK 字符集?What CJK character sets are available in MySQL?

The list of CJK character sets may vary depending on your MySQL version. For example, the gb18030 character set is not supported prior to MySQL 5.7.4. However, since the name of the applicable language appears in the DESCRIPTION column for every entry in the INFORMATION_SCHEMA.CHARACTER_SETS table, you can obtain a current list of all the non-Unicode CJK character sets using this query:

mysql> SELECT CHARACTER_SET_NAME, DESCRIPTION
       FROM INFORMATION_SCHEMA.CHARACTER_SETS
       WHERE DESCRIPTION LIKE '%Chin%'
       OR DESCRIPTION LIKE '%Japanese%'
       OR DESCRIPTION LIKE '%Korean%'
       ORDER BY CHARACTER_SET_NAME;
+--------------------+---------------------------------+
| CHARACTER_SET_NAME | DESCRIPTION                     |
+--------------------+---------------------------------+
| big5               | Big5 Traditional Chinese        |
| cp932              | SJIS for Windows Japanese       |
| eucjpms            | UJIS for Windows Japanese       |
| euckr              | EUC-KR Korean                   |
| gb18030            | China National Standard GB18030 |
| gb2312             | GB2312 Simplified Chinese       |
| gbk                | GBK Simplified Chinese          |
| sjis               | Shift-JIS Japanese              |
| ujis               | EUC-JP Japanese                 |
+--------------------+---------------------------------+

(有关更多信息,请参见 第 28.3.4 节,“INFORMATION_SCHEMA CHARACTER_SETS 表”。)(For more information, see Section 28.3.4, “The INFORMATION_SCHEMA CHARACTER_SETS Table”.)

MySQL 支持三种 GB (国家标准,或 国家标准,或 简体中文) 字符集变体,这些字符集在中华人民共和国是官方标准:gb2312、gbk 和 (从 MySQL 5.7.4 起) gb18030。MySQL supports three variants of the GB (Guojia Biaozhun, or National Standard, or Simplified Chinese) character sets which are official in the People's Republic of China: gb2312, gbk, and (as of MySQL 5.7.4) gb18030.

有时人们尝试将 gbk 字符插入 gb2312,大多数情况下是可行的,因为 gbk 是 gb2312 的超集。但最终他们尝试插入较为罕见的汉字时,就不行了。(举例见 Bug #16072)。Sometimes people try to insert gbk characters into gb2312, and it works most of the time because gbk is a superset of gb2312. But eventually they try to insert a rarer Chinese character and it does not work. (For an example, see Bug #16072).

在这里,我们尝试明确哪些字符在 gb2312 或 gbk 中是合法的,并参考官方文档。在报告 gb2312 或 gbk 的错误之前,请先检查这些参考资料:Here, we try to clarify exactly what characters are legitimate in gb2312 or gbk, with reference to the official documents. Please check these references before reporting gb2312 or gbk bugs:

也可以在 Unicode 字符集中存储 CJK 字符,尽管可用的排序规则可能不会按你预期的方式对字符排序:It is also possible to store CJK characters in Unicode character sets, although the available collations may not sort characters quite as you expect:

  • utf8 和 ucs2 字符集支持 Unicode 基本多文种平面(BMP)中的字符。这些字符的代码点值介于 U+0000 和 U+FFFF 之间。The utf8 and ucs2 character sets support the characters from Unicode Basic Multilingual Plane (BMP). These characters have code point values between U+0000 and U+FFFF.

  • utf8mb4、utf16、utf16le 和 utf32 字符集支持 BMP 字符,以及位于 BMP 之外的补充字符。补充字符的代码点值在 U+10000 到 U+10FFFF 之间。The utf8mb4, utf16, utf16le, and utf32 character sets support BMP characters, as well as supplementary characters that lie outside the BMP. Supplementary characters have code point values between U+10000 and U+10FFFF.

用于 Unicode 字符集的排序规则决定了在集合中排序(即区分)字符的能力:The collation used for a Unicode character set determines the ability to sort (that is, distinguish) characters in the set:

  • 基于 Unicode 排序算法 (UCA) 4.0.0 的排序规则只区分 BMP 字符。Collations based on Unicode Collation Algorithm (UCA) 4.0.0 distinguish only BMP characters.

  • 基于 UCA 5.2.0 或 9.0.0 的排序规则可以区分 BMP 和补充字符。Collations based on UCA 5.2.0 or 9.0.0 distinguish BMP and supplementary characters.

  • 非 UCA 排序规则可能无法区分所有 Unicode 字符。例如,utf8mb4 的默认排序规则是 utf8mb4_general_ci,它只区分 BMP 字符。Non-UCA collations may not distinguish all Unicode characters. For example, the utf8mb4 default collation is utf8mb4_general_ci, which distinguishes only BMP characters.

此外,区分字符并不等同于按特定 CJK 语言的规则对它们进行排序。目前,MySQL 只有一种针对 CJK 的 UCA 排序规则,gb18030_unicode_520_ci(这需要使用非 Unicode 的gb18030字符集)。Moreover, distinguishing characters is not the same as ordering them per the conventions of a given CJK language. Currently, MySQL has only one CJK-specific UCA collation, gb18030_unicode_520_ci (which requires use of the non-Unicode gb18030 character set).

有关 Unicode 排序规则及其区分属性的信息,包括附加字符的排序属性,请参阅 第 12.10.1 节,“Unicode 字符集”。For information about Unicode collations and their differentiating properties, including collation properties for supplementary characters, see Section 12.10.1, “Unicode Character Sets”.

A.11.2.

我已经在表中插入了 CJK 字符。为什么SELECT 会将它们显示为“?”字符?I have inserted CJK characters into my table. Why does SELECT display them as “?” characters?

这个问题通常是由于 MySQL 的某个设置与应用程序或操作系统的设置不匹配造成的。以下是一些解决此类问题的常见步骤:This problem is usually due to a setting in MySQL that does not match the settings for the application program or the operating system. Here are some common steps for correcting these types of issues:

  • 确定你使用的 MySQL 版本。Be certain of what MySQL version you are using.

    使用语句 SELECT VERSION(); 来确定。Use the statement SELECT VERSION(); to determine this.

  • 确保数据库实际上正在使用所需的字符集。Make sure that the database is actually using the desired character set.

    人们常常认为客户端字符集总是与服务器字符集或用于显示的字符集相同。然而,这两个假设都是错误的。你可以通过检查 SHOW CREATE TABLE tablename 的结果来确认,或者更好的是,使用以下语句:People often think that the client character set is always the same as either the server character set or the character set used for display purposes. However, both of these are false assumptions. You can make sure by checking the result of SHOW CREATE TABLE tablename or, better yet, by using this statement:

    SELECT character_set_name, collation_name
        FROM information_schema.columns
        WHERE table_schema = your_database_name
            AND table_name = your_table_name
            AND column_name = your_column_name;
    
  • 确定未正确显示的字符或字符的十六进制值。Determine the hexadecimal value of the character or characters that are not being displayed correctly.

    您可以使用以下查询获取表 table_name 中列 column_name 的该信息:You can obtain this information for a column column_name in the table table_name using the following query:

    SELECT HEX(column_name)
    FROM table_name;
    

    3F 是 ? 字符的编码;这意味着列中实际存储的字符是 ?。这种情况最常发生,因为在将特定字符从客户端字符集转换为目标字符集时出现了问题。3F is the encoding for the ? character; this means that ? is the character actually stored in the column. This most often happens because of a problem converting a particular character from your client character set to the target character set.

  • 确保往返转换是可能的。当你选择 literal(或 _introducer hexadecimal-value)时,是否得到 literal 作为结果?Make sure that a round trip is possible. When you select literal (or _introducer hexadecimal-value), do you obtain literal as a result?

    例如,日语片假名字符 Pe (ペ') 存在于所有 CJK 字符集中,并且其代码点值(十六进制编码)为 0x30da。要测试该字符的往返转换,请使用以下查询:For example, the Japanese Katakana character Pe (ペ') exists in all CJK character sets, and has the code point value (hexadecimal coding) 0x30da. To test a round trip for this character, use this query:

    SELECT 'ペ' AS `ペ`;         /* or SELECT _ucs2 0x30da; */
    

    如果结果也不是 ペ,则往返转换失败。If the result is not also ペ, the round trip failed.

    对于此类失败的错误报告,我们可能会要求你随后执行 SELECT HEX('ペ');。然后我们可以确定客户端编码是否正确。For bug reports regarding such failures, we might ask you to follow up with SELECT HEX('ペ');. Then we can determine whether the client encoding is correct.

  • 确保问题不是出在浏览器或其他应用程序,而是出在 MySQL 上。Make sure that the problem is not with the browser or other application, rather than with MySQL.

    使用 mysql 客户端程序来完成此任务。如果 mysql 正确显示字符,但你的应用程序没有,那么问题很可能出在系统设置上。Use the mysql client program to accomplish this task. If mysql displays characters correctly but your application does not, your problem is probably due to system settings.

    要确定你的设置,请使用 SHOW VARIABLES 语句,其输出应类似于这里所示:To determine your settings, use the SHOW VARIABLES statement, whose output should resemble what is shown here:

    mysql> SHOW VARIABLES LIKE 'char%';
    +--------------------------+----------------------------------------+
    | Variable_name            | Value                                  |
    +--------------------------+----------------------------------------+
    | character_set_client     | utf8                                   |
    | character_set_connection | utf8                                   |
    | character_set_database   | latin1                                 |
    | character_set_filesystem | binary                                 |
    | character_set_results    | utf8                                   |
    | character_set_server     | latin1                                 |
    | character_set_system     | utf8                                   |
    | character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
    +--------------------------+----------------------------------------+
    

    这些是面向国际客户的典型字符集设置(注意使用了utf8 Unicode),该客户端连接到西方的服务器(latin1是西欧字符集)。These are typical character-set settings for an international-oriented client (notice the use of utf8 Unicode) connected to a server in the West (latin1 is a West Europe character set).

    虽然 Unicode(在 Unix 上通常是utf8变体,在 Windows 上是ucs2变体)优于 Latin,但它往往不是您的操作系统工具支持得最好的。许多 Windows 用户发现,微软的字符集,例如适用于日本 Windows 的cp932,是合适的。Although Unicode (usually the utf8 variant on Unix, and the ucs2 variant on Windows) is preferable to Latin, it is often not what your operating system utilities support best. Many Windows users find that a Microsoft character set, such as cp932 for Japanese Windows, is suitable.

    如果您无法控制服务器设置,并且不知道底层计算机使用的设置,请尝试更改为您所在国家的常见字符集(euckr = 韩国;gb18030、gb2312 或 gbk = 中国大陆;big5 = 台湾;sjis、ujis、cp932 或 eucjpms = 日本;ucs2 或 utf8 = 任意地方)。通常只需要更改客户端、连接和结果设置即可。SET NAMES 语句会一次性更改这三者。例如:If you cannot control the server settings, and you have no idea what setting your underlying computer uses, try changing to a common character set for the country that you're in (euckr = Korea; gb18030, gb2312 or gbk = People's Republic of China; big5 = Taiwan; sjis, ujis, cp932, or eucjpms = Japan; ucs2 or utf8 = anywhere). Usually it is necessary to change only the client and connection and results settings. The SET NAMES. statement changes all three at once. For example:

    SET NAMES 'big5';
    

    一旦设置正确,您可以通过编辑 my.cnf 或 my.ini 来使其永久生效。例如,您可能会添加如下几行:Once the setting is correct, you can make it permanent by editing my.cnf or my.ini. For example you might add lines looking like these:

    [mysqld]
    character-set-server=big5
    [client]
    default-character-set=big5
    

    也有可能是您的应用程序中使用的 API 配置设置存在问题;有关更多信息,请参阅 为什么我的 GUI 前端或浏览器不能正确显示 CJK 字符...?It is also possible that there are issues with the API configuration setting being used in your application; see Why does my GUI front end or browser not display CJK characters correctly...? for more information.

A.11.3.

在使用 Big5 中文字符集时,我需要注意哪些问题?What problems should I be aware of when working with the Big5 Chinese character set?

MySQL 支持在香港和台湾(中华民国)常用的 Big5 字符集。MySQL 的 big5 字符集实际上是微软的代码页 950,它与原始的 big5 字符集非常相似。MySQL supports the Big5 character set which is common in Hong Kong and Taiwan (Republic of China). The MySQL big5 character set is in reality Microsoft code page 950, which is very similar to the original big5 character set.

已经提交了一个关于添加HKSCS扩展的功能请求。需要此扩展的人可能会对针对 Bug #13577 的建议补丁感兴趣。A feature request for adding HKSCS extensions has been filed. People who need this extension may find the suggested patch for Bug #13577 to be of interest.

A.11.4.

为什么日文字符集转换会失败?Why do Japanese character set conversions fail?

MySQL 支持sjis、ujis、cp932和eucjpms字符集,以及 Unicode。一个常见的需求是进行字符集之间的转换。例如,可能有一个 Unix 服务器(通常使用sjis或ujis)和一个 Windows 客户端(通常使用cp932)。MySQL supports the sjis, ujis, cp932, and eucjpms character sets, as well as Unicode. A common need is to convert between character sets. For example, there might be a Unix server (typically with sjis or ujis) and a Windows client (typically with cp932).

在下面的转换表中,ucs2 列表示源,而 sjis、cp932、ujis 和 eucjpms 列表示目标;也就是说,当我们使用 CONVERT(ucs2) 或将包含该值的 ucs2 列分配给 sjis、cp932、ujis 或 eucjpms 列时,最后四列提供的是十六进制结果。In the following conversion table, the ucs2 column represents the source, and the sjis, cp932, ujis, and eucjpms columns represent the destinations; that is, the last 4 columns provide the hexadecimal result when we use CONVERT(ucs2) or we assign a ucs2 column containing the value to an sjis, cp932, ujis, or eucjpms column.

字符名称Character Name ucs2 sjis cp932 ujis eucjpms
断条BROKEN BAR 00A6 3F 3F 8FA2C3 3F
全角断条FULLWIDTH BROKEN BAR FFE4 3F FA55 3F 8FA2
日元符号YEN SIGN 00A5 3F 3F 20 3F
全角日元符号FULLWIDTH YEN SIGN FFE5 818F 818F A1EF 3F
波浪线TILDE 007E 7E 7E 7E 7E
上划线OVERLINE 203E 3F 3F 20 3F
水平线HORIZONTAL BAR 2015 815C 815C A1BD A1BD
破折号EM DASH 2014 3F 3F 3F 3F
反斜杠REVERSE SOLIDUS 005C 815F 5C 5C 5C
全角反斜杠FULLWIDTH REVERSE SOLIDUS FF3C 3F 815F 3F A1C0
波浪号WAVE DASH 301C 8160 3F A1C1 3F
全角波浪号FULLWIDTH TILDE FF5E 3F 8160 3F A1C1
双竖线DOUBLE VERTICAL LINE 2016 8161 3F A1C2 3F
平行于PARALLEL TO 2225 3F 8161 3F A1C2
减号MINUS SIGN 2212 817C 3F A1DD 3F
全角连字符号FULLWIDTH HYPHEN-MINUS FF0D 3F 817C 3F A1DD
分币符号CENT SIGN 00A2 8191 3F A1F1 3F
全角分币符号FULLWIDTH CENT SIGN FFE0 3F 8191 3F A1F1
英镑符号POUND SIGN 00A3 8192 3F A1F2 3F
全角英镑符号FULLWIDTH POUND SIGN FFE1 3F 8192 3F A1F2
非符号NOT SIGN 00AC 81CA 3F A2CC 3F
全角非符号FULLWIDTH NOT SIGN FFE2 3F 81CA 3F A2CC

现在考虑表格的以下部分。Now consider the following portion of the table.

ucs2 sjis cp932
非符号NOT SIGN 00AC 81CA 3F
全角非符号FULLWIDTH NOT SIGN FFE2 3F 81CA

这意味着 MySQL 将 NOT SIGN (Unicode U+00AC) 转换为 sjis 代码点 0x81CA,并转换为 cp932 代码点 3F。(3F 是问号(“?”)。当无法执行转换时,总是使用它。)This means that MySQL converts the NOT SIGN (Unicode U+00AC) to sjis code point 0x81CA and to cp932 code point 3F. (3F is the question mark (“?”. This is what is always used when the conversion cannot be performed.)

A.11.5.

如果我想将 SJIS 81CA 转换为 cp932,我该怎么办?What should I do if I want to convert SJIS 81CA to cp932?

我们的答案是:“?”。这有一些缺点,许多人更倾向于“宽松”的转换,因此在sjis中的81CA (NOT SIGN)会变成cp932中的81CA (FULLWIDTH NOT SIGN)。Our answer is: “?”. There are disadvantages to this, and many people would prefer a “loose” conversion, so that 81CA (NOT SIGN) in sjis becomes 81CA (FULLWIDTH NOT SIGN) in cp932.

A.11.6.

MySQL 如何表示日元符号(¥)?How does MySQL represent the Yen (¥) sign?

A problem arises because some versions of Japanese character sets (both sjis以及euc) treat 5C as a reverse solidus (\, also known as a backslash), whereas others treat it as a yen sign (¥).A problem arises because some versions of Japanese character sets (both sjis and euc) treat 5C as a reverse solidus (\, also known as a backslash), whereas others treat it as a yen sign (¥).

MySQL 只遵循 JIS(日本工业标准)标准描述的一个版本。在 MySQL 中,5C 永远是反斜杠 (\)。MySQL follows only one version of the JIS (Japanese Industrial Standards) standard description. In MySQL, 5C is always the reverse solidus (\).

A.11.7.

在 MySQL 中使用韩文字符集时,我应该注意哪些问题?Of what issues should I be aware when working with Korean character sets in MySQL?

理论上,虽然有几个版本的 euckr (扩展 Unix 韩国码) 字符集,但只发现了一个问题。我们使用 EUC-KR 的 “ASCII” 变体,其中码点 0x5c 是反斜杠,即 \,而不是 EUC-KR 的 “KS-Roman” 变体,其中码点 0x5c 是 WON SIGN (₩)。这意味着你无法将 Unicode U+20A9 转换为 euckr:In theory, while there have been several versions of the euckr (Extended Unix Code Korea) character set, only one problem has been noted. We use the “ASCII” variant of EUC-KR, in which the code point 0x5c is REVERSE SOLIDUS, that is \, instead of the “KS-Roman” variant of EUC-KR, in which the code point 0x5c is WON SIGN (₩). This means that you cannot convert Unicode U+20A9 to euckr:

mysql> SELECT
           CONVERT('₩' USING euckr) AS euckr,
           HEX(CONVERT('₩' USING euckr)) AS hexeuckr;
+-------+----------+
| euckr | hexeuckr |
+-------+----------+
| ?     | 3F       |
+-------+----------+

A.11.8.

为什么我会收到 字符串值不正确 的错误消息?Why do I get Incorrect string value error messages?

要查看问题,请创建一个包含一列 Unicode (ucs2) 和一列中文 (gb2312) 的表。To see the problem, create a table with one Unicode (ucs2) column and one Chinese (gb2312) column.

mysql> CREATE TABLE ch
       (ucs2 CHAR(3) CHARACTER SET ucs2,
       gb2312 CHAR(3) CHARACTER SET gb2312);

在非严格 SQL 模式下,尝试将罕见字符 汌 放入两列中。In nonstrict SQL mode, try to place the rare character 汌 in both columns.

mysql> SET sql_mode = '';
mysql> INSERT INTO ch VALUES ('A汌B','A汌B');
Query OK, 1 row affected, 1 warning (0.00 sec)

INSERT 会产生警告。使用以下语句查看警告内容:The INSERT produces a warning. Use the following statement to see what it is:

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect string value: '\xE6\xB1\x8CB' for column 'gb2312' at row 1

因此,这是关于 gb2312 列的警告。So it is a warning about the gb2312 column only.

mysql> SELECT ucs2,HEX(ucs2),gb2312,HEX(gb2312) FROM ch;
+-------+--------------+--------+-------------+
| ucs2  | HEX(ucs2)    | gb2312 | HEX(gb2312) |
+-------+--------------+--------+-------------+
| A汌B | 00416C4C0042 | A?B    | 413F42      |
+-------+--------------+--------+-------------+

这里有几件事情需要说明:Several things need explanation here:

  1. 如前所述,汌 字符不在 gb2312 字符集中。The 汌 character is not in the gb2312 character set, as described earlier.

  2. 如果您使用的是旧版本的 MySQL,可能会看到不同的消息。If you are using an old version of MySQL, you may see a different message.

  3. 出现警告而不是错误,是因为 MySQL 没有设置为使用严格 SQL 模式。在非严格模式下,MySQL 会尽力而为,尝试找到最佳匹配,而不是放弃。如果使用严格 SQL 模式,Incorrect string value 消息会作为错误而不是警告出现,并且 INSERT 操作会失败。A warning occurs rather than an error because MySQL is not set to use strict SQL mode. In nonstrict mode, MySQL tries to do what it can, to get the best fit, rather than give up. With strict SQL mode, the Incorrect string value message occurs as an error rather than a warning, and the INSERT fails.

A.11.9.

为什么我的 GUI 前端或浏览器在使用 Access、PHP 或其他 API 的应用程序中会错误地显示 CJK 字符?Why does my GUI front end or browser display CJK characters incorrectly in my application using Access, PHP, or another API?

使用 mysql 客户端直接连接到服务器,并在那里尝试同样的查询。如果 mysql 正常响应,问题可能是您的应用接口需要初始化。使用 mysql 通过语句 SHOW VARIABLES LIKE 'char%'; 告诉您它使用的字符集。如果您使用的是 Access,您很可能是通过 Connector/ODBC 连接。在这种情况下,您应检查 配置 Connector/ODBC。例如,如果您使用 big5,您需要输入 SET NAMES 'big5'。(在这种情况下,不需要 ; 字符。)如果您使用 ASP,您可能需要在代码中添加 SET NAMES。这里有一个过去有效的示例:Obtain a direct connection to the server using the mysql client, and try the same query there. If mysql responds correctly, the trouble may be that your application interface requires initialization. Use mysql to tell you what character set or sets it uses with the statement SHOW VARIABLES LIKE 'char%';. If you are using Access, you are most likely connecting with Connector/ODBC. In this case, you should check Configuring Connector/ODBC. If, for example, you use big5, you would enter SET NAMES 'big5'. (In this case, no ; character is required.) If you are using ASP, you might need to add SET NAMES in the code. Here is an example that has worked in the past:

<%
Session.CodePage=0
Dim strConnection
Dim Conn
strConnection="driver={MySQL ODBC 3.51 Driver};server=server;uid=username;" \
               & "pwd=password;database=database;stmt=SET NAMES 'big5';"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnection
%>

同样地,如果您在使用 Connector/NET 时使用的是除 latin1 之外的任何字符集,则必须在连接字符串中指定字符集。更多信息请参见 Connector/NET 连接。In much the same way, if you are using any character set other than latin1 with Connector/NET, you must specify the character set in the connection string. See Connector/NET Connections, for more information.

如果您使用的是 PHP,请尝试如下:If you are using PHP, try this:

<?php
  $link = new mysqli($host, $usr, $pwd, $db);

  if( mysqli_connect_errno() )
  {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
  }

  $link->query("SET NAMES 'utf8'");
?>

在这种情况下,我们使用 SET NAMES 来更改 character_set_client、character_set_connection 和 character_set_results。In this case, we used SET NAMES to change character_set_client, character_set_connection, and character_set_results.

在 PHP 应用程序中经常遇到的另一个问题与浏览器做出的假设有关。有时只需添加或更改一个 <meta> 标签就可以纠正问题:例如,为了确保用户代理将页面内容解释为 UTF-8,可以在 HTML 页面的 <head> 部分包含 <meta http-equiv="Content-Type" content="text/html; charset=utf-8">。Another issue often encountered in PHP applications has to do with assumptions made by the browser. Sometimes adding or changing a <meta> tag suffices to correct the problem: for example, to insure that the user agent interprets page content as UTF-8, include <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> in the <head> section of the HTML page.

如果您使用的是 Connector/J,请参见 使用字符集和 Unicode。If you are using Connector/J, see Using Character Sets and Unicode.

A.11.10.

我已经升级到 MySQL 8.4。关于字符集,我如何恢复到类似 MySQL 4.0 的行为?I've upgraded to MySQL 8.4. How can I revert to behavior like that in MySQL 4.0 with regard to character sets?

在 MySQL 4.0 版本中,服务器和客户端只使用一个“全局”字符集,使用哪个字符集由服务器管理员决定。从 MySQL 4.1 版本开始,这种情况发生了变化。现在发生的是一个“握手”,如第 12.4 节,“连接字符集与校对规则”中所描述:In MySQL Version 4.0, there was a single “global” character set for both server and client, and the decision as to which character to use was made by the server administrator. This changed starting with MySQL Version 4.1. What happens now is a “handshake”, as described in Section 12.4, “Connection Character Sets and Collations”:

当客户端连接时,它会向服务器发送它想要使用的字符集的名称。服务器使用该名称来设置character_set_client、character_set_results和character_set_connection系统变量。实际上,服务器使用该字符集名称执行一次SET NAMES操作。When a client connects, it sends to the server the name of the character set that it wants to use. The server uses the name to set the character_set_client, character_set_results, and character_set_connection system variables. In effect, the server performs a SET NAMES operation using the character set name.

其效果是,你无法通过使用 mysqld 并带上 --character-set-server=utf8 来控制客户端字符集。然而,一些亚洲客户更喜欢 MySQL 4.0 的行为。为了保留这种行为,我们添加了一个 mysqld 选项,--character-set-client-handshake,可以使用 --skip-character-set-client-handshake 来关闭。如果你使用 mysqld 并带上 --skip-character-set-client-handshake 启动,那么,当客户端连接时,它会向服务器发送其希望使用的字符集名称。然而,服务器会忽略来自客户端的此请求。The effect of this is that you cannot control the client character set by starting mysqld with --character-set-server=utf8. However, some Asian customers prefer the MySQL 4.0 behavior. To make it possible to retain this behavior, we added a mysqld switch, --character-set-client-handshake, which can be turned off with --skip-character-set-client-handshake. If you start mysqld with --skip-character-set-client-handshake, then, when a client connects, it sends to the server the name of the character set that it wants to use. However, the server ignores this request from the client.

举个例子,假设你最喜欢的服务器字符集是 latin1。进一步假设客户端使用 utf8,因为这是客户端操作系统支持的。使用 latin1 作为默认字符集启动服务器:By way of example, suppose that your favorite server character set is latin1. Suppose further that the client uses utf8 because this is what the client's operating system supports. Start the server with latin1 as its default character set:

mysqld --character-set-server=latin1

然后使用默认字符集 utf8 启动客户端:And then start the client with the default character set utf8:

mysql --default-character-set=utf8

可以通过查看 SHOW VARIABLES 的输出看到最终设置:The resulting settings can be seen by viewing the output of SHOW VARIABLES:

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8                                   |
| character_set_connection | utf8                                   |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | utf8                                   |
| character_set_server     | latin1                                 |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+

现在停止客户端,并使用 mysqladmin 停止服务器。然后再次启动服务器,但这次告诉它跳过握手,如下所示:Now stop the client, and stop the server using mysqladmin. Then start the server again, but this time tell it to skip the handshake like so:

mysqld --character-set-server=utf8 --skip-character-set-client-handshake

再次以 utf8 作为默认字符集启动客户端,然后显示结果设置:Start the client with utf8 once again as the default character set, then display the resulting settings:

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | latin1                                 |
| character_set_connection | latin1                                 |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | latin1                                 |
| character_set_server     | latin1                                 |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+

如通过比较 SHOW VARIABLES 的不同结果可以看到,如果使用了 --skip-character-set-client-handshake 选项,服务器会忽略客户端的初始设置。As you can see by comparing the differing results from SHOW VARIABLES, the server ignores the client's initial settings if the --skip-character-set-client-handshake option is used.

A.11.11.

为什么某些带有 CJK 字符的 LIKE 和 FULLTEXT 搜索会失败?Why do some LIKE and FULLTEXT searches with CJK characters fail?

对于 LIKE 搜索,二进制字符串列类型如 BINARY 和 BLOB 有一个非常简单的问题:我们必须知道字符在哪里结束。对于多字节字符集,不同字符的字节长度可能不同。例如,在 utf8 中,A 需要一个字节,而 ペ 需要三个字节,如下所示:For LIKE searches, there is a very simple problem with binary string column types such as BINARY and BLOB: we must know where characters end. With multibyte character sets, different characters might have different octet lengths. For example, in utf8, A requires one byte but ペ requires three bytes, as shown here:

+-------------------------+---------------------------+
| OCTET_LENGTH(_utf8 'A') | OCTET_LENGTH(_utf8 'ペ') |
+-------------------------+---------------------------+
|                       1 |                         3 |
+-------------------------+---------------------------+

如果我们不知道字符串中第一个字符结束的位置,就不知道第二个字符开始的位置,在这种情况下,即使是非常简单的搜索,例如 LIKE '_A%' 也会失败。解决方案是使用定义为具有适当 CJK 字符集的非二进制字符串列类型。例如:mycol TEXT CHARACTER SET sjis。或者,在比较之前转换为 CJK 字符集。If we do not know where the first character in a string ends, we do not know where the second character begins, in which case even very simple searches such as LIKE '_A%' fail. The solution is to use a nonbinary string column type defined to have the proper CJK character set. For example: mycol TEXT CHARACTER SET sjis. Alternatively, convert to a CJK character set before comparing.

这也是 MySQL 不允许使用不存在字符的编码的原因之一。如果不严格拒绝错误输入,就无法知道字符结束的位置。This is one reason why MySQL cannot permit encodings of nonexistent characters. If it is not strict about rejecting bad input, it has no way of knowing where characters end.

对于 FULLTEXT 搜索,我们必须知道单词的起始和结束位置。对于西方语言,这通常不是问题,因为大多数(如果不是全部的话)都使用易于识别的单词边界:空格字符。然而,亚洲文字通常并非如此。我们可以使用一些任意的折中方法,比如假设所有汉字都代表一个单词,或者(对于日语)根据假名从片假名到平假名的变化来判断语法结尾。然而,唯一可靠的解决方案需要一个全面的单词表,这意味着我们必须为每种支持的亚洲语言在服务器中包含一个字典。这在实际上是不可行的。For FULLTEXT searches, we must know where words begin and end. With Western languages, this is rarely a problem because most (if not all) of these use an easy-to-identify word boundary: the space character. However, this is not usually the case with Asian writing. We could use arbitrary halfway measures, like assuming that all Han characters represent words, or (for Japanese) depending on changes from Katakana to Hiragana due to grammatical endings. However, the only sure solution requires a comprehensive word list, which means that we would have to include a dictionary in the server for each Asian language supported. This is simply not feasible.

A.11.12.

我如何知道字符 X 是否在所有字符集可用?How do I know whether character X is available in all character sets?

大多数简体中文字符和基本的非半角日文假名字符都出现在所有 CJK 字符集里。下面的存储过程接受一个 UCS-2 Unicode 字符,将其转换为其他字符集,并以十六进制显示结果。The majority of simplified Chinese and basic nonhalfwidth Japanese Kana characters appear in all CJK character sets. The following stored procedure accepts a UCS-2 Unicode character, converts it to other character sets, and displays the results in hexadecimal.

DELIMITER //

CREATE PROCEDURE p_convert(ucs2_char CHAR(1) CHARACTER SET ucs2)
BEGIN

CREATE TABLE tj
             (ucs2 CHAR(1) character set ucs2,
              utf8 CHAR(1) character set utf8,
              big5 CHAR(1) character set big5,
              cp932 CHAR(1) character set cp932,
              eucjpms CHAR(1) character set eucjpms,
              euckr CHAR(1) character set euckr,
              gb2312 CHAR(1) character set gb2312,
              gbk CHAR(1) character set gbk,
              sjis CHAR(1) character set sjis,
              ujis CHAR(1) character set ujis);

INSERT INTO tj (ucs2) VALUES (ucs2_char);

UPDATE tj SET utf8=ucs2,
              big5=ucs2,
              cp932=ucs2,
              eucjpms=ucs2,
              euckr=ucs2,
              gb2312=ucs2,
              gbk=ucs2,
              sjis=ucs2,
              ujis=ucs2;

/* If there are conversion problems, UPDATE produces warnings. */

SELECT hex(ucs2) AS ucs2,
       hex(utf8) AS utf8,
       hex(big5) AS big5,
       hex(cp932) AS cp932,
       hex(eucjpms) AS eucjpms,
       hex(euckr) AS euckr,
       hex(gb2312) AS gb2312,
       hex(gbk) AS gbk,
       hex(sjis) AS sjis,
       hex(ujis) AS ujis
FROM tj;

DROP TABLE tj;

END//

DELIMITER ;

输入可以是任意单个 ucs2 字符,或者是该字符的代码值(十六进制表示)。例如,从 Unicode 的 ucs2 编码和名称列表中(http://www.unicode.org/Public/UNIDATA/UnicodeData.txt)我们知道,片假名字符 Pe 出现在所有 CJK 字符集中,并且它的代码值是 X'30DA'。如果我们将此值作为 p_convert() 的参数,结果如下所示:The input can be any single ucs2 character, or it can be the code value (hexadecimal representation) of that character. For example, from Unicode's list of ucs2 encodings and names (http://www.unicode.org/Public/UNIDATA/UnicodeData.txt), we know that the Katakana character Pe appears in all CJK character sets, and that its code value is X'30DA'. If we use this value as the argument to p_convert(), the result is as shown here:

mysql> CALL p_convert(X'30DA');
+------+--------+------+-------+---------+-------+--------+------+------+------+
| ucs2 | utf8   | big5 | cp932 | eucjpms | euckr | gb2312 | gbk  | sjis | ujis |
+------+--------+------+-------+---------+-------+--------+------+------+------+
| 30DA | E3839A | C772 | 8379  | A5DA    | ABDA  | A5DA   | A5DA | 8379 | A5DA |
+------+--------+------+-------+---------+-------+--------+------+------+------+

由于没有任何列值是 3F(即问号字符,?),我们知道每次转换都成功了。Since none of the column values is 3F (that is, the question mark character, ?), we know that every conversion worked.

A.11.13.

为什么 CJK 字符串在 Unicode 中排序不正确?(I)Why do CJK strings sort incorrectly in Unicode? (I)

在旧版本的 MySQL 中出现的 CJK 排序问题,在 MySQL 8.0 中可以通过使用utf8mb4字符集和utf8mb4_ja_0900_as_cs排序规则来解决。CJK sorting problems that occurred in older MySQL versions can be solved as of MySQL 8.0 by using the utf8mb4 character set and the utf8mb4_ja_0900_as_cs collation.

A.11.14.

为什么 CJK 字符串在 Unicode 中排序不正确?(II)Why do CJK strings sort incorrectly in Unicode? (II)

在旧版本的 MySQL 中出现的 CJK 排序问题,在 MySQL 8.0 中可以通过使用utf8mb4字符集和utf8mb4_ja_0900_as_cs排序规则来解决。CJK sorting problems that occurred in older MySQL versions can be solved as of MySQL 8.0 by using the utf8mb4 character set and the utf8mb4_ja_0900_as_cs collation.

A.11.15.

为什么我的补充字符会被 MySQL 拒绝?Why are my supplementary characters rejected by MySQL?

补充字符位于 Unicode 的基本多语言平面 / 平面 0之外。BMP 字符的代码点值在U+0000到U+FFFF之间。补充字符的代码点值在U+10000到U+10FFFF之间。Supplementary characters lie outside the Unicode Basic Multilingual Plane / Plane 0. BMP characters have code point values between U+0000 and U+FFFF. Supplementary characters have code point values between U+10000 and U+10FFFF.

要存储补充字符,您必须使用允许它们的字符集:To store supplementary characters, you must use a character set that permits them:

  • utf8 和 ucs2 字符集仅支持 BMP 字符。The utf8 and ucs2 character sets support BMP characters only.

    utf8 字符集仅允许使用最多三字节的 UTF-8 字符。这导致了诸如 Bug #12600 中的报告,我们将其拒绝为 “不是错误”。对于 utf8,当 MySQL 遇到其无法识别的字节时,必须截断输入字符串。否则,将无法知道错误的多字节字符长度。The utf8 character set permits only UTF-8 characters that take up to three bytes. This has led to reports such as that found in Bug #12600, which we rejected as “not a bug”. With utf8, MySQL must truncate an input string when it encounters bytes that it does no understand. Otherwise, it is unknown how long the bad multibyte character is.

    一种可能的解决方法是使用 ucs2 而不是 utf8,在这种情况下,“坏” 字符会被改为问号。不过,不会进行截断。你也可以将数据类型改为 BLOB 或 BINARY,它们不会进行有效性检查。One possible workaround is to use ucs2 instead of utf8, in which case the “bad” characters are changed to question marks. However, no truncation takes place. You can also change the data type to BLOB or BINARY, which perform no validity checking.

  • 字符集 utf8mb4、utf16、utf16le 和 utf32 支持 BMP 字符以及 BMP 之外的补充字符。The utf8mb4, utf16, utf16le, and utf32 character sets support BMP characters, as well as supplementary characters outside the BMP.

A.11.16.

Should “CJK” be “CJKV”?

No. The term “CJKV” (Chinese Japanese Korean Vietnamese) refers to Vietnamese character sets which contain Han (originally Chinese) characters. MySQL supports the modern Vietnamese script with Western characters, but does not support the old Vietnamese script using Han characters.

截至 MySQL 5.6,对于 Unicode 字符集有越南文排序规则,如在第 12.10.1 节,“Unicode 字符集”中所述。As of MySQL 5.6, there are Vietnamese collations for Unicode character sets, as described in Section 12.10.1, “Unicode Character Sets”.

A.11.17.

MySQL 是否允许在数据库和表名中使用 CJK 字符?Does MySQL permit CJK characters to be used in database and table names?

可以。Yes.

A.11.18.

我在哪里可以找到 MySQL 手册的中文、日文和韩文翻译版本?Where can I find translations of the MySQL Manual into Chinese, Japanese, and Korean?

MySQL 5.6 手册的日文翻译可以从 https://dev.mysql.com/doc/ 下载。The Japanese translation of the MySQL 5.6 manual can be downloaded from https://dev.mysql.com/doc/.

A.11.19.

我在哪里可以获得关于 MySQL 的 CJK 及相关问题的帮助?Where can I get help with CJK and related issues in MySQL?

以下资源可用:The following resources are available: