php 如何让 MySQL 返回 UTF-8？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6225013/
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
How do I make MySQL return UTF-8?
提问by Joris Mans
I'm using PHPUnit to validate XML output from my PHP code, but apparently I have problems with the character encoding MySQLreturns. Here is the error I get from DOMDocument:
我正在使用 PHPUnit 来验证来自我的 PHP 代码的 XML 输出，但显然我对MySQL返回的字符编码有问题。这是我从 DOMDocument 得到的错误：
Input is not proper UTF-8, indicate encoding!
Bytes: 0xE9 0x20 0x42 0x65
I initialize the DOMDocument so it uses the correct encoding:
我初始化 DOMDocument 以便它使用正确的编码：
$domDocument = new DOMDocument('1.0','UTF-8');
And when I check the output from saveXML() using mb_detect_encoding the result is UTF-8.
当我使用 mb_detect_encoding 检查 saveXML() 的输出时，结果是UTF-8。
I also checked all the calls used to create the XML, using mb_detect_encoding on all createCDATASection parameters encountered and they are all either UTF-8 or ASCII (there are no plain text nodes, everything is in CDATAblocks).
我还检查了用于创建 XML 的所有调用，对遇到的所有 createCDATASection 参数使用 mb_detect_encoding，它们都是 UTF-8 或 ASCII（没有纯文本节点，所有内容都在CDATA块中）。
I think the issue comes from the use of an 'é' character (which is 0xE9 in ISO 8859-1). The line which adds that character to my XML is:
我认为问题来自于使用“é”字符（在ISO 8859-1 中为 0xE9 ）。将该字符添加到我的 XML 的行是：
$domDocument->createCDATASection($place->name);
and mb_detect_encoding($place->name) gives me UTF-8.
和 mb_detect_encoding($place->name) 给了我 UTF-8。
The data ($place->name) is pulled from a MySQL database. This database has the UTF-8 charset.
数据 ($place->name) 是从 MySQL 数据库中提取的。该数据库具有 UTF-8 字符集。
Here is some example code:
下面是一些示例代码：
$query = sprintf('SELECT name FROM place where id = 1');
$result = mysql_query($query);
$result = mysql_fetch_assoc($result);
// -- Feeding UTF-8 data directly WORKS
$domDocument = new DOMDocument('1.0','UTF-8');
$rootNode = $domDocument->createElement('Response');
$rootNode->appendChild($domDocument->createCDATASection('Café Belga'));
$domDocument->appendChild($rootNode);
$matcher = array('tag' => 'Response');
self::assertTag($matcher, $domDocument->saveXML(), '', FALSE);
// -- Feeding UTF-8 data from the resultset FAILS
$domDocument = new DOMDocument('1.0','UTF-8');
$rootNode = $domDocument->createElement('Response');
$rootNode->appendChild($domDocument->createCDATASection($result['name']));
$domDocument->appendChild($rootNode);
$matcher = array('tag' => 'Response');
self::assertTag($matcher, $domDocument->saveXML(), '', FALSE);
In my PHPStorm debugger, the string fetched from the database looks like this:
在我的 PHPStorm 调试器中，从数据库中提取的字符串如下所示：
Caf? Belga
咖啡馆？贝尔加
So I think that is the root of the problem. In MySQLWorkbench the string is correct: Café Belga.
所以我认为这是问题的根源。在 MySQLWorkbench 中，字符串是正确的：Café Belga。
When using utf8_encode($result['name']), however, everything works fine!
utf8_encode($result['name'])但是，当使用时，一切正常！
One more check in the watches window:
在手表窗口中再检查一项：
mb_detect_encoding($result['name'])-> "UTF-8"
mb_detect_encoding($result['name'])-> "UTF-8"
mb_detect_encoding(utf8_encode($result['name']))-> "UTF-8"
mb_detect_encoding(utf8_encode($result['name']))-> "UTF-8"
On a side note, are there any sites where I can simply copy-paste those hex values and see what characters they are supposed to be in different character sets?
附带说明一下，是否有任何网站可以简单地复制粘贴这些十六进制值并查看它们应该在不同的字符集中哪些字符？
回答by strauberry
You have to define the connection to your database as UTF-8:
您必须将与数据库的连接定义为UTF-8：
// Set up your connection
$connection = mysql_connect('localhost', 'user', 'pw');
mysql_select_db('yourdb', $connection);
mysql_query("SET NAMES 'utf8'", $connection);
// Now you get UTF-8 encoded stuff
$query = sprintf('SELECT name FROM place where id = 1');
$result = mysql_query($query, $connection);
$result = mysql_fetch_assoc($result);
回答by Eric Korolev
From version PHP 5.5.0 you should use
从 PHP 5.5.0 版本开始，您应该使用
mysqli_set_charset($connection,"utf8");
