解决升级到 PHP 5.3 后显示 MySQL 数据库结果时字符编码不正确的问题

Resolving incorrect character encoding when displaying MySQL database results after upgrade to PHP 5.3

提问by Shaun

Issue Description

问题描述

After upgrading PHP on our development server from 5.2 to 5.3, we're encountering an issue where data requested from our database and displayed on a web page shows with improper encoding when attempting to display Russian characters.

将我们的开发服务器上的 PHP 从 5.2 升级到 5.3 后，我们遇到了一个问题，即在尝试显示俄语字符时，从我们的数据库请求并显示在网页上的数据显示编码不正确。

Environment

环境

• Dev OS: Debian GNU/Linux 6.0
• Dev PHP: 5.3.5-0.dotdeb.1
• Live MySQL: Distrib 5.1.49

• 开发操作系统：Debian GNU/Linux 6.0
• 开发 PHP：5.3.5-0.dotdeb.1
• 实时 MySQL：分发 5.1.49

Details

细节

In PHP 5.3, the default client library for interacting with MySQL databases changed from libmysql to mysqlnd, which would appear to be the cause of the issue we are encountering.

在 PHP 5.3 中，与 MySQL 数据库交互的默认客户端库从 libmysql 更改为mysqlnd，这似乎是我们遇到的问题的原因。

We are connecting to the database with the following code:

我们使用以下代码连接到数据库：

$conn = mysql_pconnect('database.hostname', 'database_user', 'database_password');
$mysql_select_db('database', $conn);

The data stored in our database is encoded with UTF-8 encoding. Connecting to the database via the command-line client and running queries confirms that the data is intact and encoded properly. However, when we query the database in PHP and try to display the exact same data, it becomes garbled. In this specific case, we're attempting to display Russian characters and the result is non-English, non-Russian characters:

存储在我们数据库中的数据采用 UTF-8 编码。通过命令行客户端连接到数据库并运行查询可确认数据完整无缺且编码正确。但是，当我们在PHP中查询数据库并尝试显示完全相同的数据时，它变得乱码。在这种特定情况下，我们尝试显示俄语字符，结果是非英语、非俄语字符：

The response headers we receive confirm that the content-type is UTF-8:

我们收到的响应头确认内容类型是 UTF-8：

We tested the strings before display with mb_detect_encodingin strict mode as well as mb_check_encodingand were told the string was a UTF-8 string before displaying it. We also used mysql_client_encodingto test the client encoding and it also indicates the character set is UTF-8.

我们测试显示之前的字符串与mb_detect_encoding严格模式以及mb_check_encoding，并被告知该字符串显示前一个UTF-8字符串。我们还使用mysql_client_encoding来测试客户端编码，它也表明字符集是 UTF-8。

In performing research, we discovered some suggestionsto try to work around this issue:

在进行研究时，我们发现了一些尝试解决此问题的建议：

header("Content-type: text/html; charset=utf-8");
mysql_set_charset('utf8');
mysql_query("SET SESSION character_set_results = 'UTF8'");
mysql_query('SET NAMES UTF8', $conn);

We even tried utf8_encode:

我们甚至尝试过utf8_encode：

utf8_encode($string);

However, none of these solutions worked.

然而，这些解决方案都没有奏效。

Running out of options, we upgraded MySQL on our development system to Distrib 5.1.55. After that upgrade, everything displayed correctly when we connected to our development database. Of course, it continues to display incorrectly when we connect to our live database.

用尽所有选项，我们将开发系统上的 MySQL 升级到 Distrib 5.1.55。升级后，当我们连接到我们的开发数据库时，一切都正确显示。当然，当我们连接到我们的实时数据库时，它会继续错误地显示。

Ideally, we would like to resolve this issue without upgrading MySQL on our production servers unless we can verify the exact reason why this isn't working and why the upgrade will fix it. How can we resolve this encoding issue without upgrading MySQL? Alternatively, why does the MySQL upgrade fix the issue?

理想情况下，我们希望在不升级生产服务器上的 MySQL 的情况下解决此问题，除非我们可以验证这不起作用的确切原因以及升级将修复它的原因。我们如何在不升级 MySQL 的情况下解决这个编码问题？或者，为什么 MySQL 升级可以解决这个问题？

采纳答案by Pekka

If you have made sure that both the tables, and the output encoding are UTF-8, almost the only thing left is the connection encoding.

如果您确定表和输出编码都是 UTF-8，那么几乎唯一剩下的就是连接编码。

The reason for the change in behaviour when updating servers couldbe a change of the default connection encoding:

更新服务器时行为发生变化的原因可能是默认连接编码的变化：

[mysql]
default-character-set=utf8

However, I can't see any changes in the default encoding between versions, so if those were brand-new installs, I can't see that happening.

但是，我看不到版本之间的默认编码有任何变化，所以如果这些是全新安装，我看不到这种情况发生。

Anyway, what happens if you run this from within your PHP query and output the results. Any differences to the command line output?

无论如何，如果您从 PHP 查询中运行它并输出结果，会发生什么。命令行输出有什么不同吗？

 SHOW VARIABLES LIKE 'character_set%';
 SHOW VARIABLES LIKE 'collation%'; 

回答by Jarrod

I see you've tried this, but the syntax I use is: mysql_query("SET NAMES utf8"). Your syntax may be correct, I've just never seen it like that before.

我看到你已经尝试过了，但我使用的语法是：mysql_query("SET NAMES utf8")。您的语法可能是正确的，我以前从未见过这样的情况。

Example:

例子：

// connect to database stuff
$Connection = mysql_connect($server, $username, $password)
or die ("Error connecting to server");

// connect to database stuff
$db = mysql_select_db($database, $Connection)
or die ("Error selecting database");

mysql_query("SET NAMES utf8");

回答by Mariuszeq

I had similar issue after upgrading PHP from 5.2.3 to 5.3.5 (5.3.5-Win32-VC6-x86), MySQL 5.0.41 (not updated). I think that reason is a little difference between PHP versions.

将 PHP 从 5.2.3 升级到 5.3.5 (5.3.5-Win32-VC6-x86)、MySQL 5.0.41（未更新）后，我遇到了类似的问题。我认为这个原因是 PHP 版本之间的一些差异。

PHP 5.2.3 default (without SET NAMES):
character_set_client = latin1
character_set_connection = latin1
character_set_database = utf8
character_set_filesystem = binary
character_set_results = latin1
character_set_server = latin2
character_set_system = utf8
collation_connection = latin1_swedish_ci
collation_database = utf8_polish_ci
collation_server = latin2_general_ci

PHP 5.2.3默认（不SET NAMES）：
character_set_client字符= latin1的
是character_set_connection = LATIN1
character_set_database = UTF8
character_set_filesystem =二进制
character_set_results = LATIN1
被character_set_server =拉丁文2
character_set_system = UTF8
collat​​ion_connection是= latin1_swedish_ci
collat​​ion_database查看= utf8_polish_ci
collat​​ion_server的= latin2_general_ci

PHP 5.3.5 default (without SET NAMES):
character_set_client = latin2
character_set_connection = latin2
character_set_database = utf8
character_set_filesystem = binary
character_set_results = latin2
character_set_server = latin2
character_set_system = utf8
collation_connection = latin2_general_ci
collation_database = utf8_polish_ci
collation_server = latin2_general_ci

PHP 5.3.5默认（不SET NAMES）：
character_set_client字符=拉丁文2
是character_set_connection =拉丁文2
character_set_database = UTF8
character_set_filesystem =二进制
character_set_results =拉丁文2
被character_set_server =拉丁文2
character_set_system = UTF8
collat​​ion_connection是= latin2_general_ci
collat​​ion_database查看= utf8_polish_ci
collat​​ion_server的= latin2_general_ci

I added data to database in PHP 5.2.3 default (without SET NAMES), so now to display it correctly I must read it using:

我在 PHP 5.2.3 默认（没有 SET NAMES）中将数据添加到数据库，所以现在要正确显示它，我必须使用以下方法读取它：

$pdo -> query("SET NAMES 'latin1'");

Maybe something similar is reason of your problem.

也许类似的事情是你的问题的原因。