0

For my databases, I used utf8mb4_unicode_ci with utf8mb4 character set as a default. This was a mistake and the folks who are using the databases I created are complaining about the collation. I need to convert it to utf8mb4_general_ci. Am I able to get away with just changing the DB using an alter statement such as:

ALTER DATABASE `#{database}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Or, will I need to change each individual table and deal with columns even though the charset is the same between the two collations? I can't seem to find definitive answers on this... I'm using MySQL 5.7.2x .

AndrewD
  • 258
  • 4
  • 17

1 Answers1

2

utf8mb4_unicode_520_ci is better than either of the collations you mentioned.

Why are they complaining? Perhaps JOINs are failing to use indexes. I would argue with them that the old tables should be changed.

ALTER DATABASE only sets up defaults for future tables. It will not do what you need.

ALTER TABLE ... CONVERT TO ... for each table is needed. See http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes for a similar ALTER. It provides a way to automatically generate all the ALTERs.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 1
    If no one is connected to the DB, does this look safe? - `SET FOREIGN_KEY_CHECKS = 0` ,`ALTER TABLE #{table} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci` , `SET FOREIGN_KEY_CHECKS = 1` for each of the tables within the DB that are `BASE TABLES` ? – AndrewD Jul 31 '20 at 05:03