如何修复双重编码的 UTF8 字符(在 utf-8 表中)

2021-11-20 00:00:00 string utf-8 character-encoding mysql

之前的 LOAD DATA INFILE 是在假设 CSV 文件是 latin1 编码的情况下运行的.在此导入过程中，多字节字符被解释为两个单个字符，然后(再次)使用 utf-8 进行编码.

A previous LOAD DATA INFILE was run under the assumption that the CSV file is latin1-encoded. During this import the multibyte characters were interpreted as two single character and then encoded using utf-8 (again).

这种双重编码产生了像ñ而不是ñ这样的异常.

This double-encoding created anomalies like ñ instead of ñ.

如何更正这些字符串?

推荐答案

下面的MySQL函数会在双重编码后返回正确的utf8字符串:

The following MySQL function will return the correct utf8 string after double-encoding:

CONVERT(CAST(CONVERT(field USING latin1) AS BINARY) USING utf8)

它可以与 UPDATE 语句一起使用以更正字段:

It can be used with an UPDATE statement to correct the fields:

UPDATE tablename SET
    field = CONVERT(CAST(CONVERT(field USING latin1) AS BINARY) USING utf8);

相关文章