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. 现在考虑表格的以下部分。Now consider the following portion of the table. 这意味着 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: 如前所述,汌 字符不在 gb2312 字符集中。The 汌 character is not in the
gb2312 character set, as described
earlier. 如果您使用的是旧版本的 MySQL,可能会看到不同的消息。If you are using an old version of MySQL, you may see a
different message. 出现警告而不是错误,是因为 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”: 其效果是,你无法通过使用 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:
|