要使用utf8或者不是 - MySQL和PHP字符编码问题 [英] To use utf8 or not - MySQL and PHP character encoding issue

问题描述

我有一个存储在MySQL中的字符串，如下所示：ï»Ø§Ø²Ø其中我的表的字符集 utf8 并且排序规则是 utf8_general_ci 。

I have a string stored in MySQL like this: یکی از Ø where my table's character set is utf8 and collation is utf8_general_ci.

当我从MySQL检索字符串时，浏览器显示它就像这样： 并且确定（这是波斯语）。

When I retrieve the string from MySQL, the browser shows it like this: یکی از بهترین راه and it's ok (It is persian).

注意：

• 连接后使用 mysql_query（SET NAMES'utf8_persian_ci'）;
  到MySQL。

• I used mysql_query("SET NAMES 'utf8_persian_ci'"); after connecting to MySQL.

我将< meta http-equiv =Content-Type
=text / html; charset = utf-8/> 标记每个页面的头部。

I put <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> tag in head of each page.

现在，我需要使用 mysql_query（SET NAMES'utf8'）; 。

但是在使用它之后，浏览器会显示如下字符串：یکیازØ（与存储在MySQL中的相同）。

But after using it, the browser shows that string like this: یکی از Ø (as same as stored in MySQL).

如何更改我的MySQL存储记录并解决我的问题？或者使用一些PHP代码来转换输出编码？

How can I change my MySQL stored records and solve my problem? Or use some PHP code to convert the output encoding?

推荐答案

你的问题是你的 SET NAMES' utf8_persian_ci'命令无效（utf8_persion_ci是排序规则，而不是编码）。如果你在终端运行它，你会看到一个错误未知字符集：'utf8_persian_ci'。因此，您的应用程序在存储数据时使用 latin1 字符集。 MySQL将您的输入解释为latin1字符，然后将其存储为utf-8。同样，当数据被拉回时，MySQL将它从UTF-8转换回latin1，并且（希望大部分时间）是你给它的原始字节。

Your problem is that your SET NAMES 'utf8_persian_ci' command was invalid (utf8_persion_ci is a collation, not an encoding). If you run it in a terminal you will see an error Unknown character set: 'utf8_persian_ci'. Thus your application, when it stored the data, was using the latin1 character set. MySQL interpreted your input as latin1 characters which it then stored encoded as utf-8. Likewise when the data was pulled back out, MySQL converted it from UTF-8 back to latin1 and (hopefully, most of the time) the original bytes you gave it.

换句话说，数据库中的所有数据都是完全混乱的，但它只是发生了工作。

In other words, all your data in the database is completely messed up, but it just so happened to work.

要解决这个问题，你需要撤消你所做的。最直接的方法是使用PHP：

To fix this, you need to undo what you did. The most straightforward way is using PHP:

1. SET NAMES latin1;


2. 从每个表格中选择每个文字字段。


3. SET NAMES utf8;
$

1. SET NAMES latin1;
2. Select every single text field from every table.
3. SET NAMES utf8;
4. Update the same rows using the same string unaltered.

$ b 此外，您也可以在MySQL中执行这些步骤，但是棘手，因为MySQL理解数据在一个特定的字符集。您需要将文本列修改为BLOB类型，然后将它们回到修改为使用utf8字符集的文本类型。请参阅 ALTER的底部一节TABLE 以红色标记为Warning的MySQL文档。

Alternatively you can perform these steps inside MySQL, but it's tricky because MySQL understands the data to be in a certain character set. You need to modify your text columns to a BLOB type, then modify them back to text types with a utf8 character set. See the section at the bottom of the ALTER TABLE MySQL documentation labeled "Warning" in red.

执行以下任一操作后，数据库列将是他们声称的实际字符集。然后，确保您始终使用 mysql_set_charset （'utf8'） ，您可以在未来做任何数据库访问！否则你会再次混乱。 （注意，不要使用简单的 mysql_query（'SET NAMES utf8'）！有一些情况（例如重置连接） mysql_set_charset（）将在必要时设置该字符集。）

After you do either one of these things, the bytes stored in your database columns will be the actual character set they claim to be. Then, make sure you always use mysql_set_charset('utf8') on any database access from PHP that you may do in the future! Otherwise you will mess things up again. (Note, do not use a simple mysql_query('SET NAMES utf8')! There are corner cases (such as a reset connection) where this can be reset to latin1 without your knowledge. mysql_set_charset() will set the charset whenever necessary.)

如果你切换到 mysql _ * 函数并使用 PDO 而不是 charset = utf8 参数rel =nofollow> PDO dsn 。

It would be best if you switched away from mysql_* functions and used PDO instead with the charset=utf8 parameter in your PDO dsn.

这篇关于要使用utf8或者不是 - MySQL和PHP字符编码问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！