我如何让MySQL返回UTF-8? [英] How do I make MySQL return UTF-8?
问题描述
我使用PHPUnit从我的PHP代码验证XML输出,但显然我有字符编码的问题 MySQL 返回。这是我从DOMDocument得到的错误:
I'm using PHPUnit to validate XML output from my PHP code, but apparently I have problems with the character encoding MySQL returns. Here is the error I get from DOMDocument:
Input is not proper UTF-8, indicate encoding!
Bytes: 0xE9 0x20 0x42 0x65
我初始化DOMDocument使它使用正确的编码:
I initialize the DOMDocument so it uses the correct encoding:
$domDocument = new DOMDocument('1.0','UTF-8');
当我使用mb_detect_encoding检查saveXML()的输出时,结果是 UTF-8 。
And when I check the output from saveXML() using mb_detect_encoding the result is UTF-8.
我也检查了所有通话用于创建XML,对遇到的所有createCDATASection参数使用mb_detect_encoding,它们都是UTF-8或ASCII(没有纯文本节点,一切都在 CDATA blocks)。
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 CDATA blocks).
我认为问题来自于使用一个é字符在 ISO 8859-1 中)。将该字符添加到我的XML中的行是:
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:
$domDocument->createCDATASection($place->name);
和mb_detect_encoding($ place-> name)给出了UTF-8。
and mb_detect_encoding($place->name) gives me UTF-8.
数据($ place-> name)是从MySQL数据库中提取的。此数据库具有UTF-8字符集。
The data ($place->name) is pulled from a MySQL database. This database has the UTF-8 charset.
以下是一些示例代码:
$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);
在我的PHPStorm调试器中,从数据库获取的字符串如下所示:
In my PHPStorm debugger, the string fetched from the database looks like this:
Caf Belga
Caf� Belga
所以我认为这是问题。在MySQLWorkbench中的字符串是正确的:CaféBelga。
So I think that is the root of the problem. In MySQLWorkbench the string is correct: Café Belga.
当使用 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?
推荐答案
以 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);
这篇关于我如何让MySQL返回UTF-8?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!