Fix database character set and collation
If text shows up garbled after an import or migration - accented letters turning into stray symbols,
or emoji disappearing - the database is usually in an older character set such as latin1 or
three-byte utf8. Converting it to utf8mb4 (full Unicode, including emoji) fixes this.
Warning
Run this against a copy or a fresh backup first, and during a
quiet window: the ALTER TABLE statements rewrite every table and lock it while they run.
Convert a database and all its tables
Connect over SSH and convert in two parts - the database default, then each table. This small script does both for one database:
DB="prod_db"
CHARSET="utf8mb4"
COLLATION="utf8mb4_unicode_ci"
mysql -e "ALTER DATABASE \`$DB\` CHARACTER SET $CHARSET COLLATE $COLLATION;"
mysql -N -s -e "SHOW TABLES" "$DB" | while read TABLE; do
echo "Converting $DB.$TABLE"
mysql "$DB" -e "ALTER TABLE \`$TABLE\` CONVERT TO CHARACTER SET $CHARSET COLLATE $COLLATION;"
doneutf8mb4_unicode_ci is a good general-purpose collation. Some applications expect a specific one
(for example utf8mb4_general_ci or utf8mb4_0900_ai_ci) - match what your application's
documentation asks for.
Verify
Check that the database reports the new character set:
mysql -e "SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name='prod_db';"Then reload the site and confirm the previously garbled text now displays correctly.