vbence vbence - 4 months ago 16
MySQL Question

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

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
ñ
.

How to correct these strings?

Answer

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 an UPDATE statement to correct the fields:

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