Mysql Field Encoding issue

Question

I truly wouldn't be asking this if I hadn't tried everything, However I can't seem to decode the following MYSQL description field string.

“Let’s

I've tried to following statement in MYSQL to no avail.

update mytable set mycolumn = 
    convert(binary convert(mycolumn using latin1) using utf8);

This decodes the string to âLetâs go there,â which obviously still isn't correct.

Are there any other pointers someone could pass to solve this issue? There are more examples than just this character encoding across thousands of fields.

Thanks

Answer 1

It comes close to be "double-encoding" of

‘Let‛s

Here are some potentially related characters:

E28098     8216=x2018  [‘]   ON  LEFT SINGLE QUOTATION MARK
E2809A     8218=x201A  [‚]   ON  SINGLE LOW-9 QUOTATION MARK
E2809B     8219=x201B  [‛]   ON  SINGLE HIGH-REVERSED-9 QUOTATION MARK
E2809C     8220=x201C  [“]   ON  LEFT DOUBLE QUOTATION MARK

Please provide SELECT HEX(col), I think there might be a byte that got lost in pasting into this web page. And I think that the â comes from a decoding attempt that failed because of truncation due the that missing byte.

"Double encoding" happens when the text has been incorrectly labeled as latin1, then stored into a utf8 column in the table. Then that is repeated.

More: Trouble with UTF-8 characters; what I see is not what I stored

More on quotes: http://mysql.rjweb.org/doc.php/charcoll#quotes

More on Mojibake: http://mysql.rjweb.org/doc.php/charcoll#8_bit_encodings (see second table)

Answer 2

It may not be a character set that MySQL recognizes. Running SHOW CHARACTER SET; returns all charactersets avalable in MySQL, so a script can be run for all of them.

The one that looks right would be the winner, I guess. If none of them look right, you may have to decode this outside of MySQL.

select convert(binary convert(mycolumn using armscii8 ) using utf8) txt, 1 union
select convert(binary convert(mycolumn using ascii ) using utf8) txt, 2 union
select convert(binary convert(mycolumn using big5 ) using utf8) txt, 3 union
select convert(binary convert(mycolumn using binary ) using utf8) txt, 4 union
select convert(binary convert(mycolumn using cp1250 ) using utf8) txt, 5 union
select convert(binary convert(mycolumn using cp1251 ) using utf8) txt, 6 union
select convert(binary convert(mycolumn using cp1256 ) using utf8) txt, 7 union
select convert(binary convert(mycolumn using cp1257 ) using utf8) txt, 8 union
select convert(binary convert(mycolumn using cp850 ) using utf8) txt, 9 union
select convert(binary convert(mycolumn using cp852 ) using utf8) txt, 10 union
select convert(binary convert(mycolumn using cp866 ) using utf8) txt, 11 union
select convert(binary convert(mycolumn using cp932 ) using utf8) txt, 12 union
select convert(binary convert(mycolumn using eucjpms ) using utf8) txt, 13 union
select convert(binary convert(mycolumn using euckr ) using utf8) txt, 14 union
select convert(binary convert(mycolumn using gb18030 ) using utf8) txt, 15 union
select convert(binary convert(mycolumn using gb2312 ) using utf8) txt, 16 union
select convert(binary convert(mycolumn using gbk ) using utf8) txt, 17 union
select convert(binary convert(mycolumn using geostd8 ) using utf8) txt, 18 union
select convert(binary convert(mycolumn using greek ) using utf8) txt, 19 union
select convert(binary convert(mycolumn using hebrew ) using utf8) txt, 20 union
select convert(binary convert(mycolumn using hp8 ) using utf8) txt, 21 union
select convert(binary convert(mycolumn using keybcs2 ) using utf8) txt, 22 union
select convert(binary convert(mycolumn using koi8r ) using utf8) txt, 23 union
select convert(binary convert(mycolumn using koi8u ) using utf8) txt, 24 union
select convert(binary convert(mycolumn using latin1 ) using utf8) txt, 25 union
select convert(binary convert(mycolumn using latin2 ) using utf8) txt, 26 union
select convert(binary convert(mycolumn using latin5 ) using utf8) txt, 27 union
select convert(binary convert(mycolumn using latin7 ) using utf8) txt, 28 union
select convert(binary convert(mycolumn using macce ) using utf8) txt, 29 union
select convert(binary convert(mycolumn using macroman ) using utf8) txt, 30 union
select convert(binary convert(mycolumn using sjis ) using utf8) txt, 31 union
select convert(binary convert(mycolumn using swe7 ) using utf8) txt, 32 union
select convert(binary convert(mycolumn using tis620 ) using utf8) txt, 33 union
select convert(binary convert(mycolumn using ucs2 ) using utf8) txt, 34 union
select convert(binary convert(mycolumn using ujis ) using utf8) txt, 35 union
select convert(binary convert(mycolumn using utf16 ) using utf8) txt, 36 union
select convert(binary convert(mycolumn using utf16le ) using utf8) txt, 37 union
select convert(binary convert(mycolumn using utf32 ) using utf8) txt, 38 union
select convert(binary convert(mycolumn using utf8 ) using utf8) txt, 39 union
select convert(binary convert(mycolumn using utf8mb4 ) using utf8) txt, 40 union