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
CONVERT(CONVERT(CONVERT(`Question` USING latin1) USING binary) USING utf8)
| improve this answer | |

The data appears to be correctly encoded in UTF-8. phpMyAdmin is rendering the UTF-8 string using latin1 configuration.

Fix your phpMyAdmin and/or web server configuration, not your data. You may have set the default configuration of your webserver to ISO-8859-1 instead of UTF-8, or phpMyAdmin may be setting this encoding. It is possible to have headers specifying both encodings using different headers. Different browsers resolve this differently, so you may get a different rendering if you use a different browser.

| improve this answer | |
  • It's nice of you to think there was no bug in our application, but that's not the case. I realize that a UTF-8 é would look like what I posted on a latin1 terminal (which explains why the solution involves latin1), but I assure you, the data is double-encoded due to a bug in our application. phpMyAdmin is rendering correctly. In fact, I've never known it not to render properly, which is why the first thing I always ask when people have db encoding problem is "how does it look in phpMyAdmin?" – ikegami Jul 21 '16 at 3:09
  • @ikegami - Then I come along and say that that is not adequate. Only SELECT HEX(col) ... can check for sure on what is stored. é is E9 in cp1250, cp1256, cp1257, dec8, latin1, latin2, latin5, latin7, macroman; C3A9 for utf8/utf8mb4; and C383C2A9 if "double-encoded". – Rick James Jul 22 '16 at 3:06
  • @Rick James, Indeed, HEX would be better – ikegami Jul 22 '16 at 3:19
  • phpMyAdmin sets appropriate headers so that it doesn't vary by browser. I updated the question to include output of HEX. – ikegami Aug 3 '16 at 14:23

Don't go changing the text until you have verified that it was stored incorrectly, and not merely read incorrectly. Use HEX() as mentioned in "Test the data" in https://stackoverflow.com/a/38363567/1766831 .

Moving down in that same link, you can see the likely causes of the flawed data.

| improve this answer | |
  • phpMyAdmin is not the proof; it was there specifically to avoid these non-applicable answers. I shall update my question to show the 4 bytes in hex when I'm next at a computer. – ikegami Jul 22 '16 at 3:21
  • I updated the question to include the output of HEX. The cause (UTF-8 text over a latin1 connection) had already been fixed (by switching to a UTF-8 connection) before I posted the question. – ikegami Aug 3 '16 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.