Character sets and collations
utf8mb4 versus utf8mb3, what utf8mb4_0900_ai_ci treats as equal, why a UNIQUE key rejects a word you never inserted, and the illegal-mix-of-collations error.
After this lesson you can
- Choose utf8mb4 and explain what utf8mb3 cannot store
- Predict which strings the default collation treats as equal
- Explain why a UNIQUE index can reject a string that is not byte-identical
- Fix an illegal mix of collations in a join
A character set decides which characters a column can store and how many bytes each takes. A collation decides how two strings compare and sort. Every string column has both.
utf8mb4, not utf8
MySQL's historical utf8 is an alias for utf8mb3: at most three bytes per
character, which leaves out emoji and some rarer CJK characters (they need
four). utf8mb4 is real UTF-8 and has been the server default since 8.0,
with the default collation utf8mb4_0900_ai_ci. Old schemas still contain
utf8mb3 columns, and they refuse four-byte characters in strict mode
(the second attempt in the example further down).
What the default collation treats as equal
The suffixes describe the rules: ai is accent-insensitive, ci is
case-insensitive, and 0900 means it follows Unicode 9.0 collation
rules. Those rules also expand characters: German ß compares equal to
ss. Another property is not in the name: the _0900_ collations are
NO PAD, so a trailing space is a real character and 'apple ' is not
equal to 'apple'. The older utf8mb4_general_ci and utf8mb4_unicode_ci
are PAD SPACE and ignore trailing spaces in comparisons.
CREATE TABLE words (id INT PRIMARY KEY, w VARCHAR(20));INSERT INTO words VALUES (1, 'apple'), (2, 'APPLE'), (3, 'apple '), (4, 'café'), (5, 'cafe'), (6, 'strasse'), (7, 'straße');SELECT COLLATION(w) AS column_collation, @@collation_connection AS literal_collation, (SELECT GROUP_CONCAT(id ORDER BY id) FROM words WHERE w = 'apple') AS eq_apple, (SELECT GROUP_CONCAT(id ORDER BY id) FROM words WHERE w = 'cafe') AS eq_cafe, (SELECT GROUP_CONCAT(id ORDER BY id) FROM words WHERE LOWER(w) = LOWER('CAFE')) AS eq_lower_cafe, (SELECT GROUP_CONCAT(id ORDER BY id) FROM words WHERE w = 'strasse') AS eq_strasse, (SELECT GROUP_CONCAT(id ORDER BY id) FROM words WHERE w = 'apple' COLLATE utf8mb4_0900_as_cs) AS eq_apple_cs, CHAR_LENGTH('café') AS chars, LENGTH('café') AS bytesFROM wordsWHERE id = 1;A column compares under its own collation even when the literal on the
other side comes from a connection with a different default. Adding
COLLATE utf8mb4_0900_as_cs (accent- and case-sensitive) or comparing with
BINARY narrows the match to the exact spelling.
Equality decides uniqueness
A UNIQUE index enforces uniqueness under the column's collation. If
'Resume' is already stored, 'résumé' is a duplicate, because the
collation says the two are equal. That is usually what you want for
usernames and tags, and it is a surprise the first time it rejects a value
that looks new.
CREATE TABLE tags (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, UNIQUE KEY uq_name (name));INSERT INTO tags (name) VALUES ('Resume');CREATE TABLE old_notes (body VARCHAR(20) CHARACTER SET utf8mb3);CREATE TABLE legacy (code VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci);CREATE TABLE modern (code VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci);INSERT INTO legacy VALUES ('A1');INSERT INTO modern VALUES ('a1');-- A helper that runs a statement and records the error instead of stopping.CREATE TABLE attempts (n INT AUTO_INCREMENT PRIMARY KEY, statement TEXT, outcome TEXT);CREATE PROCEDURE try_sql(IN stmt TEXT)BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @err_no = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT; INSERT INTO attempts (statement, outcome) VALUES (stmt, CONCAT('error ', @err_no, ': ', REPLACE(@err_msg, CONCAT(DATABASE(), '.'), ''))); END; SET @sql = stmt; PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s; INSERT INTO attempts (statement, outcome) VALUES (stmt, 'ok');END;CALL try_sql('INSERT INTO tags (name) VALUES (''résumé'')');CALL try_sql('INSERT INTO old_notes VALUES (''ok 😀'')');CALL try_sql('SELECT * FROM legacy l JOIN modern m ON l.code = m.code');CALL try_sql('SELECT * FROM legacy l JOIN modern m ON l.code = m.code COLLATE utf8mb4_0900_ai_ci'); SELECT n, outcome FROM attempts ORDER BY n;The third attempt shows the error you meet when joining tables created in
different eras: two columns with different collations and the same
coercibility cannot be compared, and MySQL stops with error 1267 rather than
guess. Naming a collation on one side with COLLATE resolves it. Be aware
that converting the indexed column's side can prevent that column's index
from being used; the lasting fix is to ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci so both sides match.
Bytes are not characters
LENGTH() counts bytes and CHAR_LENGTH() counts characters. 'café'
is 4 characters and 5 bytes in utf8mb4. A VARCHAR(20) limit is in
characters; index size limits are in bytes, which is why a long utf8mb4
column can be too wide to index in full.
Common mistakes
- Declaring
utf8and expecting emoji to work. It isutf8mb3, and the insert fails with error 1366. - Wrapping both sides in
LOWER()"to ignore case". It changes nothing: the result keeps the column's collation, soLOWER(w) = LOWER('CAFE')matched exactly whatw = 'cafe'did. The real trap is lower-casing in application code, which misses the accent-only duplicates (josé/jose) that aUNIQUEindex will reject.
Try it yourself
2 visible tests · 2 hidden testsTable signups(id, email) has no unique key yet, and email uses the
server default collation utf8mb4_0900_ai_ci. Before adding
UNIQUE (email), the team wants to know which rows would be rejected if
the rows were inserted again in id order.
Return every row whose email is equal under the column's collation
to the email of some row with a smaller id. Columns: id, email,
and first_id, the smallest id it collides with. Order by id. The
first row of each colliding group is not returned.
CREATE TABLE signups (id INT PRIMARY KEY, email VARCHAR(100) NOT NULL);INSERT INTO signups VALUES (1, 'ann@example.com'), (2, 'bob@example.com'), (3, 'ANN@example.com'), (4, 'cy@example.com');Sign up to run the tests, check the hidden ones and save your progress. Sign up