Change the Collation of a MySQL Database in phpMyAdmin

What is collation?

MySQL is a database that stores text. Two rules decide how it treats text. The first is the character set. It tells which letters and symbols can be saved, such as emoji or accents. utf8mb4 is a popular, modern choice.

The second rule is collation. It tells how text is sorted and compared. For example, it decides if "a" and "A" count as the same. Collation names look like utf8mb4_unicode_ci. The ci part means "case insensitive".

You may need to change it when special letters look broken, like question marks. You may also need it when an app asks for a certain one.

Warning: Changing collation can change your data. Export a backup first. Use the Export tab in phpMyAdmin.

Change the default for a database

1. Log in to your control panel and open phpMyAdmin.
2. Click your database on the left.
3. Click the Operations tab.
4. Find the box named Collation.
5. Pick the new collation from the list.
6. Click Go.

This only changes the default for new tables. It does not change tables you already have.

Change one table

1. Click the table name on the left.
2. Click the Operations tab.
3. Look under Table options for Collation.
4. Pick the new one.
5. Tick Change all column collations.
6. Click Go.

Change with a command

Click the SQL tab and run this. Replace the table name with yours.

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This changes the table and all its text columns to the new setting.

Check the result

Open the Structure tab of a table. The Collation column shows the setting for each field.

Quick recap

• Collation sets how text is sorted and compared.
• Use the Operations tab for a database or a table.
• Tick the option to change all columns for a table.
• Keep one collation across related tables.
• Back up first.