2

I noticed that my MODX database still uses latin1 character set in the database and in its tables. I would like to convert them to utf8mb4 and update collations accordingly.

Not totally sure how I should do this. Is this correct?

  • I alter every table to use utf8mb4 and utf8_unicode_ci?
  • I update the default character set and collation of the database.

Are indexes updated automatically? Is there something else I should be aware of?

A bonus question: what would be the most suitable latest utf8_unicode collation? Western languages should work.

Niko Suominen
  • 97
  • 9

1 Answers1

4

Changing the default character sets of a table or a schema does not change the data in the column itself, it only changes the default to apply the next time you add a table or add a column to a table.

To convert current data, alter one table at a time:

ALTER TABLE <name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

The collation utf8mb4_0900_ai_ci is faster than earlier collations (at least according to the documentation), and it's the most current and accurate. This collation requires MySQL 8.0.

The most current collation in MySQL 5.7 is utf8_unicode_520_ci.

A table-conversion like this rebuilds the indexes, so there's nothing else you need to do.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828