如何纠正存储在MySQL utf8_general_ci字段中的双重编码的UTF-8字符串？

Question

我需要重新设计一个类，其中（除其他事项外）UTF-8字符串被错误双重编码：

$string = iconv('ISO-8859-1', 'UTF-8', $string);
:
$string = utf8_encode($string);

这些有问题的字符串已经保存在MySQL数据库中多个表字段中。所有受影响的字段使用排序规则utf8_general_ci。

通常我会设置一个小的PHP修补脚本，循环遍历受影响的表格，选择记录，通过在双编码字段上使用utf8_decode()来纠正有问题的记录并更新它们。

由于这次有许多和巨大的表格，并且错误仅影响德语umlauts(äöüßÄÖÜ), 我想知道是否有比那更聪明/更快的解决方法。

像以下的纯MySQL解决方案是否安全和可靠？

 UPDATE `table` SET `col` = REPLACE(`col`, 'ä', 'ä');

还有其他解决方案/最佳实践吗？

- Jürgen Thelen

Answer 1

将表格修改为将列字符集更改为Latin-1。现在您将拥有单一编码的UTF-8字符串，但它们位于一个排序规则应该是Latin-1的字段中。

然后你需要通过二进制字符集将列字符集改回UTF-8 - 这样MySQL就不会在任何时候转换字符。

ALTER TABLE MyTable MODIFY MyColumn ... CHARACTER SET latin1
ALTER TABLE MyTable MODIFY MyColumn ... CHARACTER SET binary
ALTER TABLE MyTable MODIFY MyColumn ... CHARACTER SET utf8

(如果我没记错的话，这应该是正确的语法；在...中放入适当的列类型)

- Hammerite

---

哎呀，这个问题真的让我惊出了一身冷汗（双关语^^）！感谢您从魔法袋中取出了这个解决方案。它非常有效，让我不再担心“内存不足”的问题。我只需要使用CHANGE而不是MODIFY。顺便说一下，我注意到切换到字符集latin1会隐式地将排序规则切换为latin1_swedish_ci。是否更安全地附加COLLATE latin1_swedish_ci来强制执行此操作？对于binary（切换到none排序规则）和utf8（切换回utf8_general_ci排序规则），情况也是如此。再次感谢您，您救了我的夜晚^^。 - Jürgen Thelen

我认为每次更改字符集时，如果您没有指定排序规则，则排序规则将是新字符集的默认排序规则。对于前两个更改，排序规则不应该有影响，因为您只是暂时使用该字符集，并且排序规则不会影响表中存储的字符或存储的字符。只有编码在这两个更改中起作用。因此，您只需要在最后一个更改（即更改回UTF-8）时指定排序规则。但是，如果我错了，我欢迎被纠正。 - Hammerite

感谢您进一步阐述。在MySQL文档中找到了一个页面，证实了您对默认排序规则回退的想法。对我来说，仅在最后一个开关上强制使用排序规则是有意义的。我会这样做。 - Jürgen Thelen

跳过第一个 ALTER -- 它是不必要的，可能会损坏数据。 - Rick James

如果您跳过第一个ALTER命令，那么这些命令将不会产生任何实际效果。您只需要稍微等待一段时间来更改列定义，然后立即将其更改回来。您能否具体说明数据可能会“受损”的声明？潜在损害的性质是什么？ - Hammerite

Answer 2

我尝试了已发布的解决方案，但是我的数据库一直出错。最终我偶然发现了以下解决方案（我想是在某个论坛上，但我不记得在哪里）：

UPDATE table_name SET col_name = CONVERT(CONVERT(CONVERT(col_name USING latin1) USING binary) USING utf8);

它非常有效，希望这能帮助像我一样通过绝望的谷歌搜索来到这里的人。

注意：这当然是假定您的双编码字符问题源于过于热心的MySQL将latin1转换为utf8，但我相信这就是大多数这些“损坏的字符”发生的地方。这基本上执行与上述相同的转换回到latin1，然后是binary，最后是utf8（使用binary步骤作为防止已经编码的latin1实体重新编码的方法）

- Owen Waring

Answer 3

我发现以下方法更简单：

mysqldump -h DB_HOST -u DB_USER -p --skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql

然后，使用以下命令删除所有表并重新导入：

mysql -h DB_HOST -u DB_USER -p --default-character-set=utf8 DB_NAME < DB_NAME-dump.sql

以下这个URL是关于如何解决MySQL中双重编码UTF-8数据的问题： http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/

- Ross

---

这个解决方案比这里的其他解决方案更容易且更快。 - coding Bott

这个很好用...在双重编码的列上。但是它搞砸了我原本正确编码的列（别问为什么），把所有的西里尔文都变成了??????????????????。通过将新重新编码的文本复制回旧表格，很容易解决。谢谢。 - ow3n

Answer 4

MySql是字符集感知的，因此您可以在SQL中进行转换。但对于这种情况，我可能更喜欢在PHP中编写脚本，因为它只是一个一次性任务。

请记住，MySql中的列具有字符集属性。排序规则（理论上）与字符集正交。虽然utf8_general_ci排序规则会暗示字符集为utf8，但这并不是必然的。您可以在理论上将utf8排序规则与latin1编码混合使用（结果会得到垃圾数据）。

如果您决定在SQL中执行此操作，请查看此处：

http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

- troelskn

---

老实说，在你的第二段提到它之前，我一直认为排序规则 utf8_general_ci 就等同于字符集 utf8。谢谢你让我再次阅读 MySQL 基础文档，时间已经过去几十年了（这次更加仔细^^）。非常感谢你。+1 - Jürgen Thelen

Answer 5

MySQL提供了正则表达式匹配，但没有正则表达式替换，因此通常最好在php中迭代每一行，根据需要进行转换，并在更改后更新该行。

- Denis de Bernardy

Answer 6

使用mysqldump生成一个转储文件，更改编码声明（在第一条命令中），然后重新加载到另一个数据库中。

您还可以使用iconv对转储文件进行转码。

您可以使用SELECT INTO OUTFILE将数据导出到文件中，然后使用php或iconv处理该文件，最后使用LOAD DATA INFILE将其加载回数据库中。

- bobflux

---

我曾考虑过对整个转储文件进行utf8_decode，但最终放弃了。原因是有些表格太大了（每个表格都超过1G），这将迫使我逐行读取和转换整个转储文件。无论如何还是谢谢。 - Jürgen Thelen