Alter table convert to using latin1

Question

I need to do what I do here for a whole table:

UPDATE users SET name = CONVERT(CAST(convert(name using  latin1) as BINARY) USING utf8);

How do I do this with the ALTER TABLE query ?

jose_bacoy · Answer 1 · 2018-04-13T13:53:08.970

0

Let's say columnA is defined as VARCHAR(25) CHARACTER SET latin1 then you can change it to utf8 so that it can have different values from different languages using below:

  ALTER TABLE yourTable MODIFY columnA VARCHAR(25) CHARACTER SET utf8;

edited Apr 13 '18 at 13:53

answered Apr 13 '18 at 13:27

jose_bacoy

12,227
1
20
38

Are you looking at my CAST - cause i'm converting two times ? – Troels Johannesen Apr 13 '18 at 13:44
when you convert it to BINARY it will have 0x00 at the end so it does not look good; Btw, how is your column `name` defined? I assumed it to be CHAR; isn't? – jose_bacoy Apr 13 '18 at 13:49
Char is varchar for name. But i have the issue throughout the table. – Troels Johannesen Apr 13 '18 at 13:50
if it is varchar then the conversion is much trivial. I updated my answer – jose_bacoy Apr 13 '18 at 13:53

score 0 · Answer 2 · answered Apr 17 '18 at 23:07

First of all, make sure that is the correct fix.

If I am not mistaken, you are trying to undo "double-encoding"?

See this for symptoms: Trouble with UTF-8 characters; what I see is not what I stored

See this for where the fix applies: http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases
Namely

CHARACTER SET utf8mb4 with double-encoding:

UPDATE tbl SET col = CONVERT(BINARY(CONVERT(col USING latin1)) USING utf8mb4);

Dump the table, load it somewhere else, run the conversion, test the results, especially getting SELECT col, HEX(col) ...

Alter table convert to using latin1

2 Answers2