特殊字符在 MySQL (UTF-8) 中不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3295002/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Special characters won't work in MySQL (UTF-8)
提问by Nisto
So, I've had some issues while trying to come over from Latin1encoded databases, tables as well as columns, and now that everything is finally in UTF-8, I can't seem to update a row in a column. I am trying to replace an "e" with an e with acute (é). But it gives me this:
所以，我在尝试从Latin1编码的数据库、表和列中过来时遇到了一些问题，现在一切都最终在 UTF-8 中，我似乎无法更新列中的行。我试图用带有尖刻 (é) 的 e 替换“e”。但它给了我这个：
ERROR 1366 (HY000): Incorrect string value: '\x82m ...' for column 'Name' at row 1
错误 1366 (HY000)：字符串值不正确：'\x82m ...' 列 'Name' 在第 1 行
when running this:
运行时：
UPDATE access SET Name='ém' WHERE id="2";
UPDATE access SET Name='ém' WHERE id="2";
All databases gives me this when running the status command (except the current databasepart of course):
所有数据库在运行 status 命令时都会给我这个（current database当然部分除外）：
Connection id: 1
Current database: access
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.1.47-community MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 20 min 16 sec
Threads: 1 Questions: 110 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 11 Queries per second avg: 0.90
And running the chcp command in cmd gives me 850. Oh, and at some points I got this:
在 cmd 中运行 chcp 命令会给我850. 哦，在某些时候我得到了这个：
ERROR 1300 (HY000): Invalid utf8 character string: 'ém' WHERE id="2"
错误 1300 (HY000)：无效的 utf8 字符串：'ém' WHERE id="2"
I've looked everywhere for a solution, but I couldn't seem to find anything anywhere, and since I've always had good responses on Stackoverflow, I thought I'd ask here.
我到处寻找解决方案，但我似乎在任何地方都找不到任何东西，而且由于我在 Stackoverflow 上的反应一直很好，所以我想我会在这里问。
Thanks for any help!
谢谢你的帮助！
采纳答案by Will A
This thread, although somewhat old, seems to result in the conclusion that cmd.exe and the mysql client don't handle UTF-8 encoding properly (with the blame being more aimed at cmd.exe).
这个线程虽然有点旧，但似乎得出的结论是 cmd.exe 和 mysql 客户端没有正确处理 UTF-8 编码（责备更多地针对 cmd.exe）。
Reading in SQL from a file is recommended, as is using an alternative client - or a flavour of UNIX. :)
建议从文件中读取 SQL，就像使用替代客户端一样 - 或一种 UNIX。:)
回答by bob esponja
The solution is to set the connection variables to whatever codepage your installation of windows uses (not latin1 like what a lot of pages out there recommend - cmd.exe's character encoding isn't latin1).
解决方案是将连接变量设置为您安装的 Windows 使用的任何代码页（不是 latin1，就像很多页面推荐的那样 - cmd.exe 的字符编码不是 latin1）。
In my case the codepage is 850:
在我的情况下，代码页是 850：
mysql> SET NAMES cp850;
mysql> SET NAMES cp850;
Here's an example with the connection set to UTF-8:
以下是连接设置为 UTF-8 的示例：
mysql> show variables like '%char%';
+--------------------------+---------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\xampp\mysql\share\charsets\ |
+--------------------------+---------------------------------+
8 rows in set (0.00 sec)
This is what happens to accented characters:
这是重音字符会发生的情况：
mysql> select nom from assignatura where nom like '%prob%';
+---------------------------------------+
| nom |
+---------------------------------------+
| Probabilitat i Processos Estocàstics |
| Probabilitat i Processos Estocàstics |
+---------------------------------------+
2 rows in set (0.03 sec)
Notice the extraneous ├character just before the á. Also the accent is the wrong direction, it should be à.
注意.├之前的无关字符á。此外，重音是错误的方向，应该是à。
After executing SET NAMES cp850;:
执行后SET NAMES cp850;：
mysql> show variables like '%char%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | cp850 |
| character_set_connection | cp850 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | cp850 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)
We finally get the correct accented character:
我们终于得到了正确的重音字符：
mysql> select nom from assignatura where nom like '%prob%';
+--------------------------------------+
| nom |
+--------------------------------------+
| Probabilitat i Processos Estocàstics |
| Probabilitat i Processos Estocàstics |
+--------------------------------------+
2 rows in set (0.00 sec)
回答by D.Shawley
Well ... 0x82 is e-acute in code page 850. It would be 0xE9 in ISO-8859-1 which makes it something like 0xD0 0xB4 in UTF-8. I don't know if there is a good way to get a DOS window to handle UTF-8 input correctly. Here is an alternative if you are using the command line client. You can set the client character set to match whatever your local code page is and let the mysql library take care of the transcoding for you:
嗯... 0x82 在代码页 850 中是 e-acute。它在 ISO-8859-1 中是 0xE9，这使它类似于 UTF-8 中的 0xD0 0xB4。不知道有没有什么好办法让DOS窗口正确处理UTF-8输入。如果您使用命令行客户端，这里有一个替代方案。您可以设置客户端字符集以匹配您的本地代码页，并让 mysql 库为您处理转码：
c:\> mysql --default-character-set=cp850
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.1.34, for apple-darwin9.6.0 (i386) using readline 5.2
Connection id: 17
Current database:
Current user: daveshawley@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.34-log Source distribution
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: ucs2
Db characterset: ucs2
Client characterset: cp850
Conn. characterset: cp850
TCP port: 3306
Uptime: 19 days 8 hours 37 min 55 sec
Threads: 2 Questions: 248 Slow queries: 0 Opens: 71 Flush tables: 1 Open tables: 64 Queries per second avg: 0.0
--------------
I know that this works for the combination of latin1in one window and utf8in another window on my MacBook. I also verified that an ALTER TABLE ... CONVERT TO CHARACTER SET ucs2did the right thing.
我知道这适用于在我的 MacBook 上的latin1一个窗口和utf8另一个窗口中的组合。我还验证了 anALTER TABLE ... CONVERT TO CHARACTER SET ucs2做了正确的事情。
回答by nos
When you input stuff on the command line, the strings will be in whatever character set the terminal uses. Why the mysql client doesn't translate that before sending it to the db still puzzles me, but it doesn't. You're probably sending latin1 to the db.
当您在命令行上输入内容时，字符串将使用终端使用的任何字符集。为什么 mysql 客户端在将它发送到数据库之前不翻译它仍然让我感到困惑，但事实并非如此。您可能正在将 latin1 发送到数据库。
You could save your update SQL in a text file, make surethat text file is UTF-8, and run
something like type myfile.txt | mysql db_name
您可以将更新 SQL 保存在文本文件中，确保文本文件是 UTF-8，然后运行类似type myfile.txt | mysql db_name
