FAQ Databases & MySQL

How to change a database character set or collation

Changing the database default does not touch existing tables, which is the part that catches people out.

iFastHosting Support Team Updated 26 August 2026 5 min read Intermediate

The character set controls which characters a database can store, and the collation controls how it sorts and compares them. The two are usually changed together, most often when moving an older database to utf8mb4 so it can store emoji and the full range of Unicode characters properly. It is a change worth making carefully rather than casually, because it can touch every table in the database.

Back up before changing anything

Converting a character set rewrites the underlying data for every column affected. It is a safe, well-understood operation on a healthy database, but it should never be run without a recent backup in hand first. See exporting a database for a quick export before you start.

Changing it in phpMyAdmin

Select the database, open the Operations tab, and look for the collation setting near the top of the page. Choosing a new collation there and saving changes the database's default, which new tables will use going forward. To convert existing tables, select them from the database's table list, choose "Change collation" from the actions available, and pick the same collation there.

Changing it with SQL directly

To change the database's own default for anything created in it from now on:

ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

That statement alone does not touch a single row in any existing table. To convert an existing table, including every column in it, use:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Run this once for every table in the database that needs converting. On a database with many tables, exporting the schema, adjusting the statements in a text editor, and running them as a batch is usually quicker than doing each one by hand in phpMyAdmin.

Why the database-level change is not enough on its own

This is the detail that catches people out: ALTER DATABASE only sets the default for anything created afterwards. Every table that already existed keeps whatever character set and collation it was created with, regardless of what the database default is changed to, until you convert each one explicitly with its own ALTER TABLE statement. A database that reports itself as utf8mb4 while its tables are still on the old character set will keep failing to store the exact characters you were trying to fix.

Choosing a collation

For most sites built around modern content, utf8mb4_unicode_ci is the sensible default: it sorts and compares text using rules that match how people actually expect alphabetical ordering to work across different languages. utf8mb4_general_ci is a simpler, slightly faster comparison that ignores some of those linguistic rules, which rarely matters for a typical website and is not worth choosing over accuracy unless you have measured a specific reason to. Whichever you pick, use the same collation consistently across every table in the same database — mixing collations on tables that get compared or joined together is a common source of confusing errors.

After converting

Check that your application's own database connection is set to use the new character set too, since a mismatch between the connection and the actual table encoding can still produce garbled characters even after the conversion above. Most connection libraries default to a modern character set automatically, but if you are connecting manually, setting it explicitly avoids any ambiguity:

SET NAMES utf8mb4;

Load the site afterwards and check pages that display any text with accents, symbols or emoji, since that is where a leftover mismatch shows up first. If anything still displays incorrectly after converting every table, contact us with the database name and we can check the encoding directly.