25. Interclassements (collations)

Un interclassement (collation) fixe la manière dont deux textes se comparent : égalité, ordre, GROUP BY, DISTINCT, clés PRIMARY / UNIQUE, jointures, LIKE, partitions et plein texte. MIRAJ en propose trois, choisis par colonne, par table ou par expression, avec les règles de la référence. Tout texte est rangé en UTF-8 (utf8mb4).

25.1 Les trois interclassements#

Interclassement	Noms SQL acceptés	Comportement
général (par défaut)	`utf8mb4_general_ci`, `utf8mb3_general_ci` (`utf8_general_ci`), `latin1_swedish_ci`, `latin1_general_ci`	un poids de 16 bits par caractère ; casse et accents ignorés (`'Crème' = 'creme'`), `ß` = `s` (mais ≠ `ss`), `æ`, `œ`, `ø`, `ł` gardent leur propre poids ; tout caractère hors BMP pèse `U+FFFD` (`'😀' = '😁'`)
Unicode	`utf8mb4_unicode_ci`, `utf8mb3_unicode_ci` (`utf8_unicode_ci`), et par approximation `utf8mb4_unicode_520_ci`, `utf8mb3_unicode_520_ci`, `utf8mb4_uca1400_ai_ci`, `utf8mb3_uca1400_ai_ci`	table UCA 4.0.0 relevée sur la référence, niveau primaire seul : casse et accents ignorés, expansions (`'ß' = 'ss'`, `'œ' = 'oe'`, `'ﬁ' = 'fi'`, `'Ⅳ' = 'iv'`), marques combinantes et caractères de contrôle ignorés (`'e' + U+0301 = 'e'`), `æ` reste une lettre propre (`'æ' ≠ 'ae'`) ; ordre UCA (ponctuation < chiffres < lettres : `'_' < 'a'`) ; idéogrammes et caractères absents de la table : poids implicites ; tout caractère hors BMP a un même poids (`'😀' = '😁'`)
binaire	`utf8mb4_bin`, `utf8mb3_bin` (`utf8_bin`), `latin1_bin`	comparaison par point de code : casse et accents distingués (`'A1' ≠ 'a1'`, `'é' ≠ 'e'`), `'B' < 'a'`

Les trois sont PAD SPACE : les espaces finaux (U+0020 seulement) sont ignorés par =, l'ordre, GROUP BY, DISTINCT, les clés et les jointures ('a' = 'a ' vaut 1, même en binaire ; une tabulation finale compte). LIKE ne complète jamais par des espaces ('a ' LIKE 'a' est faux).

Les alias utf8_… sont lus utf8mb3_…. CHARACTER SET latin1 est accepté pour une colonne mais le texte reste rangé en utf8mb4. Le type binaire (BINARY, VARBINARY, BLOB, CAST(… AS BINARY)) se compare toujours octet par octet, sans PAD.

Une colonne JSON est toujours en utf8mb4_bin (comme sur la référence) : deux documents qui ne diffèrent que par la casse sont différents, et JSON_UNQUOTE(JSON_EXTRACT(doc, '$.a')) = 'x' ne trouve pas "X". SHOW CREATE TABLE n'écrit rien pour elle.

25.2 Choisir l'interclassement#

Colonne et table#

CREATE TABLE liasse (
    code   VARCHAR(10) COLLATE utf8mb4_bin PRIMARY KEY,   -- 'A1' et 'a1' sont deux codes
    nom    VARCHAR(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    notes  TEXT                                           -- défaut de la table
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

[CHARACTER SET jeu] COLLATE nom après le type d'une colonne texte (CHAR, VARCHAR, *TEXT, ENUM, SET) ; CHARACTER SET jeu seul donne l'interclassement par défaut du jeu, l'interclassement général.
L'attribut BINARY d'une colonne texte (VARCHAR(10) BINARY) vaut COLLATE utf8mb4_bin ; BINARY COLLATE d'un autre nom : erreur 1302.
[DEFAULT] CHARSET = jeu, [DEFAULT] COLLATE = nom en option de table : défaut des colonnes texte écrites sans interclassement.
CREATE TABLE … LIKE recopie colonnes et défaut ; CREATE TABLE … AS SELECT et les colonnes de vue prennent l'interclassement de la colonne source ou de l'expression (CONCAT(col_bin, 'x') reste binaire).

`ALTER TABLE`#

MODIFY / CHANGE / ADD d'une colonne : COLLATE écrit, sinon le défaut de la table ; RENAME COLUMN garde l'interclassement.
[DEFAULT] COLLATE = nom change le défaut de la table sans toucher aux colonnes existantes.
CONVERT TO CHARACTER SET utf8mb4 [COLLATE nom] convertit toutes les colonnes texte (hors JSON).
Changer l'interclassement d'une colonne de clé recontrôle la clé : des valeurs devenues égales ('A1' et 'a1' passant de binaire à général, 'ß' et 'ss' passant à Unicode) donnent 1062 et la table reste inchangée.
Changer l'interclassement d'une colonne de partitionnement est refusé (1235).

Expression#

expr COLLATE nom impose un interclassement à une expression texte :

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

Le nom doit être un interclassement de utf8mb4 ('a' COLLATE utf8_bin ou COLLATE latin1_bin : 1253) ; COLLATE binary : 1064.

25.3 Coercibilité : quel interclassement l'emporte#

Quand deux textes se rencontrent (=, <, IN, BETWEEN, CASE, LIKE, STRCMP, LOCATE, jointure, UNION, CONCAT…), chacun porte une coercibilité ; la plus faible l'emporte :

Coercibilité	Valeur	Exemples
`EXPLICIT`	0	`expr COLLATE nom`
`NONE`	1	`CONCAT(col_unicode, col_general)` : mélange sans vainqueur
`IMPLICIT`	2	colonne de table, de vue, de table dérivée
`SYSCONST`	3	`USER()`, `DATABASE()`, `VERSION()`, `@@variable`
`CAST`	4	`CAST(… AS CHAR)`, `CONVERT(… USING utf8mb4)`
`USERVAR`	5	`@v`
`COERCIBLE`	6	littéral, paramètre, fonction sans argument texte (`HEX`, `DATE_FORMAT`)
`NUMERIC` / `IGNORABLE`	7 / 8	nombres, dates, `NULL` : ne prennent pas part

Colonne contre littéral : la colonne l'emporte (WHERE code = 'a1' sur une colonne binaire ne rend pas 'A1') ; un COLLATE explicite l'emporte sur la colonne.
À coercibilité égale et interclassements différents : le binaire l'emporte (colonne binaire contre colonne générale dans un ON : comparaison binaire) ; général contre Unicode : erreur 1267 ; deux COLLATE explicites différents : 1267.
Une opération à trois opérandes sans vainqueur (u IN (g, 'x'), CASE) : 1270 ; quatre et plus : 1271 (UNION : 1271 pour tout mélange sans vainqueur).
CONCAT, COALESCE, IF… d'arguments sans vainqueur rendent (utf8mb4_bin, NONE) : leur résultat ne peut plus être comparé à un littéral (1267).

25.4 Fonctions et opérateurs#

Opération	Interclassement appliqué
`=`, `<`, `<=>`, `IN`, `BETWEEN`, `CASE`, `NULLIF`, `LEAST`, `GREATEST`, `STRCMP`, `FIELD`, `FIND_IN_SET`	interclassement résolu des opérandes
`GROUP BY`, `DISTINCT`, `COUNT(DISTINCT)`, `MIN` / `MAX`, `ORDER BY`, fenêtres, jointures, `UNION`	celui de l'expression
`LIKE`	résolu sur la valeur et le motif ; caractère contre caractère, sans PAD et sans expansion (`'ß' LIKE 'ss'` est faux en Unicode, `'Crème' LIKE 'Cr_me'` vrai partout, `LIKE 'crème'` faux en binaire)
`LOCATE`, `INSTR`, `POSITION`	général : sans casse, sensible aux accents ; Unicode et binaire : fenêtre de même longueur en octets que la sous-chaîne (`LOCATE('ss', 'Straße' COLLATE utf8mb4_unicode_ci)` rend 5, `LOCATE('a', 'A' COLLATE utf8mb4_bin)` rend 0)
`REGEXP`, `REGEXP_*`	pas de poids (`'é' REGEXP 'e'` faux) ; insensible à la casse sauf en binaire (`'ABC' COLLATE utf8mb4_bin REGEXP 'abc'` vaut 0) ; le type de correspondance `'c'` / `'i'` l'emporte
`REPLACE`, `TRIM`, `SUBSTRING_INDEX`	recherche binaire (sensible à la casse) dans les trois cas ; interclassements incompatibles : 1267 / 1270
`WEIGHT_STRING`	celui du premier argument : général 16 bits par caractère, Unicode 16 bits par unité de poids (`AS CHAR(n)` coupe à n unités, complète par `0209`), binaire 3 octets par caractère (complété par `000020`)
`JSON_SEARCH`	celui du document (colonne `JSON` : binaire ; littéral : général ; colonne texte : la sienne)
`MATCH … AGAINST`	celui de l'index `FULLTEXT` : `'strasse'` trouve `Straße` en Unicode, le binaire distingue casse et accents

UPPER et LOWER restent des correspondances de casse Unicode quel que soit l'interclassement.

25.5 Index, clés et partitions#

Clés primaires et UNIQUE, index secondaires, clés étrangères, index trigramme et FULLTEXT, bornes et listes de partitions suivent l'interclassement de leur colonne : en binaire 'A1' et 'a1' sont deux clés, en Unicode 'ß' et 'ss' n'en font qu'une (1062). Un index ne sert une condition que si l'interclassement résolu de la condition est celui de la colonne : WHERE code COLLATE utf8mb4_general_ci = 'a1' lit toute la table. Un FULLTEXT sur des colonnes d'interclassements différents : 1283.

25.6 Métadonnées#

SHOW CREATE TABLE écrit CHARACTER SET utf8mb4 COLLATE nom après le type d'une colonne dont l'interclassement diffère du défaut de la table, et DEFAULT CHARSET=utf8mb4 COLLATE=nom quand le défaut de la table n'est pas l'interclassement général.
SHOW FULL COLUMNS (colonne Collation), information_schema.COLUMNS.COLLATION_NAME, TABLES.TABLE_COLLATION.
SHOW COLLATION et information_schema.COLLATIONS : trois lignes, utf8mb4_general_ci (45, défaut), utf8mb4_bin (46), utf8mb4_unicode_ci (224), toutes PAD SPACE ; les alias ne sont pas listés.
Protocole : une colonne de résultat Unicode ou binaire est annoncée 224 ou 46 ; une colonne générale, comme une colonne JSON, reste annoncée 33.

25.7 Erreurs#

Code	Cas
1062	doublon sous l'interclassement de la clé (aussi à l'`ALTER` qui change l'interclassement)
1064	`COLLATE binary` sur un texte
1235	changer l'interclassement d'une colonne de partitionnement ; `ALTER DATABASE … COLLATE`
1253	interclassement d'un autre jeu (`CHARACTER SET latin1 COLLATE utf8mb4_unicode_ci`, `'a' COLLATE utf8_bin`)
1267	deux opérandes sans vainqueur : `Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='`
1270	trois opérandes sans vainqueur (`IN`, `CASE`, `REPLACE`)
1271	quatre opérandes et plus, ou `UNION`
1273	nom inconnu : `Unknown collation: 'utf8mb4_0900_ai_ci'`
1283	`FULLTEXT` sur des colonnes d'interclassements différents
1302	`BINARY` et un `COLLATE` autre que binaire sur la même colonne

25.8 Écarts et hors périmètre#

utf8mb4_unicode_520_ci et utf8mb4_uca1400_ai_ci sont rangées sous Unicode (table 4.0.0) : sur la référence leurs poids diffèrent (caractères ajoutés après Unicode 4.0, émojis distincts entre eux), et comparer unicode_ci à unicode_520_ci y donne 1267 (MIRAJ : aucune erreur).
CHARACTER SET utf8mb4 seul, CAST(… AS CHAR) et CONVERT(… USING utf8mb4) donnent l'interclassement général ; la référence récente y met uca1400_ai_ci.
Non encore pris en compte : interclassement par défaut d'une base (CREATE DATABASE … COLLATE, accepté sans effet), SET NAMES … COLLATE et collation_connection (sans effet), interclassement d'une variable utilisateur (SET @v = x COLLATE … : @v reste général), de NEW.col / OLD.col dans un déclencheur et des variables de routine (comparés comme des littéraux généraux).
CHARSET(), COLLATION() et COERCIBILITY() sont disponibles (chapitre 8, §8.9) : elles rendent le jeu, l'interclassement et la coercibilité (0 à 8) de l'expression telle que le moteur la résout, binary pour un nombre, une date ou NULL, latin1 / latin1_swedish_ci ou utf8mb3 pour CONVERT(… USING jeu).
Hors périmètre : interclassements NO PAD, sensibles à la casse ou aux accents autres que binaires (*_as_cs, *_ai_cs), linguistiques (german2, czech, turkish…), utf8mb4_0900_* : 1273.
Bases existantes : toute table créée avant les interclassements est lue en interclassement général, index et partitions inchangés ; seul un ALTER TABLE … COLLATE volontaire la change.