DB-유

Understanding MySQL Character Sets: utf8 vs utf8mb4

· mysql, charset, unicode

Being Korean, I run into character set trouble more often than most people. Every few months an application inserts an emoji or a rare hanja and the row comes back with a question mark, or the insert dies with Incorrect string value. The cause is almost always the same: the table uses MySQL's utf8, which is not really UTF-8.

In MySQL, utf8 is an alias for utf8mb3. It stores at most three bytes per character, so it only covers the Basic Multilingual Plane. All emoji and many supplementary CJK ideographs need four bytes, which means utf8mb4 (available since 5.5.3; I'd use 5.7, where the index length problem below mostly disappears). Take a backup before converting anything.

Find out what you have

Character sets are set at four levels: server, database, table and column. Check all of them:

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_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME
FROM information_schema.COLUMNS
WHERE CHARACTER_SET_NAME IS NOT NULL AND CHARACTER_SET_NAME <> 'utf8mb4'
  AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys');

On a stock MySQL 5.7 the server default is latin1 with latin1_swedish_ci, so a database created without an explicit CHARACTER SET clause is latin1.

Server defaults

Edit /etc/my.cnf (CentOS) or /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu) and add:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
skip-character-set-client-handshake

[client]
default-character-set = utf8mb4

init_connect runs for non-SUPER users on connect and is a safety net for drivers that do not send their own SET NAMES. skip-character-set-client-handshake forces the server charset regardless of what the client announces; use it only if you control all clients. Restart the service afterwards.

On collation, utf8mb4_general_ci is faster but sorts some characters incorrectly. I stick to utf8mb4_unicode_ci because it follows the Unicode Collation Algorithm and I would rather have correct sorting than a tiny speedup.

Converting databases and tables

Change the default for new tables first:

ALTER DATABASE shop CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Then convert each table. CONVERT TO rewrites every character column and re-encodes the data:

ALTER TABLE shop.customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

To generate the statements for a whole schema:

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

Each ALTER copies the table, so on large tables do it during a quiet period or with pt-online-schema-change.

Watch out for one side effect: CONVERT TO may change column types. A TEXT column in utf8 that would not fit its data in utf8mb4 gets promoted to MEDIUMTEXT, and VARCHAR(N) stays N characters but now reserves 4N bytes in row-size calculations.

The index length limit

InnoDB limits index key prefixes to 767 bytes with the COMPACT and REDUNDANT row formats. A VARCHAR(255) in utf8 is 765 bytes and fits; in utf8mb4 it is 1020 bytes and does not. The error looks like this:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

On MySQL 5.7 the defaults (innodb_file_format=Barracuda, innodb_large_prefix=ON, ROW_FORMAT=DYNAMIC) raise the limit to 3072 bytes, so this rarely happens on new tables. Older tables created under 5.5/5.6 may still be COMPACT. Fix them per table:

ALTER TABLE shop.customers ROW_FORMAT=DYNAMIC;

On MySQL 5.6, also set these in my.cnf and restart:

innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix = 1

Alternatively shorten indexed columns to VARCHAR(191) (191 x 4 = 764 bytes), which is what WordPress and Laravel did for exactly this reason.

Prove it with a four-byte character

Insert a four-byte character and read it back:

SET NAMES utf8mb4;
INSERT INTO shop.customers (name) VALUES (CONVERT(UNHEX('546573742020F0A09C8E') USING utf8mb4));
SELECT name, HEX(name), CHAR_LENGTH(name), LENGTH(name) FROM shop.customers WHERE name LIKE 'Test%';

The last four bytes are the CJK Extension B ideograph U+2070E, which does not exist in utf8mb3. CHAR_LENGTH should be 7 and LENGTH 10, and the hex should end in F0A09C8E. If you see 3F (a question mark) or the insert fails, the column or the connection charset is still wrong.

Gotchas

  • ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'name' at row 1: the column or the connection is utf8, not utf8mb4. Check both SHOW CREATE TABLE and SHOW VARIABLES LIKE 'character_set_connection'.
  • Data shows as ???? after conversion: it was already lost when it was inserted through a utf8 connection; conversion cannot recover it.
  • Mojibake like é instead of é: double encoding. The data is UTF-8 bytes stored in a latin1 column. The fix is ALTER TABLE t MODIFY col BLOB; ALTER TABLE t MODIFY col VARCHAR(255) CHARACTER SET utf8mb4; to reinterpret the bytes without conversion.
  • JDBC clients: characterEncoding=UTF-8 in the connection URL is correct; Connector/J maps it to utf8mb4 when the server default is utf8mb4.

New schemas: utf8mb4 everywhere, no exceptions. Old ones: convert them before the first ticket about a missing emoji lands on your desk.