is this a safe way to change character set on a MySQL table to UTF-8

Question

I need to change the character set on two MySQL tables to UTF8. Initially I came up with the following query but I wanted to know if this is a safe way to change the character set on (live) tables which have foreign keys. In my dev environment I just used the "FOREIGN_KEY_CHECKS = 0" without the lock but i wasn't so concerned about integrity at the time, with the live system I am.

LOCK TABLES 
  tags WRITE,
  tags_lost WRITE;

  SET FOREIGN_KEY_CHECKS = 0;

    ALTER TABLE myDB.exampleTable CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE myDB.exampleTable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE myDB.exampleTable CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE myDB.exampleTable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

  SET FOREIGN_KEY_CHECKS = 1;    
UNLOCK TABLES;

The other option is to lock the tables, drop the foreign key, alter the char set, and then recreate the key but I was just hoping this shortcut would work just as well.

You don't want to change to UTF8, you want to change to `UTF8mb4`, [please read why that is so](http://stackoverflow.com/a/279279/3536236) — Martin, May 27 '16 at 23:15

score 0 · Answer 1 · answered May 27 '16 at 22:43

Don't change the charset, only do the ALTER ... CONVERT TO .... The latter does both the declaration and the data.

If you have already done both, check the data:

SELECT col, HEX(col) FROM exampleTable WHERE ...

and let's see if the hex is correct. You could have ended up with the dreaded "double encoding".

is this a safe way to change character set on a MySQL table to UTF-8

1 Answers1