How to fix UTF8 double encoded characters (in utf-8 table)

Question

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

This double coding created anomalies like ñ instead of ñ .

How to fix these lines?

Answer 1

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

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

It can be used with the UPDATE to correct fields:

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

Answer 2

The above answer worked for some of my data, but after starting it got many NULL columns. I thought that if the conversion was not successful, it returns null. To avoid this, I added a little check.

 UPDATE tbl SET col = CASE WHEN CONVERT(CAST(CONVERT(col USING latin1) AS BINARY) USING utf8) IS NULL THEN col ELSE CONVERT(CAST(CONVERT(col USING latin1) AS BINARY) USING utf8) END