Emoji turning into ????, Chinese or accented characters showing as é garbage, “Incorrect string value” errors, or “Illegal mix of collations” — these all come down to character sets. In MySQL and MariaDB, the fix is to use utf8mb4 consistently: server, databases, tables, columns and connections.

Why utf8mb4?

MySQL’s historical utf8 (now called utf8mb3) stores at most 3 bytes per character. Real UTF-8 needs up to 4 bytes for emoji, many CJK characters and symbols. Inserting them into a utf8mb3 column fails (“Incorrect string value: ‘\xF0\x9F\x98\x80’”) or truncates the text.

utf8mb4 is full UTF-8. Use it for everything. MySQL 8 already defaults to utf8mb4; older installs and imported databases often don’t.

Collations

A collation defines how strings compare and sort (case, accents):

Collation Notes
utf8mb4_0900_ai_ci MySQL 8 default; Unicode 9.0 rules, accent- and case-insensitive. Not available in MariaDB
utf8mb4_unicode_ci Works on MySQL and MariaDB; good general choice for portability
utf8mb4_unicode_520_ci Newer Unicode rules, MySQL/MariaDB
utf8mb4_uca1400_ai_ci MariaDB 11.x default family
utf8mb4_bin Exact byte comparison (case-sensitive)
utf8mb4_general_ci Older, faster but less correct sorting

_ci = case-insensitive, _ai = accent-insensitive, _cs/_as = sensitive.

Check what you have

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata;

SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = 'shop';

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'shop' AND character_set_name IS NOT NULL;

Server defaults

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

Restart. This affects new databases and tables; existing ones keep their settings.

Connection character set

Even with utf8mb4 tables, data gets garbled if the connection uses another charset. Applications should set it explicitly:

  • PHP PDO: mysql:host=…;dbname=…;charset=utf8mb4
  • WordPress: define('DB_CHARSET', 'utf8mb4');
  • Laravel: 'charset' => 'utf8mb4' in config/database.php (default)
  • Node mysql2: charset: 'utf8mb4'
  • Python: charset='utf8mb4'

Convert existing databases and tables

Back up first (mysqldump). Then:

ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CONVERT TO rewrites the table and its text columns. Generate statements for all tables:

SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.tables WHERE table_schema = 'shop' AND table_type = 'BASE TABLE';

Large tables are rebuilt — schedule it in a quiet period (or use online schema change tools like gh-ost/pt-online-schema-change for big production tables).

Index length limit

With 4 bytes per character, an indexed VARCHAR(255) needs up to 1020 bytes. Modern InnoDB (DYNAMIC row format, default since MySQL 5.7) allows index keys up to 3072 bytes, so this is fine. On very old setups you’ll see “Specified key was too long; max key length is 767 bytes” — upgrade the row format or index a prefix (VARCHAR(191)), which is why some apps use 191.

Fixing “mojibake” (double-encoded text)

If text shows as é instead of é, UTF-8 bytes were stored through a latin1 connection and are now double-encoded. Converting the table won’t fix it — the stored bytes are wrong. A common repair path is to dump with the original (wrong) charset and re-import declaring UTF-8:

mysqldump --default-character-set=latin1 --skip-set-charset shop > shop.sql
sed -i 's/CHARSET=latin1/CHARSET=utf8mb4/g' shop.sql
mysql --default-character-set=utf8mb4 shop_fixed < shop.sql

Test on a copy first — mixed situations need case-by-case handling.

“Illegal mix of collations”

Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

Two columns (or a column and the connection) in one comparison use different collations. Fix by converting tables to the same collation, or set the connection collation to match (SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci). Common after importing MySQL 8 dumps into older schemas or mixing tables created at different times.

Moving between MySQL and MariaDB

MySQL 8’s utf8mb4_0900_ai_ci doesn’t exist in MariaDB. Replace it before importing — see importing large SQL files and MariaDB vs MySQL.

  • MySQL
  • MariaDB
  • Unicode