Charset and collation on a MySQL database

Charset (character set) determines which characters the database can store, and collation determines how the characters are compared and sorted. Using the wrong character set is the most common cause of Danish letters (æ, ø, å) or emojis appearing as question marks or mojibake (e.g. æ instead of æ).

¶Recommended values

Use utf8mb4 as the character set and utf8mb4_unicode_ci as the collation. utf8mb4 supports all Unicode characters, including emojis.

¶Method 1: phpMyAdmin

Change the default for the whole database (affects only new tables):

1. Log in to phpMyAdmin and select the database on the left‑hand side.
2. Click the Operations tab.
3. Find the Collation section.
4. Choose utf8mb4_unicode_ci from the dropdown menu.
5. Click Go.

Change an existing table (including all its columns):

1. Select the table on the left‑hand side.
2. Click the Operations tab.
3. Under Table options choose the new collation.
4. Tick Also change collation for all columns.
5. Click Go.

¶Method 2: SQL

Change the database default:

ALTER DATABASE mindatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Change a table and all its columns:

ALTER TABLE mintabel CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

In phpMyAdmin you can change the character set per column: select the table, click Structure, click Change next to the column, and choose a new collation in the column definition.

¶Convert all tables at once

Generate the ALTER statements automatically:

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mindatabase' AND TABLE_TYPE = 'BASE TABLE';

Run the result in phpMyAdmin under the SQL tab or via the command line.

¶Connection character set (SET NAMES)

Database character set and connection character set are two different things. Even if your tables are correctly set to utf8mb4, characters can become corrupted in transit if the connection between your application and MySQL uses a different character set.

SET NAMES utf8mb4 sets three session variables at once:

• character_set_client — what the server expects to receive from the client
• character_set_connection — what the server uses internally while processing queries
• character_set_results — what the server sends back to the client

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

Best practice: use the driver’s built‑in character set option instead of sending SET NAMES manually. This ensures that functions such as mysqli_real_escape_string() and PDO::quote() also use the correct character set – otherwise, in rare cases, you could open yourself to SQL injection.