MySQL Encoding – How to Fix Double-Encoded Data

encodingMySQL

I have a field defined as Question VARCHAR(1024) CHARACTER SET utf8 NOT NULL. Our application stored double-encoded data into this field. For example, é shows up as é in phpMyAdmin, and C383C2A9 when passed to HEX.

I've added a second field (DoubleEncoded TINYINT NOT NULL DEFAULT 1), which identifies which records suffer from this problem.

I want to fix the remaining fields. Is there a way to do this using MySQL SQL? In other words, I need something to replace decode_utf8 in the following:

UPDATE `MyTable`
   SET `Question` = decode_utf8(`Question`),
       `DoubleEncoded` = 0
 WHERE `DoubleEncoded` = 1

Best Answer

CONVERT(CONVERT(CONVERT(`Question` USING latin1) USING binary) USING utf8)
Related Question