MySQL在UTF-8 PHP文件中输出西方编码 [英] MySQL outputs Western encoding in UTF-8 PHP file

问题描述

我有以下问题:在一个非常简单的php-mysqli查询上:

I have the following problem: on a very simple php-mysqli query:

if ( $result = $mysqli->query( $sqlquery ) )
{
    $res = $result->fetch_all();
    $result->close();
}

我得到的字符串被错误地编码为Western编码的字符串，尽管数据库，表和列都在utf8_general_ci排序规则中. php脚本本身是utf-8编码的，脚本的不含mysql的部分将获得正确的编码.可以说echo "ő"完美地工作，但是当以正确的UTF-8编码查看文件时，上一个示例中的echo $res[0]输出EF BF BD字符.如果我手动将浏览器的编码切换为Western，则mysqli来源的字符串将获得良好的解码，但非西方字符将替换为?".

I get strings wrongly encoded as Western encoded string, although the database, the table and the column is in utf8_general_ci collation. The php script itself is utf-8 encoded and the mysql-less parts of the script get the correct encodings. So say echo "ő" works perfectly, but echo $res[0] from the previous example outputs the EF BF BD character when the file viewed in the correct UTF-8 encoding. If I manually switch the browser's encoding to Western, the mysqli sourced strings get good decoding, except for the non-western characters being replaced with "?'.

使它变得更加奇怪的是，在我的开发环境中，这没有发生，而在我的Web服务器上，这是发生的.开发人员环境是LAMP堆栈(统一服务器)，而Web服务器使用的是nginx.

What makes it even stranger is that on my development environment this isn't happening, while on my webserver it is. The developer environment is a LAMP stack (The Uniform Server), while the webserver uses nginx.

在这种情况下，我使用phpMyAdmin输入了数据库中的数据，并且在phpmyadmin内部它完美显示. phpMyAdmin的排序规则也是utf-8.我相信问题一定在这里，就像在同一台Web服务器上一样，对于我通过php(使用POST)输入数据的其他站点，不会发生同样的问题.在这种情况下，无论在输入数据还是在查看数据时都可以正确看到数据(我的意思是在php生成的网页中)，但是在phpMyAdmin中特殊字符不正确.

In this case, I entered the data in the database using phpMyAdmin, and inside phpmyadmin it displays perfectly. phpMyAdmin's collation is utf-8 too. I believe that the problem must be somewhere around here, as on the same webserver, for an other site where I enter data through php (using POST) the same problem doesn't happen. On that case, the data is visible correctly both while entering and while viewing it (I mean in the php generated webpages), but the special characters are not correct in phpMyAdmin.

您能帮助我在哪里调试吗?它是否连接到 php 或 mysql 或 nginx 或 phpMyAdmin ?

Can you help me start where to debug? Is it connected to php or mysql or nginx or phpMyAdmin?

推荐答案

使用 mysqli_set_charset 在连接后将客户端编码更改为UTF-8:

Use mysqli_set_charset to change the client encoding to UTF-8 just after you connect:

$mysqli->set_charset("utf8");

客户端编码是MySql期望输入的内容(例如，当您向搜索查询中插入用户提供的文本时)以及它提供的结果(因此，它必须与您的输出编码匹配，以便echo以正确显示内容.

The client encoding is what MySql expects your input to be in (e.g. when you insert user-supplied text to a search query) and what it gives you the results in (so it has to match your output encoding in order for echo to display things correctly).

您需要使其与网页的编码相匹配，以解决上述和两种情况下PHP源文件的编码(以便正确解释查询的硬编码部分)

You need to have it match the encoding of your web page to account for the two scenarios above and the encoding of the PHP source file (so that the hardcoded parts of your queries are interpreted correctly).

更新:如何将使用latin-1插入的数据转换为utf-8

对于已经使用错误的连接编码插入的数据，有一个解决此问题的简便方法.对于包含此类数据的每一列，您需要执行以下操作:

Regarding data that have already been inserted using the wrong connection encoding there is a convenient solution to fix the problem. For each column that contains this kind of data you need to do:

ALTER TABLE table_name MODIFY column_name existing_column_type CHARACTER SET latin1;
ALTER TABLE table_name MODIFY column_name BLOB;
ALTER TABLE table_name MODIFY column_name existing_column_type CHARACTER SET utf8;

每次用数据库中的正确值替换占位符table_name，column_name和existing_column_type.

The placeholders table_name, column_name and existing_column_type should be replaced with the correct values from your database each time.

这是什么

1. 告诉MySql它需要将数据存储在latin1的该列中.此字符集仅包含utf8的一小部分，因此通常此转换涉及数据丢失，但是在此特定情况下，数据在输入时已被解释为latin1，因此不会产生副作用.但是，MySql会在内部转换数据的字节表示形式，以匹配最初从PHP发送的数据.
2. 将列转换为没有关联的编码信息的二进制类型(BLOB).此时，该列将包含原始字节，这些原始字节是正确的utf8字符串.
3. 将列转换为其以前的字符类型，告诉MySql应将原始字节视为utf8编码.

1. Tell MySql that it needs to store data in that column in latin1. This character set contains only a small subset of utf8 so in general this conversion involves data loss, but in this specific scenario the data was already interpreted as latin1 on input so there will be no side effects. However, MySql will internally convert the byte representation of your data to match what was originally sent from PHP.
2. Convert the column to a binary type (BLOB) that has no associated encoding information. At this point the column will contain raw bytes that are a proper utf8 character string.
3. Convert the column to its previous character type, telling MySql that the raw bytes should be considered to be in utf8 encoding.

警告:仅当所讨论的列仅包含 个错误插入的数据时，才可以使用这种随意选择的方法.第一次出现任何非ASCII字符时，所有已正确插入的数据都会被截断！

WARNING: You can only use this indiscriminate approach if the column in question contains only incorrectly inserted data. Any data that has been correctly inserted will be truncated at the first occurrence of any non-ASCII character!

因此，在PHP侧面修复程序生效之前，现在就这样做是个好主意.

Therefore it's a good idea to do it right now, before the PHP side fix goes into effect.

这篇关于MySQL在UTF-8 PHP文件中输出西方编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！