Character Sets and Collations
What the domain tests
Character Sets and Collations tests how MySQL stores and compares strings. Questions cover utf8mb4, collation suffixes, connection character sets, introducers, and the effect of collation on comparisons and indexes.
Core rules
- A character set is the encoding; a collation is the comparison and sort order. MySQL 8.0 defaults to utf8mb4 and the default collation utf8mb4_0900_ai_ci.
- utf8 is an alias for utf8mb3, which uses up to three bytes and cannot represent four-byte characters. Use utf8mb4 for full Unicode, including emoji.
- Collation names ending _ci are case-insensitive, _cs case-sensitive, and _bin compares binary values. The 0900 collations are based on Unicode 9.0.0.
- SET NAMES utf8mb4 sets character_set_client, character_set_connection, and character_set_results for the session. Character set conversion happens between client, connection, and column.
- Introducers such as _utf8mb4'text' and COLLATE clauses force a character set or collation for a literal or expression.
- Indexes use the column collation. Comparing columns with different collations can prevent index use and raise 'Illegal mix of collations'.
- The maximum index key length depends on storage engine and character set. utf8mb4 uses up to four bytes per character.
Worked example
With a utf8mb4_0900_ai_ci column, WHERE name = 'ana' matches 'Ana' and 'ANA' because the collation is case-insensitive and accent-insensitive. If the column is declared COLLATE utf8mb4_0900_as_cs, the same predicate matches only the exact case and accent. The reasoning is that the collation, not the character set alone, determines equality.
Traps
- Believing utf8 stores all Unicode; it is utf8mb3.
- Mixing a utf8mb4 literal with a utf8mb3 column and expecting no conversion.
- Assuming a case-sensitive comparison without checking the collation suffix.
- Setting only character_set_client and not the connection and results sets.
- Creating a long indexed VARCHAR under utf8mb4 and exceeding the key-length limit.