25. Collations

A collation determines how two texts are compared: equality, ordering, GROUP BY, DISTINCT, PRIMARY / UNIQUE keys, joins, LIKE, partitions and full-text search. Miraj offers three, chosen per column, per table or per expression, following the rules of the reference. All text is stored as UTF-8 (utf8mb4).

25.1 The three collations#

All three are PAD SPACE: trailing spaces (U+0020 only) are ignored by =, ordering, GROUP BY, DISTINCT, keys and joins ('a' = 'a ' is 1, even in binary; a trailing tab counts). LIKE never pads with spaces ('a ' LIKE 'a' is false).

The utf8_… aliases are read as utf8mb3_…. CHARACTER SET latin1 is accepted for a column but the text is still stored as utf8mb4. The binary type (BINARY, VARBINARY, BLOB, CAST(… AS BINARY)) is always compared byte by byte, without PAD.

A JSON column is always utf8mb4_bin (as on the reference): two documents that differ only in case are different, and JSON_UNQUOTE(JSON_EXTRACT(doc, '$.a')) = 'x' does not find "X". SHOW CREATE TABLE writes nothing for it.

25.2 Choosing the collation#

Column and table#

CREATE TABLE liasse (
    code   VARCHAR(10) COLLATE utf8mb4_bin PRIMARY KEY,   -- 'A1' and 'a1' are two codes
    nom    VARCHAR(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    notes  TEXT                                           -- table default
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

• [CHARACTER SET set] COLLATE name after the type of a text column (CHAR, VARCHAR, *TEXT, ENUM, SET); CHARACTER SET set alone gives the default collation of the set, the general collation.
• The BINARY attribute of a text column (VARCHAR(10) BINARY) is equivalent to COLLATE utf8mb4_bin; BINARY COLLATE with any other name: error 1302.
• [DEFAULT] CHARSET = set, [DEFAULT] COLLATE = name as a table option: default for text columns written without a collation.
• CREATE TABLE … LIKE copies columns and default; CREATE TABLE … AS SELECT and view columns take the collation of the source column or expression (CONCAT(col_bin, 'x') stays binary).

ALTER TABLE#

• MODIFY / CHANGE / ADD of a column: the COLLATE written, otherwise the table default; RENAME COLUMN keeps the collation.
• [DEFAULT] COLLATE = name changes the table default without touching existing columns.
• CONVERT TO CHARACTER SET utf8mb4 [COLLATE name] converts all text columns (except JSON).
• Changing the collation of a key column rechecks the key: values that have become equal ('A1' and 'a1' going from binary to general, 'ß' and 'ss' going to Unicode) give 1062 and the table is left unchanged.
• Changing the collation of a partitioning column is refused (1235).

Expression#

expr COLLATE name imposes a collation on a text expression:

SELECT 'a' COLLATE utf8mb4_bin = 'A';                    -- 0
SELECT * FROM clients WHERE nom = 'strasse' COLLATE utf8mb4_unicode_ci;   -- finds « Straße »
SELECT code FROM liasse ORDER BY code COLLATE utf8mb4_general_ci;

The name must be a utf8mb4 collation ('a' COLLATE utf8_bin or COLLATE latin1_bin: 1253); COLLATE binary: 1064.

25.3 Coercibility: which collation wins#

When two texts meet (=, <, IN, BETWEEN, CASE, LIKE, STRCMP, LOCATE, join, UNION, CONCAT…), each carries a coercibility; the lowest wins:

• Column against literal: the column wins (WHERE code = 'a1' on a binary column does not return 'A1'); an explicit COLLATE wins over the column.
• With equal coercibility and different collations: binary wins (binary column against general column in an ON: binary comparison); general against Unicode: error 1267; two different explicit COLLATEs: 1267.
• An operation with three operands and no winner (u IN (g, 'x'), CASE): 1270; four or more: 1271 (UNION: 1271 for any mix with no winner).
• CONCAT, COALESCE, IF… with arguments that have no winner return (utf8mb4_bin, NONE): their result can no longer be compared to a literal (1267).

25.4 Functions and operators#

UPPER and LOWER remain Unicode case mappings whatever the collation.

25.5 Indexes, keys and partitions#

Primary and UNIQUE keys, secondary indexes, foreign keys, trigram and FULLTEXT indexes, and partition bounds and lists follow the collation of their column: in binary 'A1' and 'a1' are two keys, in Unicode 'ß' and 'ss' make only one (1062). An index serves a condition only if the resolved collation of the condition is that of the column: WHERE code COLLATE utf8mb4_general_ci = 'a1' reads the whole table. A FULLTEXT index on columns with different collations: 1283.

25.6 Metadata#

• SHOW CREATE TABLE writes CHARACTER SET utf8mb4 COLLATE name after the type of a column whose collation differs from the table default, and DEFAULT CHARSET=utf8mb4 COLLATE=name when the table default is not the general collation.
• SHOW FULL COLUMNS (Collation column), information_schema.COLUMNS.COLLATION_NAME, TABLES.TABLE_COLLATION.
• SHOW COLLATION and information_schema.COLLATIONS: three rows, utf8mb4_general_ci (45, default), utf8mb4_bin (46), utf8mb4_unicode_ci (224), all PAD SPACE; aliases are not listed.
• Protocol: a Unicode or binary result column is announced as 224 or 46; a general column, like a JSON column, is still announced as 33.

25.7 Errors#

25.8 Differences and out of scope#

• utf8mb4_unicode_520_ci and utf8mb4_uca1400_ai_ci are filed under Unicode (4.0.0 table): on the reference their weights differ (characters added after Unicode 4.0, emojis distinct from one another), and comparing unicode_ci to unicode_520_ci there gives 1267 (Miraj: no error).
• CHARACTER SET utf8mb4 alone, CAST(… AS CHAR) and CONVERT(… USING utf8mb4) give the general collation; the recent reference uses uca1400_ai_ci there.
• Not yet supported: default collation of a database (CREATE DATABASE … COLLATE, accepted with no effect), SET NAMES … COLLATE and collation_connection (no effect), collation of a user variable (SET @v = x COLLATE …: @v stays general), of NEW.col / OLD.col in a trigger and of routine variables (compared as general literals).
• CHARSET(), COLLATION() and COERCIBILITY() are available (chapter 8, §8.9): they return the character set, collation and coercibility (0 to 8) of the expression as the engine resolves it, binary for a number, a date or NULL, latin1 / latin1_swedish_ci or utf8mb3 for CONVERT(… USING set).
• Out of scope: NO PAD collations, case- or accent-sensitive collations other than binary (*_as_cs, *_ai_cs), language-specific ones (german2, czech, turkish…), utf8mb4_0900_*: 1273.
• Existing databases: any table created before collations were introduced is read with the general collation, indexes and partitions unchanged; only a deliberate ALTER TABLE … COLLATE changes it.