Converting character sets and foreign key constaints

I'm trying to convert the character set and collation of my MySQL database and all containing tables from utf8/utf8_unicode_ci to utf8mb4/utf8mb4_unicode_ci.

I will refer to my database name as: MyDB

I will refer to my table(s) as: MyTable1 [, MyTable2, MyTable3, etc]

I have set the database character set and collation using:

ALTER DATABASE MyDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Then I used this SQL script to generate all of my table commands:

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ",
    "ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ") 
    AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'MyDB';

This SQL generated a table with these contents:

ALTER TABLE MyDB.MyTable1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
... etc

Now my table has a lot of foreign key constraints and if I directly execute this query I get foreign key constraint errors because of incompatible character sets half way through the modification process:

#1025 - Error on rename of './MyDB/#sql-bba_3975' to './MyDB/MyTable1' (errno: 150)

So I figured I could just set FOREIGN_KEY_CHECKS to 0 before I run my query. I remember doing this before when I had to convert my database from latin1 to utf8.

```
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE MyDB.MyTable1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
.

Sort the ALTERs so that the FK checks will stop complaining. Do a child before a parent (or is it the other way??).

If you have any circular references, then, yeah, remove and re-apply the FKs.

(You have pointed out yet another reason to eschew FKs.)

While you are at it, consider utf8mb4_unicode_520_ci; it comes from a newer Unicode standard.

StackExchange Database Administrators Q#154180, answer score: 3