如何更正 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() 来纠正错误记录并更新它们。

由于这次我有很多巨大的表，并且该错误仅影响德语变音符号（äöüßäÖÜ），我想知道是否有比这更智能/更快的解决方案。

像下面这样的纯 MySQL 解决方案安全且值得推荐吗？

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

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

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

（这是正确的语法 iirc；将适当的列类型放在 ... 所在的位置）

Answer 2

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

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

并且它很有效。希望这可以帮助那些像我一样从绝望的谷歌搜索中偶然发现这里的人。

注意：当然，这是假设您的双编码字符问题源于从latin1到utf8的过度有用的MySQL转换，但我相信这就是大多数“损坏的字符”发生的地方。这基本上执行与上面提到的相同的转换，返回到 latin1，然后是二进制，然后是 utf8（使用二进制步骤作为防止重新编码已编码的 latin1 实体的方法）

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 中找到提示：
http://blog. hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/

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

Answer 5

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

Answer 6

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

您还可以在转储上使用 iconv 对其进行转码。

您可以选择 INTO OUTFILE，使用 php 或 iconv 处理文件，然后 LOAD DATA INFILE。