Text is stored as bytes. A character set says how characters become bytes. A collation says how characters are compared and sorted.
utf8mb4 is the right choice
utf8mb4 is MySQL’s full UTF-8. It stores every Unicode character, including emoji and many scripts, using 1 to 4 bytes per character.
SELECT CHAR_LENGTH('a') AS chars_a, LENGTH('a') AS bytes_a,
CHAR_LENGTH('é') AS chars_e, LENGTH('é') AS bytes_e,
CHAR_LENGTH(CONVERT(0xF09F9880 USING utf8mb4)) AS chars_emoji, LENGTH(CONVERT(0xF09F9880 USING utf8mb4)) AS bytes_emoji;+---------+---------+---------+---------+-------------+-------------+
| chars_a | bytes_a | chars_e | bytes_e | chars_emoji | bytes_emoji |
+---------+---------+---------+---------+-------------+-------------+
| 1 | 1 | 1 | 2 | 1 | 4 |
+---------+---------+---------+---------+-------------+-------------+
1 row in setAn ordinary letter takes one byte, an accented letter two and an emoji four. CHAR_LENGTH counts characters, LENGTH counts bytes.
The utf8 trap
In MySQL, the name utf8 is an alias for utf8mb3, an old three-byte version that cannot store emoji or many rare characters. It is deprecated. Always write utf8mb4.
CREATE DATABASE old_style CHARACTER SET utf8;
SHOW WARNINGS;
SELECT default_character_set_name FROM information_schema.schemata WHERE schema_name = 'old_style';+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
+----------------------------+
| DEFAULT_CHARACTER_SET_NAME |
+----------------------------+
| utf8mb3 |
+----------------------------+
1 row in setMySQL accepted utf8 but stored utf8mb3 and warned that the name is deprecated. In a future version the name utf8 may mean utf8mb4. Do not rely on either. Say utf8mb4.
Collations
A collation is a set of comparison rules for a character set. Its name reads like a description:
| Name part | Meaning |
|---|---|
utf8mb4 | The character set |
0900 | Based on the Unicode 9.0 algorithm (faster and more accurate) |
ai / as | Accent-insensitive / accent-sensitive |
ci / cs | Case-insensitive / case-sensitive |
bin | Compare raw bytes |
SELECT 'a' = 'A' COLLATE utf8mb4_0900_ai_ci AS ai_ci,
'a' = 'A' COLLATE utf8mb4_0900_as_cs AS as_cs,
'e' = 'é' COLLATE utf8mb4_0900_ai_ci AS accents_ignored,
'e' = 'é' COLLATE utf8mb4_0900_as_ci AS accents_matter,
'a' = 'A' COLLATE utf8mb4_bin AS bin_compare;+-------+-------+-----------------+----------------+-------------+
| ai_ci | as_cs | accents_ignored | accents_matter | bin_compare |
+-------+-------+-----------------+----------------+-------------+
| 1 | 0 | 1 | 0 | 0 |
+-------+-------+-----------------+----------------+-------------+
1 row in setWhich collation should you pick?
- Default:
utf8mb4_0900_ai_ci(MySQL 8). Good for most applications. - Exact matching (case-sensitive):
utf8mb4_0900_as_cs, orutf8mb4_binfor codes, tokens or hashes. - Compatibility with MariaDB or MySQL 5.7:
utf8mb4_unicode_ciorutf8mb4_general_ci. These cannot always be mixed with0900collations in one query.
Where you set them
They can be set at four levels, each overriding the one above: server, database, table, column.
CREATE TABLE names (
id INT AUTO_INCREMENT PRIMARY KEY,
display_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
code VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
INSERT INTO names (display_name, code) VALUES ('Zoë', 'Ab1');
SELECT display_name FROM names WHERE display_name = 'zoe';
SELECT code FROM names WHERE code = 'ab1';+--------------+
| display_name |
+--------------+
| Zoë |
+--------------+
1 row in set
Empty setThe name search ignores case and accents and finds Zoë. The code search is case-sensitive and finds nothing.
Connection character set
The connection also has a character set. If the application connects with the wrong one, characters turn into ? or garbage (é). Set it in the connection (charset=utf8mb4 in a PDO DSN, for example) or with SET NAMES utf8mb4.
SET NAMES utf8mb4;
SELECT @@character_set_client AS client, @@character_set_connection AS connection, @@character_set_results AS results;+---------+------------+---------+
| client | connection | results |
+---------+------------+---------+
| utf8mb4 | utf8mb4 | utf8mb4 |
+---------+------------+---------+
1 row in setConverting an old table
CREATE TABLE legacy_notes (id INT, body VARCHAR(100)) DEFAULT CHARSET = latin1;
INSERT INTO legacy_notes VALUES (1, 'cafe');
ALTER TABLE legacy_notes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SELECT table_collation FROM information_schema.tables WHERE table_schema = 'learn' AND table_name = 'legacy_notes';+--------------------+
| TABLE_COLLATION |
+--------------------+
| utf8mb4_0900_ai_ci |
+--------------------+
1 row in setCONVERT TO changes the table default and every text column, and rewrites the data. Always back up and test on a copy. A VARCHAR(255) index on utf8mb4 fits in InnoDB’s key length limit (3072 bytes), but very long indexed columns may not.
Mixed collations
utf8mb4_0900_ai_ci and utf8mb4_unicode_ci) gives Illegal mix of collations. Use one collation consistently across a project.