Converting a database to UTF-8

A database stores text in a character set and a collation, and mixing them up is what turns an é or an emoji into a string of mojibake. The common legacy case is a database created as latin1 that holds UTF-8 bytes, or a database genuinely in one charset being served to pages that expect another. Converting the schema to UTF-8 is routine, but it is easy to do wrong, because how the bytes were written determines whether you can simply convert or must re-interpret them first.

When conversion is needed

You should consider a conversion when:

• pages show é, ’ or other garbled characters that a UTF-8 page then displays wrongly,
• you are moving between hosts and the target expects a different default charset than the source,
• you need to support text a charset cannot store, such as emoji in latin1.

The safest outcome is one consistent standard: UTF-8 storage with a matching collation and a page and connection that both negotiate UTF-8.

Diagnose how the bytes were written

There are two different situations and they need different commands:

Genuine UTF-8 content. If the data is already correct UTF-8 bytes, a simple conversion of the table to utf8mb4 changes only the metadata and leaves the text intact.

UTF-8 bytes stored in a latin1 column. If text was written as UTF-8 but the column was latin1, a naive convert mislabels the bytes. The fix is the re-interpret sequence: convert the column to latin1's binary interpretation, then to the target, so the stored bytes are reinterpreted as UTF-8 rather than altered.

ALTER TABLE `page` CONVERT TO CHARACTER SET latin1;
ALTER TABLE `page` CONVERT TO CHARACTER SET utf8mb4;

The order matters, and the same idea generalises: touch the control sequence for how bytes are interpreted before you ask for a real conversion. A backup and a test on a copy are non-negotiable; the cheap truth is that a mis-applied conversion is invisible until a character renders wrong.

Convert the schema

For a real conversion where the stored bytes match the column's charset:

1. Take a backup of the database and confirm you can restore it.
2. Set the connection charset so server, client and table agree:

   SET NAMES utf8mb4;

1. Convert each table to the target charset and collation:

   ALTER TABLE `page` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

1. Update the table default so new columns inherit the same charset:

   ALTER TABLE `page` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

1. Apply the same to the database default so newly created tables inherit it.

For MySQL and MariaDB, utf8mb4 is the correct choice over utf8, because the three-byte utf8 cannot store four-byte emoji. Set the connection via the application or SET NAMES so code and database both speak UTF-8, otherwise pages can still show mojibake even with a converted schema.

After conversion, check indexes that use text columns. Moving a column from latin1 to utf8mb4 stores each character in up to four bytes instead of one, so an index defined on column title (100) that previously consumed 100 bytes now needs up to 400. On InnoDB the classic limit is 3072 bytes per index (767 bytes on smaller row formats), so a wide index can exceed it and fail. If an ALTER TABLE rejects an index, shorten the indexed prefix or choose a 255-byte-safe limit relevant to your engine, then re-run.

Verify before you call it done

• Query a known row that contained accents or emoji and confirm it renders correctly in a UTF-8 client.
• Open the live page in a browser and check the accented characters, not just the generic text.
• Confirm the connection and any restore scripts run SET NAMES utf8mb4 or equivalent, so a later import does not undo the state.

The search and replace database article covers the related task of updating URLs inside the data when a migration moves a site, and the environment portability page shows how connection settings like charset travel between hosts without silent drift.

When the data is already unreadable

If corruption is already stored (the bytes were mangled, not just mislabelled), a conversion cannot recover what was discarded. Use the original backup that predates the corruption, re-import the source, and apply the correct charset on the clean data. The environment portability and host migration checklist articles frame the move so a charset mismatch is caught on staging rather than appearing as mojibake in production.