MySQL collations : comprendre, choisir et éviter les pièges

Qu'est-ce qu'une collation MySQL ?

C’est l’ensemble des règles qui définissent comment MySQL compare et trie des chaînes pour un jeu de caractères donné.

Voici les principales notions à connaître :

1 Jeux de caractères pris en charge

Déterminent l’ensemble des symboles possibles (emoji inclus).

ascii, latin1, utf8 (3 octets), utf8mb4 (4 octets)

2 Collations et suffixes

Règles de tri/égalité: ai_ci (accent/case insensitive), as_cs (accent/case sensitive).

utf8mb4_0900_ai_ci, utf8mb4_0900_as_cs, latin1_swedish_ci

3 Sensibilités et règles linguistiques

Impactent égalités, LIKE et ORDER BY selon la langue.

Case sensitivity (CS/CI)

Accent sensitivity (AS/AI)

Width/kana sensitivity (ja), règles turques pour I/ı

Version UCA (Unicode Collation Algorithm)

4 Options techniques importantes

Niveaux de paramètres et collations spéciales :

server/database/table/column/connection

COLLATE ... dans ORDER BY et comparaisons

_bin (binaire) pour égalités exactes

MySQL 8 : collations 0900 basées sur Unicode 9

Problèmes classiques

"Illegal mix of collations"

JOIN/UNION/WHERE comparent des colonnes avec collations incompatibles.

Tests unitaires qui échouent

Résultats différents entre local et CI à cause de defaults serveur divergents.

Index UNIQUE incohérent

ai_ci ignore accents/casse: "resume" et "résumé" considérés identiques.

ORDER BY surprenant

Tri linguistique inattendu (ex: "Å" après "Z" selon la collation).

Exemple de problème courant :

# Deux chaînes perçues comme différentes par l'utilisateur mais égales pour MySQL

string1 = "resume"

string2 = "résumé" # Accents ignorés en ai_ci

assert compare(string1, string2, collation="utf8mb4_0900_ai_ci") # ❌ Surprise: égalité

Symptômes qui doivent vous alerter

🚨 Signaux d'alarme

!

Erreur MySQL: "Illegal mix of collations" lors d’un JOIN/UNION

!

ORDER BY qui ne correspond pas aux attentes des utilisateurs

!

LIKE 'école%' ne trouve pas 'ecole' (ou inversement) selon la collation

!

Index UNIQUE qui accepte des doublons apparents ou en rejette trop

!

Comportement différent entre environnements (collation/charset par défaut)

Comment les détecter

✨ Solution recommandée : configuration MySQL maîtrisée

Définissez systématiquement utf8mb4 et une collation adaptée (souvent utf8mb4_0900_ai_ci), puis vérifiez tous les niveaux: serveur, base, tables, colonnes, connexion. Ajoutez des COLLATE explicites dans les requêtes sensibles.

✅ Visibilité immédiate

Inspectez @@collation_% et @@character_set_% en un coup d’œil

📊 Audit complet

Recensement des collations par colonne via information_schema

🧹 Remédiation

ALTER DATABASE/TABLE/CONVERT TO CHARACTER SET utf8mb4 ...

💾 Sécurité

Migrations idempotentes garantissant la cohérence dans le temps

🔍 Vérifier mes collations MySQL

Autres méthodes de détection

Inspecter la base et le code

✓ Affichez @@collation_server, @@collation_database, @@collation_connection

✓ Passez en revue les migrations et clauses COLLATE explicites

En ligne de commande (Unix)

# Voir les collations/charsets du serveur

mysql -e "SHOW VARIABLES LIKE 'collation%'; SHOW VARIABLES LIKE 'character_set%';"

# Lister les collations par colonne

mysql -e "SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME FROM information_schema.COLUMNS WHERE COLLATION_NAME IS NOT NULL"

# Vérifier une base précise

mysql -e "SHOW CREATE DATABASE ma_base\G; SHOW TABLE STATUS FROM ma_base;"

# Tester une comparaison avec une collation

mysql -e "SELECT 'resume' = 'résumé' COLLATE utf8mb4_0900_ai_ci AS eq;"

En code

JavaScript

await db.query("SHOW VARIABLES LIKE 'collation%'; SHOW VARIABLES LIKE 'character_set%';")

Python

cursor.execute("SHOW FULL COLUMNS FROM ma_table"); rows = cursor.fetchall()

Excel / Google Sheets

TRIER(A:A;1;VRAI) ; utilisez la locale du fichier pour le tri linguistique

Nettoyer et prévenir

🚀 Réglages rapides côté MySQL

Avant d’écrire des contournements, standardisez vos collations et charsets dans MySQL pour obtenir des comportements prévisibles :

✓ utf8mb4 partout (serveur, base, tables, colonnes)

✓ Collation par défaut cohérente (ex: utf8mb4_0900_ai_ci)

✓ COLLATE explicite pour requêtes sensibles

Appliquer maintenant

Méthodes techniques avancées

🔧 Normaliser

✓ CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci

✓ Fixez la collation de connexion (SET NAMES utf8mb4 COLLATE ...)

✓ Ajoutez COLLATE dans ORDER BY/WHERE quand le besoin est localisé

🧹 Harmoniser

✓ Alignez les collations des colonnes jointes pour éviter les mix

✓ Utilisez _bin pour égalités exactes (hash, tokens, IDs)

✓ Testez avec accents, casse, emoji (utf8mb4 obligatoire)

⚙️ Automatiser

✓ Migrations imposant charset/collation par défaut du schéma

✓ Tests SQL pour détecter "Illegal mix of collations"

✓ CI qui audite information_schema pour repérer les écarts

Checklist rapide

Serveur et connexion en utf8mb4 avec collation choisie

Base et tables avec defaults cohérents (charset/collation)

Migrations explicitant charset/collation pour chaque colonne texte

Tests avec données contenant accents, casse, emoji

Aucun "Illegal mix of collations" dans la suite de tests

Documentation interne sur le choix de la collation et ses impacts

Conclusion

Les collations MySQL déterminent comment vos utilisateurs voient et manipulent le texte. Bien les régler, c’est éviter des bugs subtils et des régressions.

Standardisez utf8mb4, choisissez une collation adaptée, contrôlez chaque niveau et explicitez vos besoins dans les requêtes critiques.