Converting a MySQL database to UTF-8 (utf8mb4)

utf8mb4 is the character set modern apps use. It stores every language and symbol, including emoji. Older databases often use latin1 or the older utf8 (also called utf8mb3), which can’t store emoji and some characters. You may see:

• Question marks (????) in place of emoji or some characters.
• Errors such as “Incorrect string value” when saving content.
• WordPress warnings about the database character set.

This guide converts a database to utf8mb4 with the utf8mb4_unicode_ci collation.

Caution

Back up the database first. In cPanel, export it with phpMyAdmin or take a backup with JetBackup. See Restoring files, email and databases using JetBackup.

Check the current character set

1. In cPanel, open phpMyAdmin and click your database on the left.
2. The Collation column shows each table’s collation. Anything starting with latin1_ or utf8_ (not utf8mb4_) needs converting.

Convert in phpMyAdmin

1. Click your database on the left, then the Operations tab.
2. Under Collation, choose utf8mb4_unicode_ci.
3. Tick Change all tables collations and Change all tables columns collations.
4. Click Go.

This changes the database default and converts every table and text column.

Convert with SQL

To convert tables one at a time, click your database, open the SQL tab and run, for each table:

ALTER DATABASE your_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

To create the commands for every table at once, run this, then copy the results into the SQL tab and run them:

SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.tables
WHERE table_schema = 'your_database' AND table_type = 'BASE TABLE';

After converting

• Tell your app to use utf8mb4. In WordPress, check wp-config.php has define( 'DB_CHARSET', 'utf8mb4' );. In PHP code, connect with charset=utf8mb4 (PDO) or $mysqli->set_charset('utf8mb4'). See Specifying the MySQL character set in PHP.
• Check your website. Look at pages with accents, other languages and symbols.

If text already looks wrong

If text already showed odd characters such as é instead of é before you converted, the data was saved with the wrong character set (double-encoded). Converting won’t fix this by itself and can make it worse. Restore your backup and contact support, and we’ll help you fix it.

Related articles

• Changing the MySQL collation settings in phpMyAdmin
• Database limits and versions