Comment MySQL gère t-il ses collations, c’est à dire la manière dont son gérer les comparaisons entre littéraux, notamment la sensibilité au regarde de la casse (différentiation entre majuscules et minuscules) ou en ce qui concerne la sensibilité aux caractères diacritiques (les accents, la cédille, les ligatures) ?
Le moins que l’on puisse dire c’est que la façon de gérer les collations est particulièrement confuse dans MySQL.

Distinction : ま マ

Ce qui dit la norme SQL ISO/IEC 9075-2 (fondations SQL) est simple : la « SQL Feature F690″ de nom « Collation support » précise comment les collations sont gérées dans le langage SQL. Mais la chose est indiquée de manière sibylline dans la norme SQL, car les collations dépendent en fait des jeux de caractères et l’on doit donc se tourner vers les normes concernant les jeux de caractères. La norme à utiliser est alors la norme ISO/IEC 14651.

UN PEU DE TERMINOLOGIE…

Pour la suite de notre propos, nous allons définir quelques termes :

casse : autrefois, boite dotée de nombreux casiers (ou cassetins) pour le rangement des caractères typographiques en métal d’un seul corps d’une même police. En haut figurait les lettres capitales en majuscule ainsi que quelques lettres accentuées et en base les minuscules ainsi que les autres lettres les plus utilisés et les espaces de différentes longueur. Par extension ce terme a été repris en informatique das le domaine typographique pour représenter la forme majuscule (haut de casse) ou minuscule (bas de casse) des lettres.

collation : encore en usage dans l’aviation ou les métiers de l’urgence (pompiers, police…) ce terme désigne le fait de retransmettre une information dans sa voix pour faire comprendre à celui qui vous l’a communiqué que vous avez parfaitement compris, en reprenant presque mot à mot le terme du propos. Par exemple, lorsque le contrôle aérien ordonne au pilote d’un avion « AFR77FW, alignez vous piste 36 gauche et attendez » ce dernier doit répondre, par exemple cela « Alignons piste 36 gauche et attendons AFR77FW« . Ce terme est très ancien, car les moines copistes, ces photocopieurs d’avant la révolution Gutenberg, collationnaient les grimoires…
En matière de données informatique, la collation est donc la propriété d’une donnée littérale a être interprétée d’une manière spécifique en tenant compte de différents paramètres, par exemple la casse ou les accents.

lettre basique : toutes les lettres simples dénuées d’accents ou autre caractère diacritique ou encore non incorporées dans une ligature. Pour le français ce sont les lettres : a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z.

diacritique : un (signe) diacritique (du grec diacritikós, « qui distingue ») est un signe complémentant une lettre basique afin d’en enrichir le sens. En français, ce peut être un accent (aigu, grave, circonflexe, tréma : diacritique suscrit), une cédille (diacritique souscrit).

ligature : une ligature est la fusion de deux ou plusieurs caractères basiques pour n’en former qu’un seul nouveau, considéré ou non comme un caractère à part entière. Il existe 3 ligatures spécifique au français : l' »e » dans l' »o » comme dans cœur ou bœuf, l' »e » dans l' »a » comme Lætitia et ex-æquo et enfin le « et » commercial & appelé esperluette à l’origine abréviation.
L’arobe (ou arobase) est une ligature d’origine latine reprise pour les adresses dans les langages informatiques, afin de spécifier une variable (adresse mémoire) ou pour les e-mails.
Certaines ligature ne sont la que pour l’esthétique typographique comme le double « f » ff ou l’arrangement de 3 lettres ffi.

1 – LE PROBLÈME DES COLLATIONS

Inutile de lire l’énorme document que constitue la norme la norme ISO/IEC 14651 pour comprendre le problème.
Une collation est un attribut d’un littéral et permet de spécifier :
1) l’ordre de classement des lettres conforme au dictionnaire spécifique à une langue;
2) si l’on doit tenir compte de la casse (et donc faire la distinction entre majuscules et minuscules) ou non;
3) si l’on doit tenir compte des éléments diacritique d’une lettre (et donc faire la distinction entre les lettres avec et sans accents) ou non;
4) si l’on doit tenir compte des ligatures (donc des symboles constituées de plusieurs lettres imbriquées) ou non;
5) si l’on doit tenir compte des différences entre les formes des écritures des symboles comme c’est le cas du japonais syllabique (katakana カタカナ et hiragana ひらがな);
6) si l’on doit tenir comte de la largeur d’encodage (certains caractères étant codés sur 1 octets, d’autres sur 2, d’autres sur 3 ou plus encore en fonction des jeux de caractères) auxquels les collations sont associées;
7) ne tenir aucun compte de la sémantique des caractères et effectuer des comparaisons sur les codes binaires des symboles.

1.1 – ordre de classement des lettres conforme au dictionnaire spécifique à une langue

Le classement des lettres est spécifique à deux éléments de l’alphabet d’une langue : l’ordre des symboles qui la compose et le sens de lecture. En français l’ordre des symboles est a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z et le sens de lecture de gauche à droite.
L’arabe et l’hébreu se lisent de droite à gauche et pour l’interclassement de certains symboles comme les parenthèses il faut en inverser le sens habituel ouvrant/fermant.

L’interclassement des lettres françaises est un peu particulier. Par exemple pour le cas de la ligature « e » dans l' »o » (« œ »), il faut considérer les mots ayant cette ligature comme devant être classés en ordre alphabétique par équivalence à la double lettre « oe ». On obtiendra donc le classement suivant :
coercition
cœur
coexister

Pour les accents, c’est plus complexe… Un mot sans accent précède toujours la graphie accentuée. Mais pour les mots possédant plusieurs accents la règle est particulière…
Dans la plupart des langues, le classement des lettres avec et sans accent s’effectue dans le sens de la lecture. Si toutes les lettres basiques sont les mêmes le premier accent trouvé dans l’ordre de lecture détermine l’ordre final de classement des mots. Mais en français, comme dans quelques autres langues, la lecture pour classement des accents doit se faire dans l’ordre inverse, c’est à dire en partant de la fin (à droite) et en remontant les lettres du mot vers la gauche.
Voici une liste de mots et son classement pour d’autres langues que le français :
cote
coté
côte
côté
Voici maintenant le classement en français :
cote
côte
coté
côté
On trouvera le même problème pour les mots élève et élevé (ici dans le bon ordre)

1.2 – Gestion de la casse

Deux possibilités : distinction ou confusion entre majuscules et minuscules.

Distinction : 'a'  'A' 

Confusion :   'a' =  'A'

1.3 – Gestion des caractères diacritiques

Deux possibilités : confusion ou distinction des caractères diacritiques.

Distinction : 'a'  'à' 

Confusion :   'a' =  'à'

1.4 – Gestion des ligatures

Deux possibilités : confusion ou distinction des ligatures.

Distinction : 'ae' 'æ' 

Confusion :   'ae' =  'æ'

1.5 – Gestion des formes d’écritures

Deux possibilités : confusion ou distinction des formes d’écriture.

Distinction : ま = マ

Confusion : ま  マ

Ici on compare les deux symboles du « ma » en phonétique japonaise hiragana et katatana.
NOTA : ceci ne concerne actuellement que le japonais qui possède cette particularité (idéogrammes cursifs anciens de l’hiragana et plus abrupts du moderne katakana)

1.6 – Gestion de la largeur d’encodage

Deux possibilités : confusion ou distinction des largeur d’encodage.

Distinction : N' '  N' ' 

Confusion :   N' ' =  N' '

Ici le premier caractère est un espace ANSI de code 32 (hexadécimal 20) et le second un espace chinois de code 8192 (hexadécimal 2000).
NOTA : ceci ne concerne que certaines langues asiatiques dans lesquelles il existe quelques caractères ayant un codage sur un ou deux caractères.

1.7 – Comparaison par code sous jacent au littéral

Deux possibilités : confusion ou distinction de l’encodage binaire.

Distinction : CAST(N'a' AS VARBINARY(2))  CAST('a' AS VARBINARY(2)) 

Confusion :   CAST(N'a' AS VARBINARY(2)) =  CAST(N'a' AS VARBINARY(2))

NOTA : le paramétrage « binaire » exclu tous les autres.

QUELQUES REMARQUES À CE STADE…

En breton, il n’y a pas de lettre « c » qui s’écrit « k », mais par contre il y a deux ligatures « ch » et « c’h »;
Il existe d’autres particularité de langues régionales françaises comme pour le corse par exemple.

1.8 – Implémentation des collations dans les SGBDR

Les collations nécessite une clause COLLATE pour être utilisées. Cette clause COLLATE peu être définie :
– au niveau du serveur lors de l’installation;
– au niveau de la base, lors de la création;
– au niveau de la création des tables et des vues pour les colonnes littérales;
– dans chaque expression de requête traitant de littéraux.
MySQL accepte tous ces points avec quelques restrictions concernant le dernier (certaines fonctions n’accepte pas la clause COLLATE)

La plupart des éditeurs de SGBDR se sont accordés pour nommer les collations avec un nom commençant par le nom de la langue et les suffixes ci, cs, ai, as pour signifier respectivement la sensibilité ou non à la casse (case insensitive/sensitive) et la sensibilité aux accents (accent insensitive/sensitive).
La sensibilité/insensibilité aux accents est généralement élargie aux ligatures.
Certains éditeurs ont ajoutés les suffixes KS pour Kanatype Sensitive et WS pour Width Sensitive.
Pour les collations travaillant directement sur le code hexadécimal des symboles, les éditeurs se sont accordés sur le suffixe bin.

1.9 – Implémentation des collations dans MySQL

La liste des collations disponible peut être obtenue sous MySQL par la commande :
SHOW COLLATION;
On peut aussi obtenir la liste de jeux de caractères et la collation qui leur est affecté par défaut, à l’aide de la commande
SHOW CHARACTER SET;

Pour savoir quelle collation est utilisée par une colonne de table ou de vue, utilisez la vue d’information de schéma :

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 

FROM   INFORMATION_SCHEMA.COLUMNS

En supplément, pour toute colonne dans une table MySQL propose les fonctions de métadonnées CHARSET(maColonne) et COLLATION(maColonne) qui renvoient respectivement le jeu de caractères et la collation d’une colonne de table.

Le nombre de collation disponible dans MySQL 5.5 est assez faible (195) en comparaison avec ce qui se fait par exemple dans SQL Server 2008 R2 (2 397). Surtout, un grand nombre de langues, dont le français, ne sont pas représentées. Voici les langues possible :
bulgarian
chinese
croatian
czech
danish
english
esperanto
estonian
german
hungarian
icelandic
japanese
korean
latvian
lithuanian
persian
polish
romanian
sinhala
slovak
slovenian
spanish
swedish
thai
turkish
ukrainian

De plus les collations sont fortement liées à certains jeux de caractères, ce qui ne devrait pas être le cas, car en principe une collation devrait pourvoir être représentée sur n’importe quel jeu de caractères, en vertu d’un principe magistral dans les SGBDR, celui d’indépendance entre couche physique (le jeu de caractères) et couche logique (la collation). Cette intrication entre jeu de caractères et collations dans MySQL rend plus difficile encore son exploitation, car tous les langages, comme les différentes plateformes systèmes et autres outils intermédiaires (serveur Web pare exemple), ne peuvent pas toujours prendre en compte cette diversité !

2 – LE TEST

Pour tester les collations disponible sous MySQL et qui sont nécessaires au français, nous avons pris toutes celles qui nous paraissaient pouvoir être utiles.

Voici les collations qui n’ont pas pu être retenues :

N’accepte aucun caractère diacritique (accent, cédille, ligature ) :
ascii_general_ci
idem pour ascii_bin
cp866_general_ci
cp866_bin
cp1251_bin
cp1251_general_ci
cp1251_general_cs

N’accepte pas les caractères diacritiques majuscules :
cp1256_general_ci

Accepte les accents, mais ni la cédille ni les ligatures :
latin7_bin
cp1257_bin
cp1257_general_ci

Accepte les accents et la cédille mais pas les ligatures :
cp850_general_ci
cp850_bin
latin2_bin
cp1250_general_ci
cp1250_bin
latin5_bin
cp852_general_ci
cp852_bin

Accepte les caractères diacritiques, mais provoque des erreurs de restitution dans la majorité des applications :
utf8mb4_general_ci
utf8mb4_bin
utf8mb4_unicode_ci
utf16_general_ci
utf16_bin
utf16_unicode_ci
utf32_general_ci
utf32_bin
utf32_unicode_ci

Notre table d’essais se résume donc à :

CREATE TABLE T_COL 

(COL_ID INT NOT NULL PRIMARY KEY, 

 COL_latin1_general_ci VARCHAR(16) COLLATE latin1_general_ci, 

 COL_latin1_general_cs VARCHAR(16) COLLATE latin1_general_cs, 

 COL_latin1_bin        VARCHAR(16) COLLATE latin1_bin, 

 COL_utf8_general_ci   VARCHAR(16) COLLATE utf8_general_ci, 

 COL_utf8_bin          VARCHAR(16) COLLATE utf8_bin, 

 COL_utf8_unicode_ci   VARCHAR(16) COLLATE utf8_unicode_ci, 

 COL_ucs2_bin          VARCHAR(16) COLLATE ucs2_bin);;

On pourra en vérifier le jeu de caractère associé à chaque collation de colonne à l’aide de la requête suivante :

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 

FROM   INFORMATION_SCHEMA.COLUMNS 

WHERE  TABLE_NAME = 'T_COL' 

 

COLUMN_NAME                CHARACTER_SET_NAME  COLLATION_NAME 

-------------------------- ------------------- --------------------------  

COL_latin1_general_ci      latin1              latin1_general_ci 

COL_latin1_general_cs      latin1              latin1_general_cs 

COL_latin1_bin             latin1              latin1_bin 

COL_utf8_general_ci        utf8                utf8_general_ci 

COL_utf8_bin               utf8                utf8_bin 

COL_utf8_unicode_ci        utf8                utf8_unicode_ci 

COL_ucs2_bin               ucs2                ucs2_bin

Voici un premier jeu d’essais destiné à vérifier les comparaisons :

INSERT INTO T_COL VALUES 

(1,  'Été',   'Été',   'Été',   'Été',   'Été',   'Été',   'Été'  ), 

(2,  'ÉTÉ',   'ÉTÉ',   'ÉTÉ',   'ÉTÉ',   'ÉTÉ',   'ÉTÉ',   'ÉTÉ'  ), 

(3,  'ETE',   'ETE',   'ETE',   'ETE',   'ETE',   'ETE',   'ETE'  ), 

(4,  'ete',   'ete',   'ete',   'ete',   'ete',   'ete',   'ete'  ), 

(5,  'été',   'été',   'été',   'été',   'été',   'été',   'été'  ), 

(6,  'Maçon', 'Maçon', 'Maçon', 'Maçon', 'Maçon', 'Maçon', 'Maçon'), 

(7,  'MAÇON', 'MAÇON', 'MAÇON', 'MAÇON', 'MAÇON', 'MAÇON', 'MAÇON'), 

(8,  'MACON', 'MACON', 'MACON', 'MACON', 'MACON', 'MACON', 'MACON'), 

(9,  'macon', 'macon', 'macon', 'macon', 'macon', 'macon', 'macon'), 

(10, 'maçon', 'maçon', 'maçon', 'maçon', 'maçon', 'maçon', 'maçon'), 

(11, 'Cœur',  'Cœur',  'Cœur',  'Cœur',  'Cœur',  'Cœur',  'Cœur' ), 

(12, 'CŒUR',  'CŒUR',  'CŒUR',  'CŒUR',  'CŒUR',  'CŒUR',  'CŒUR' ), 

(13, 'COEUR', 'COEUR', 'COEUR', 'COEUR', 'COEUR', 'COEUR', 'COEUR'), 

(14, 'coeur', 'coeur', 'coeur', 'coeur', 'coeur', 'coeur', 'coeur'), 

(15, 'cœur',  'cœur',  'cœur',  'cœur',  'cœur',  'cœur',  'cœur' ), 

(16, 'Æquo',  'Æquo',  'Æquo',  'Æquo',  'Æquo',  'Æquo',  'Æquo' ), 

(17, 'ÆQUO',  'ÆQUO',  'ÆQUO',  'ÆQUO',  'ÆQUO',  'ÆQUO',  'ÆQUO' ), 

(18, 'AEQUO', 'AEQUO', 'AEQUO', 'AEQUO', 'AEQUO', 'AEQUO', 'AEQUO'), 

(19, 'aequo', 'aequo', 'aequo', 'aequo', 'aequo', 'aequo', 'aequo'), 

(20, 'æquo',  'æquo',  'æquo',  'æquo',  'æquo',  'æquo',  'æquo' ), 

(21, 'Cloÿs', 'Cloÿs', 'Cloÿs', 'Cloÿs', 'Cloÿs', 'Cloÿs', 'Cloÿs'), 

(22, 'CLOŸS', 'CLOŸS', 'CLOŸS', 'CLOŸS', 'CLOŸS', 'CLOŸS', 'CLOŸS'), 

(23, 'CLOYS', 'CLOYS', 'CLOYS', 'CLOYS', 'CLOYS', 'CLOYS', 'CLOYS'), 

(24, 'cloys', 'cloys', 'cloys', 'cloys', 'cloys', 'cloys', 'cloys'), 

(25, 'cloÿs', 'cloÿs', 'cloÿs', 'cloÿs', 'cloÿs', 'cloÿs', 'cloÿs'), 

(26, 'H & M', 'H & M', 'H & M', 'H & M', 'H & M', 'H & M', 'H & M'), 

(27, 'H et M', 'H et M', 'H et M', 'H et M', 'H et M', 'H et M', 'H et M');

2.1 Tests de comparaison :

Binaires:

SELECT *  

FROM   T_COL 

WHERE  COL_utf8_bin IN ('ete', 'macon', 'coeur', 'aequo', 'cloys', 'H & M') 

 

SELECT *  

FROM   T_COL 

WHERE  COL_ucs2_bin IN ('ete', 'macon', 'coeur', 'aequo', 'cloys', 'H & M') 

 

SELECT *  

FROM   T_COL 

WHERE  COL_latin1_bin IN ('ete', 'macon', 'coeur', 'aequo', 'cloys', 'H & M')

Sensible à la casse, sensible aux diacritiques (accents, cédille, ligatures) :

SELECT *  

FROM   T_COL 

WHERE  COL_latin1_general_cs IN ('ete', 'macon', 'coeur', 'aequo', 'cloys', 'H & M')

Insensible à la casse, sensible aux diacritiques (accents, cédille, ligatures) :

SELECT *  

FROM   T_COL 

WHERE  COL_latin1_general_ci IN ('ete', 'macon', 'coeur', 'aequo', 'cloys', 'H & M')

Insensible à la casse, insensible aux accents et à la cédille, mais sensible aux ligatures :

SELECT *  

FROM   T_COL 

WHERE  COL_utf8_general_ci IN ('ete', 'macon', 'coeur', 'aequo', 'cloys', 'H & M')

Insensible à la casse, insensible aux accents, à la cédille, et à la ligature OE, mais sensible à la ligature AE et & :

SELECT *  

FROM   T_COL 

WHERE  COL_utf8_unicode_ci IN ('ete', 'macon', 'coeur', 'aequo', 'cloys', 'H & M')

Le bilan est morose :
1 - aucune collation en permet de confondre toutes les spécificités de la langue française. La ligature AE, heureusement peu présente résiste à toutes les collations MySQL, mais plus embêtant le & aussi !
2 - aucune collation ne permet d’être sensible à la casse et insensible au diacritiques et ligatures.

2.2 Tests de classement :

Ajoutons quelques nouvelles données pour le test de classement :

INSERT INTO T_COL VALUES 

(101, 'élève', 'élève', 'élève', 'élève', 'élève', 'élève', 'élève'), 

(102, 'élevé', 'élevé', 'élevé', 'élevé', 'élevé', 'élevé', 'élevé'), 

(103, 'cote', 'cote', 'cote', 'cote', 'cote', 'cote', 'cote'), 

(104, 'coté', 'coté', 'coté', 'coté', 'coté', 'coté', 'coté'),  

(105, 'côté', 'côté', 'côté', 'côté', 'côté', 'côté', 'côté'), 

(106, 'côte', 'côte', 'côte', 'côte', 'côte', 'côte', 'côte'), 

(107, 'azote', 'azote', 'azote', 'azote', 'azote', 'azote', 'azote'), 

(108, 'coexister', 'coexister', 'coexister', 'coexister', 'coexister', 'coexister', 'coexister'), 

(109, 'coercition', 'coercition', 'coercition', 'coercition', 'coercition', 'coercition', 'coercition'), 

(110, 'maçonné', 'maçonné', 'maçonné', 'maçonné', 'maçonné', 'maçonné', 'maçonné'), 

(111, 'maconne', 'maconne', 'maconne', 'maconne', 'maconne', 'maconne', 'maconne'), 

(112, 'mâton', 'mâton', 'mâton', 'mâton', 'mâton', 'mâton', 'mâton'),  

(113, 'bâton', 'bâton', 'bâton', 'bâton', 'bâton', 'bâton', 'bâton'), 

(114, 'bacon', 'bacon', 'bacon', 'bacon', 'bacon', 'bacon', 'bacon'), 

(115, 'et', 'et', 'et', 'et', 'et', 'et', 'et'),  

(116, 'ette', 'ette', 'ette', 'ette', 'ette', 'ette', 'ette'), 

(117, 'état', 'état', 'état', 'état', 'état', 'état', 'état'), 

(118, 'tenue', 'tenue', 'tenue', 'tenue', 'tenue', 'tenue', 'tenue'), 

(119, 'tenues', 'tenues', 'tenues', 'tenues', 'tenues', 'tenues', 'tenues'), 

(120, 'ténue', 'ténue', 'ténue', 'ténue', 'ténue', 'ténue', 'ténue'), 

(121, 'ténues', 'ténues', 'ténues', 'ténues', 'ténues', 'ténues', 'ténues'), 

(122, 'façon', 'façon', 'façon', 'façon', 'façon', 'façon', 'façon'), 

(123, 'faconde', 'faconde', 'faconde', 'faconde', 'faconde', 'faconde', 'faconde'), 

(124, 'façonner', 'façonner', 'façonner', 'façonner', 'façonner', 'façonner', 'façonner'), 

(125, 'élèves', 'élèves', 'élèves', 'élèves', 'élèves', 'élèves', 'élèves'), 

(126, 'élevés', 'élèvés', 'élèvés', 'élèvés', 'élèvés', 'élèvés', 'élèvés'), 

(127, 'élevées', 'élevées', 'élevées', 'élevées', 'élevées', 'élevées', 'élevées'), 

(128, 'aérien', 'aérien', 'aérien', 'aérien', 'aérien', 'aérien', 'aérien'), 

(129, 'H z M', 'H z M', 'H z M', 'H z M', 'H z M', 'H z M', 'H z M');

Ordre : majuscules > minuscules > diacritiques > ligature

SELECT *   

FROM   T_COL  

ORDER  BY COL_utf8_bin 

 

SELECT *   

FROM   T_COL  

ORDER  BY COL_ucs2_bin

Ordre : majuscules > minuscules > ligature / diacritiques

SELECT *   

FROM   T_COL  

ORDER  BY  COL_latin1_bin

Ordre : sensible à la casse, pas de confusion des diacritiques, incapacité à placer les ligatures au bon endroit et règle de tri des accents non respecté pour le français

SELECT *   

FROM   T_COL  

ORDER  BY COL_latin1_general_cs

Ordre : insensible à la casse, pas de confusion des diacritiques, incapacité à placer les ligatures au bon endroit et règle de tri des accents non respecté pour le français

SELECT *   

FROM   T_COL  

ORDER  BY COL_latin1_general_ci

Ordre : insensible à la casse, insensible aux accents et à la cédille, mais sensible aux ligatures et règle de tri des accents non respecté pour le français :

SELECT *   

FROM   T_COL  

ORDER  BY COL_utf8_general_ci

Ordre : insensible à la casse, insensible aux accents, à la cédille, et à la ligature OE, mais sensible à la ligature AE et & et règle de tri des accents non respecté pour le français:

SELECT *   

FROM   T_COL  

ORDER  BY COL_utf8_unicode_ci

Le bilan est mitigé :
La collation la plus proche d’un usage « user firendly » est utf8_unicode_ci mais aucune des collations ne réussit à trier correctement certaines ligatures (AE et &) et le tri des accents est incorrect ce qui est un problème mineur.

3 – UTILISATION DE LA CLAUSE COLLATE DANS LES REQUÊTES

La clause COLLATE permet de modifier le comportement d’une chaine de caractères dans toute expression contenant un littéral.
Elle peut être utilisées dans toutes les clauses d’un SELECT.
Cependant, comme à son habitude, MySQL ne renvoie rien si les collations ne sont pas compatible, au lieu de renvoyer un message d’erreur disant que le critère ne peut pas être traité.
Voici un exemple de ce bug :

Comme vous le constaterez, MySQL ne renvoie rien (c’est généralement le cas du SELECT qui ignore les erreur et renvoie une table vide…).

En principe la collation devrait fonctionner pour toutes les fonctions traitant des littéraux. Hélas ce n’est pas vraiment le cas. Certains fonction prennent en compte la collation, d’autres pas.
Par exemple la fonction POSITION prend bien en compte la collation :

SELECT *, POSITION('oe' IN COL_utf8_unicode_ci), POSITION('oe' IN COL_utf8_bin) 

FROM   T_COL 

WHERE  POSITION('oe' IN COL_utf8_unicode_ci)  0  

   OR  POSITION('oe' IN COL_utf8_bin)  0;

Les ligatures sont bien pris en compte
Mais pas dans la fonction REGEXP :

SELECT * 

FROM   T_COL 

WHERE  COL_utf8_unicode_ci REGEXP '^coeur$'

Qui ne prend même pas en compte les accents.
Il faut donc tester chaque fonction pour savoir si elle prend en compte la collation, car ceci n’est pas clairement exprimé dans la documentation !

3.1 – Comparaisons sur colonnes encodées en UTF8

Comparaisons stricte (depuis colonnes ci/ai) :

SELECT * 

FROM   T_COL 

WHERE  COL_utf8_unicode_ci COLLATE utf8_bin IN ('macon', 'coeur', 'ete', 'aequo', 'h et m', 'cloys')

Comparaisons ci/ai (depuis colonnes binaire) : toutes les ligatures sont ignorées

SELECT * 

FROM   T_COL 

WHERE  COL_utf8_bin COLLATE utf8_general_ci IN ('macon', 'coeur', 'ete', 'aequo', 'h et m', 'cloys')

Comparaisons ci/ai (depuis colonnes binaire) : — les ligatures AE et & sont ignorées

SELECT * 

FROM   T_COL 

WHERE  COL_utf8_bin COLLATE utf8_unicode_ci IN ('macon', 'coeur', 'ete', 'aequo', 'h et m', 'cloys')

Il n’est pas possible de faire une comparaison ci/as ou cs/ai en UTF8

3.2 – Comparaisons sur colonnes encodées en latin1

Comparaisons stricte (depuis colonnes ci/ai) :

SELECT *   

FROM   T_COL  

WHERE  COL_latin1_general_ci COLLATE latin1_bin IN ('macon', 'coeur', 'ete', 'aequo', 'h et m', 'cloys') 

 

SELECT *   

FROM   T_COL  

WHERE  COL_latin1_bin COLLATE latin1_general_cs IN ('macon', 'coeur', 'ete', 'aequo', 'h et m', 'cloys')

Comparaisons ci/as (depuis colonnes binaire) :

SELECT *   

FROM   T_COL  

WHERE  COL_latin1_bin COLLATE latin1_general_ci IN ('macon', 'coeur', 'ete', 'aequo', 'h et m', 'cloys')

Il n’est pas possible de faire une comparaison ci/ai, cs/ai, ci/as en latin 1

3.3 – conclusion

Dans les comparaisons insensible aux accents, MySQL prend bien en compte la cédille et la ligature « e » dans l' »o » (œ), mais pas les ligatures « e » dans l' »a » (æ) ni le « et » commercial (&).

Il n’existe aucun possibilité d’obtenir une comparaison cs/ai ou ci/as c’est a dire en sensibilité partielle, soit uniquement des accents soit uniquement de la casse.

En outre le jeu de caractères latin1 ne permet pas de faire des comparaisons ci/ai.

4 – CONCLUSION

Le constat est sans appel : MySQL fournit un support des collations minimaliste et inutilisable de manière professionnelle. La gestion des ligatures ne concerne que l »e » dans l' »o » (œ) et ignore l' »e » dans l' »a » (æ) et le « et » commercial (&). Le classement des accents est incorrect. Plus grave, les comparaisons en sensibilité partielle (insensible uniquement aux accents ou uniquement à la casse) sont impossibles.
En conclusion, MySQL n’est pas apte à être utilisé dans un cadre professionnel en langue française dès que les enjeux des données doivent concerner des applications du domaine public (santé, justice, état civil par exemple).

Enfin, aucune collation de MySQL ne permet de gérer les problématiques du katakana et de l’hiragana ni la largeur d’encodage, toutes choses propres aux langues asiatique. Si vous envisagez une application internationale et visez le marché asiatique, ce sera un réel problème. Car plus encore que nous, les asiatiques, et les japonais en particulier sont extrêmement à cheval sur les traditions et un « à peu près » ineffectif en la matière se traduirait immédiatement par une sanction commerciale.

Comme j’ai déjà eu l’occasion de le dire, cela fait partie des fonctionnalités poudre au yeux… MySQL fait croire qu’il y a bien une gestion des collations, mais elle s’avère quasi inexploitable !

De plus certains problèmes liés à la mauvaise gestion des collations dans MySQL persitent depuis de nombreuses années, sans qu’aucune solution n’ait été apportées… Exemple : http://bugs.mysql.com/bug.php?id=22034. C’est bien triste lorsque l’on sait que le rapporteur de ce bug, M. Peter Gulutzan est un des grands architectes du moteur de MySQL… C’est dire à quel point le staff de MySQL s’intéresse à rectifier rapidement le tir !

Si l’on compare à la concurrence, MySQL est très en retard. PostGreSQL 9.1 propose le CREATE COLLATION pour créer soit même ses propres collations, tandis que MS SQL Server propose 2 397 collations pour 66 langues en dehors du latin, déclinées en 18 déclinaisons (cs/ci, as/ai, ks, ws, bin…).

5 – BASES DE DONNÉES ET ACCENTS

Certains aficionados de MySQL nous ont affirmés que la mauvaise gestion des accents et autres caractères diacritiques n’avait aucune importance a leur yeux, car ils stockaient des données en majuscule et sans accents…
Rappelons tout d’abord que les majuscules doivent être accentuées, et elles le sont effectivement ! Il suffit juste de lire quelques unes de vos publications favorites pour le constater. En sus c’est une obligation légale, qui peut avoir de lourdes conséquences lorsqu’on ne le fait pas (il est possible d’invoquer une faute et faire annuler une procédure juridique du fait d’une erreur par absence d’accents). Enfin certains mots de la langue française n’ont pas le même sens avec ou sans accents. Cela peut donc conduire à une erreur d’interprétation et au cas ou cette dernière aurait des conséquence fâcheuses, faire porter la responsabilité sur l’auteur du logiciel !
A lire sur le sujet : http://blog.developpez.com/sqlpro/p8239/

ANNEXE 1 – comment SQL Server gère les collations pour le katakana et l’hiragana :

ANNEXE 2 – comment SQL Server gère la largeur d’encodage :

Dans cet exemple, la première formulation correspond à deux idéogrammes séparés par un espace classique (1 octet). Dans la seconde formulation, figure les mêmes idéogrammes, on utilise un espace chinois dont la largeur d’encodage est double (2 octets).

——- ————— —————-
1 0x427A20004257 0x427A00304257

La première requête donne un résultat car la collation n’est pas sensible à la largeur d’encodage, tandis que la seconde requête avec une collation distinguant la largeur d’encodage (extension _WS) ne retourne rien :

QUELQUES RÉFÉRENCES :

Les caractères diacritiques et leur importance notamment pour le français :
http://cahiers.gutenberg.eu.org/cg-bin/article/CG_1996___25_65_0.pdf
http://j.poitou.free.fr/pro/html/ltn/diacritiques.html

Classements des mots notamment règle pour les accents :
http://fr.wikipedia.org/wiki/Classement_alphab%C3%A9tique

Hiragana et katakana (phonétique) :
http://en.wiktionary.org/wiki/Appendix:Comparison_of_hiragana_and_katakana_derivations

La façon dont UNICODE traite les collations :
http://www.unicode.org/reports/tr10/

Quelques tableaux de correspondances des principales collations des SGBDR :
http://www.collation-charts.org/

Quelques explications (alambiquées) sur l’usage des collations dans MySQL :
http://www.verbose.fr/mysql/charset-collate-tricky.html
http://antoun.developpez.com/mysql5/jeux-collations/Jeux_et_Collations.pdf

NOTE SUR LE I-GREC TRÉMA : Ÿ

On me fait souvent remarquer que le y tréma n’existe pas en français… Hé bien, si, dans de nombreux nom de villes ou de personne. Ainsi la rue de Cloÿs à Paris, la commune de l’Haÿ-les-Roses en banlieue parisienne, la ville de Aÿ en région champenoise, dont le vin, moins connu que le champagne est passé en nom propre et s’emploie souvent au scrabble., et n’oublions pas l’écrivain Pierre Louÿs célèbre notamment pour son roman, « la chanson de Bilitis »…

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *