How to Fix MySQL Character Set Issues and Enable Full Unicode Support

Why MySQL character set matters

If you see Chinese text turning into question marks or emojis showing as boxes, the root cause is usually an incorrect MySQL character set.

Step 1: Diagnose the current character set

Check the database, table, and global settings:

SHOW CREATE DATABASE your_db_name;

SHOW CREATE TABLE users;

SHOW VARIABLES LIKE 'character_set%';

If the output shows latin1 or utf8 (which is actually utf8mb3), you have found the culprit. Remember that MySQL's utf8 does not support emoji; you must use utf8mb4.

Step 2: Modify the character set

1. Change the default character set of the database

ALTER DATABASE your_db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Note: this only affects newly created tables.

2. Convert existing tables (the key step)

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This command automatically converts all columns in the table to the new charset.

3. (Optional) Change a specific column

ALTER TABLE users MODIFY username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Remember to specify the column type, otherwise the statement will fail.

Step 3: Make the change permanent in the configuration file

Edit my.cnf or my.ini and add:

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake  # force unified charset

Restart MySQL; new databases and tables will default to utf8mb4.

Choosing the right collation

utf8mb4_unicode_ci

– recommended for multilingual sorting. utf8mb4_general_ci – slightly faster but less accurate. utf8mb4_bin – case‑sensitive, suitable for passwords or other sensitive fields.

Common pitfalls to avoid

Always back up your data before making structural changes.

Ensure client connections specify charset=utf8mb4 (PHP, Java, Python, etc.).

Remember that ALTER ... DEFAULT CHARSET only changes defaults; you need CONVERT TO to actually transform existing data.

MySQL version must be >= 5.5.3 to support utf8mb4.

Because utf8mb4 uses more bytes, avoid VARCHAR(255) indexes; use VARCHAR(191) instead.

Final checklist

Backup the database.

Run ALTER DATABASE ....

Run ALTER TABLE ... CONVERT TO ... for each table.

Update my.cnf with utf8mb4 settings.

Restart MySQL.

Verify with SHOW CREATE TABLE that the changes took effect.

In short, utf8 is limited; utf8mb4 is the proper solution for full Unicode support.