Aller au menu - Aller au contenu

Icône Comprendre les jeux de caractères et interclassements

Par Avatar e-t172
Mise à jour : 23/02/2009
632 visites depuis 7 jours, classé 182/786
La version 4.1 de Mysql a introduit une nouvelle fonctionnalité : le serveur de bases de données a maintenant "conscience" du jeu de caractères utilisé pour stocker les données.

Mais vous vous en doutiez peut-être, en voyant apparaître dans votre vue de table habituelle dans phpmyadmin, un curieux paramètre nommé "interclassement", et comportant une liste de valeurs bizarres et identiques (probablement latin1_swedish_ci). Mais, par manque de temps ou de curiosité, ou encore parce que vous n'arriviez pas à trouver d'informations au sujet de cette nouveauté, vous n'en savez pas plus que ça.

Ce tutoriel a pour but de vous expliquer en quoi consistent ces fonctionnalités, quels sont leurs buts et comment les utiliser.

Par principe, ce tutoriel n'expliquera les manipulations que sous formes de requêtes SQL. Il n'y aura pas de captures d'écran de phpMyAdmin. Cependant, ce dernier dispose comme d'habitude des éléments nécessaires pour imiter ces manipulations via l'interface web (pour peu que vous ayez une version récente). Il vous sera facile de les trouver.
Retour en haut
Sommaire du tutoriel :
Icône du chapitre
Retour en haut

Les jeux de caractères

Si vous voulez comprendre quelque chose à ce tutoriel, il faut absolument que vous maîtrisiez la notion de jeu de caractères. Si vous savez déjà de quoi il s'agit, vous pouvez passer directement à la section suivante. Sinon, lisez ce qui suit.

L'ordinateur ne connaît pas la notion de caractères à proprement parler ; il ne connaît que les nombres. Du coup, on a inventé une table de conversion qui fait correspondre un nombre à un caractère : il s'agit de l'ASCII. Ce dernier définit 128 caractères, sur 7 bits.

Cela fonctionnait très bien lorsque l'informatique n'était encore qu'à ses débuts. Mais ensuite, on s'est rendu compte qu'on avait oublié dans ce code d'inclure... les caractères accentués de nos chères langues nationales. Ainsi, il était impossible d'écrire un "é", un "è", un "à" ou un "ù", et encore moins des glyphes arabes, chinois ou japonais.

Pour remédier à cela, on a utilisé le huitième bit, qui était jusqu'alors inutilisé (enfin, pas vraiment : il était utilisé à des fins de contrôle de l'intégrité des données, mais cela n'est plus très utile car cette fonction est maintenant prise en charge par les protocoles de communication), pour créer des "extensions" au code ASCII. Ces extensions particulières sont des encodages, ou encore des jeux de caractères (charsets) particuliers. En utilisant le huitième bit, on pouvait créer 128 caractères supplémentaires, ce qui était plus que suffisant pour y placer pas mal de caractères accentués.

Ainsi, toute une flopée de jeux de caractères ont été créés, chacun couvrant une plage de langues ou d'alphabets précis : ISO-8859-1 à ISO-8859-15 notamment. Celui correspondant à notre alphabet occidental est l'ISO-8859-1, mais il est de plus en plus remplacé par l'ISO-8859-15 car ce dernier, plus récent, ajoute le support du signe euro "¬".

Ces nombreux encodages ont rapidement créé des problèmes d'incompatibilité. En effet, en l'absence d'informations, comment savoir si le caractère portant la valeur 233 est un "é", comme en ISO-8859-1, ou la lettre hébraïque "yod", comme en ISO-8859-8 (alphabet hébreu) ?

Pour éviter ce genre de dilemme, chaque document transmis ou enregistré porte en général à "proximité" (par exemple, dans un en-tête HTTP), voire dans le document lui-même (comme en XML) un champ indiquant le jeu de caractères dans lequel il a été encodé. Le cas échéant, le logiciel de lecture se replie en général vers un encodage par défaut (souvent, le plus répandu).

Mais que se passe-t-il si le logiciel se trompe et choisit le mauvais encodage, parce que l'encodage par défaut n'est pas le bon, ou parce que l'information d'encodage livrée avec le document est erronée ?


Eh bien, vous vous en doutez, on a droit à quelques problèmes ! Le plus souvent, le logiciel arrivera tout de même à afficher le document, mais tous les caractères accentués seront tout simplement massacrés (les caractères "normaux" sont épargnés car il s'agit de la base ASCII, commune à tous les encodages).

Le plus souvent, on est confronté à deux cas : soit le document est lu en ISO-8859-1 alors qu'il est encodé en UTF-8 (encodage que nous verrons un peu plus loin), auquel cas vous verrez des caractères de ce style à la place des accents : "é" (très joli, n'est-il pas ? ;) ), soit le document est lu en UTF-8 alors qu'il est encodé en ISO-8859-1 (plus rare, sauf en XML), auquel cas tous les accents seront tout simplement remplacés par des "?".

D'où l'intérêt de faire attention à bien vérifier que l'encodage spécifié est bien celui avec lequel on encode le document !

D'accord pour les problèmes d'encodage, mais il y en a un autre : comment faire pour écrire des glyphes latins, hébreu, chinois et russes dans la même page ? Par exemple, pour un cours d'histoire des langues ? Quel encodage ISO-8859-truc dois-je utiliser ?


Aucun. :D En fait, il n'est pas possible de stocker toutes ces possibilités dans les 128 combinaisons possibles. On n'a donc pas le choix : il faut agrandir l'espace, c'est-à-dire s'étendre sur plus d'un octet par caractère.

Oui, mais si on double chaque caractère (par exemple), la taille du document va doubler ! Il n'y a pas une meilleure solution ?


Si. En fait, on va utiliser le fameux huitième bit comme "indicateur d'encodage" : par exemple, on peut dire que s'il est à 0, il s'agit d'un caractère ASCII, et s'il est à 1, on est en présence d'un glyphe non standard et que ici, et ici uniquement, on va le coder avec deux octets. Cela nous donne donc les 7 bits du premier octet plus les 8 bits du second octet, soit 15 bits, soit encore 32768 possibilités. D'un coup, on se sent plus à l'aise !

Ce type d'encodage existe : il s'agit du "célèbre" UTF-8.

Attends, je pige pas là : pourquoi tout le monde ne se met pas à utiliser de l'UTF-8, puisqu'il est bien meilleur que les encodages de la série ISO ?


Pour plusieurs raisons (pour info, le Site du Zéro travaille en UTF-8).

  • UTF-8 est un jeu de caractères récent par rapport aux autres. Il est longtemps resté méconnu et peu utilisé. Du coup, l'habitude d'utiliser ISO-8859-1 reste encore solidement répandue.
  • UTF-8 n'est d'aucune utilité si vous êtes absolument certains que vous allez rester dans un alphabet bien précis (l'alphabet latin, par exemple). Auquel cas, il peut s'avérer plus judicieux d'utiliser un encodage de la série ISO car un caractère accentué y prend moins de place qu'en UTF-8.
    Attention : quand je dis "absolument certains", c'est vraiment certain de chez certain : par exemple, irez-vous jurer sur votre tête que vous n'insérerez jamais de caractère hébreu ou russe sur votre blog ? Sur un billet ayant pour sujet l'encodage des caractères par exemple, ou sur une langue précise ?
  • Surtout, le principal obstacle à la généralisation d'UTF-8 est que, contrairement aux encodages classiques, il faut que les logiciels soient adaptés. Pourquoi ? Parce que dans un encodage classique, le nombre d'octets est le même que le nombre de caractères (vu qu'un octet égale un caractère). Avec UTF-8, cette égalité ne tient plus. Du coup, le comportement des programmes non conçus pour gérer l'UTF-8 peut se révéler étrange avec ces types de documents. Il faut également signaler que pour cette même raison, une chaîne en UTF-8 est plus longue à traiter qu'une chaîne en encodage classique.


Tout ça est bien joli, mais je ne sais même pas quel jeu de caractères j'utilise pour mon application/page web/documents !


Si vous ne savez pas quel encodage vous utilisez, il y a gros à parier qu'il s'agit de ISO-8859-1 (aussi appelé latin1).
Retour en haut

Les interclassements

Maintenant que vous avez appris en quoi consistent les jeux de caractères, voyons maintenant ce que sont les interclassements, et à quoi ils servent.

Un interclassement (aussi appelé collation) est une table de correspondance spécifique à un jeu de caractères. Un jeu de caractères peut avoir plusieurs interclassements : en général, un par langue (par exemple, latin1 français, latin1 suédois).

Il a deux fonctions :

  • il permet l'ordonnancement correct d'une liste de caractères. Par exemple, si l'on classe une liste par ordre alphabétique, qu'est-ce qui indique au logiciel que le "é" vient après le "e", ou que les majuscules viennent avant les minuscules (par exemple) ? Eh bien c'est l'interclassement. Ces ordres sont en général spécifiques à une langue. MySQL s'en sert pour les clauses ORDER BY des requêtes.
    En l'absence d'interclassement, les codes des caractères sont utilisés ; or, ceux-ci ne reflètent pas le véritable ordre des glyphes.
  • Il permet de savoir quand un caractère est "équivalent" à un autre. Par exemple, avec un interclassement adapté, le logiciel peut "prendre conscience" du fait que le caractère "e" est équivalent à "E", mais aussi à "é", "è" ou "ê". Cela peut se révéler extrêmement pratique pour la recherche : ainsi, une recherche sur "élément" retournera les résultats contenant "élément" mais aussi "Element", "element", voire "élEmeNt". D'où des recherches plus efficaces.
    En l'absence d'interclassement, aucun caractère n'est équivalent à un autre.


Avec le jeu de caractères UTF-8, en plus des principales langues, un interclassement "générique" permet de gérer le cas des langues mélangées.

Les interclassements vous seront très utiles pour obtenir des classements ORDER BY plus exacts avec les chaînes de caractères, ou pour rendre vos recherches plus efficaces (notamment les recherches de type FULLTEXT).
Retour en haut

Présentation des fonctionnalités de MySQL

MySQL gère une foule de jeux de caractères, parmi lesquels on retrouve les "classiques" ISO-8859-* et UTF-8, mais aussi des codages chinois, russes, japonais, coréens...

Pour afficher la liste de tous les jeux de caractères supportés par votre serveur MySQL, exécutez la requête suivante :
Code : SQL
1
SHOW CHARACTER SET

Et pour les interclassements :
Code : SQL
1
SHOW COLLATION


Les interclassements se terminant par _ci sont insensibles à la casse ("E" = "e"), ceux se terminant par _cs sont sensibles à la casse, et enfin, ceux se terminant par _bin signifient classement binaire (autrement dit, pas d'interclassement).


MySQL fait preuve d'une grande souplesse dans la définition de ces paramètres. Il est ainsi possible de spécifier dans quel jeu de caractères et interclassement les données sont stockées séparément pour chaque colonne. Il est également possible pour un client de préciser le jeu de caractères utilisé lors de la transmission des données, de sorte que le serveur fasse automatiquement la traduction, si nécessaire.

La manipulation de ces fonctions est quelque peu compliquée à expliquer (et donc à comprendre). Néanmoins, une fois que vous aurez compris, vous réaliserez que MySQL peut vous simplifier la vie dans bien des cas.
Retour en haut

Les données dites "binaires"

Les colonnes de type binaire (BLOB, BINARY VARCHAR...) ne gèrent pas les jeux de caractères, ni les interclassements, et considèrent leur contenu comme des données brutes (d'où leur nom : binaire). Mis à part cette particularité, elles fonctionnent comme des colonnes de type TEXT.

Vous devriez les utiliser pour stocker des données pour lesquelles le jeu de caractères n'apporte rien, c'est-à-dire des données non textuelles au sens strict du terme (par exemple, des URL, des adresses e-mail...).

Ne négligez pas ce point ! Si vous stockez des URL dans une colonne TEXT, cela signifie, par exemple, que ces deux adresses seront les mêmes pour MySQL :
Code : Autre
1

http://www.example.com/éléctricité/PAGE.htm

Code : Autre
1

<lien url="http://www.example.com/electricite/Page.htm">http://www.example.com/electricite/Page.htm</lien>

Alors qu'en réalité, ces deux adresses sont différentes ! (Idem pour les adresses e-mail...)


Il est possible, même avec des colonnes de type TEXT, de spécifier avec MySQL de ne pas prendre en charge les jeux de caractères et interclassements pour une opération précise. Il suffit d'ajouter le mot-clé BINARY devant la chaîne de caractères en question (ou le nom d'une colonne). Ainsi, la requête suivante retournera 1 (vrai) :

Code : SQL
1
SELECT "éEe" = "eee"


Tandis que celle-ci retournera 0 (faux) :

Code : SQL
1
SELECT "éEe" = BINARY "eee"


Notez que j'aurais pu mettre BINARY de n'importe quel côté de la comparaison.

Lors de l'utilisation de données considérées comme binaires, MySQL n'effectuera aucune conversion, y compris lors de la transmission des données ou du stockage !
Retour en haut

Jeux de caractères et interclassements utilisés pour le stockage

Il est possible de spécifier un jeu de caractères et / ou un interclassement pour une base de données, pour une table, et pour une colonne d'une table. Les paramètres des bases de données et des tables ne sont en fait que des valeurs par défaut pour les nouvelles colonnes : seul le jeu de caractère / interclassement de la colonne est en fait exploité lors d'une opération.

En plus clair, cela signifie que si vous ne spécifiez pas de jeu de caractères (par exemple) lorsque vous créez ou "altérez" une colonne, ce sera le jeu de caractères de la table qui sera utilisé et écrit comme étant le jeu de caractères de la colonne. Même principe pour le jeu de caractères des tables et celui des des bases de données.

Voici les différentes requêtes à utiliser pour spécifier les jeux de caractères et les interclassements utilisés pour le stockage des données :

  • pour une base de données :
    Code : SQL
    1
    CREATE DATABASE base_de_donnees [...] CHARACTER SET jeu_de_caracteres COLLATE interclassement

    Code : SQL
    1
    ALTER DATABASE base_de_donnees CHARACTER SET jeu_de_caracteres COLLATE interclassement
  • pour une table :
    Code : SQL
    1
    CREATE TABLE table [...] CHARACTER SET jeu_de_caracteres COLLATE interclassement

    Code : SQL
    1
    ALTER TABLE table CHARACTER SET jeu_de_caracteres COLLATE interclassement
  • pour une colonne :
    Code : SQL
    1
2
3
4
5
    CREATE TABLE table (
colonne [...] CHARACTER SET jeu_de_caracteres COLLATE interclassement
[...]
)
[...]

    Code : SQL
    1
    ALTER TABLE table CHANGE colonne colonne type CHARACTER SET jeu_de_caracteres COLLATE interclassement


Attention : le fait de modifier le jeu de caractères d'une colonne avec ALTER entraîne automatiquement la conversion des données déjà présentes dans la colonne !


Notez que la requête suivante permet de convertir toutes les colonnes de la table à un jeu de caractères précis :

Code : SQL
1
ALTER TABLE table CONVERT TO CHARACTER SET jeu_de_caracteres COLLATE interclassement


J'ai une colonne déclarée en latin1, mais j'ai stocké à l'intérieur des données en UTF-8 ! Comment rétablir la cohérence de mes données ?


Ce problème se produit très souvent, notamment lorsque vous mettez à jour une base de données MySQL 4.0 en une version plus récente, prenant en compte les jeux de caractères, ou tout simplement parce que vous venez de découvrir les fonctionnalités de MySQL avec ce tuto, et que vous stockiez vos données en UTF-8 sans vous préoccuper de la déclaration de la colonne.

La documentation MySQL indique une astuce pour rétablir la situation : convertir la colonne en type binaire (BLOB, par exemple), puis la reconvertir en type texte en spécifiant le jeu de caractères voulu :

Code : SQL
1
2
ALTER TABLE table CHANGE colonne colonne type_binaire;
ALTER TABLE table CHANGE colonne colonne type_texte CHARACTER SET jeu_de_caracteres;


Par exemple :

Code : SQL
1
2
ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
Retour en haut

Jeux de caractères et interclassements de connexion

Considérez le problème suivant : vous avez une colonne dont les données sont stockées en UTF-8, et votre client (par exemple, un script PHP) utilise ISO-8859-1. Comment manipuler les données de la colonne ? Il faudrait d'abord convertir les données envoyées en UTF-8, puis les données reçues en ISO-8858-1... Pas très pratique, surtout lorsqu'on a plusieurs clients utilisant des encodages différents.

C'est là que MySQL va nous aider : il peut se charger lui-même de la traduction. Dans notre exemple, il suffirait de dire à MySQL que le client est en ISO-8859-1, pour qu'il effectue tout seul les conversions d'encodage là où c'est nécessaire ! Ainsi, il convertirait les données envoyées par le client en UTF-8, et vice-versa, pour les données que le serveur envoie. Elle est pas belle, la vie ? :D

Pour cela, il faut manipuler quatre variables MySQL :

  • character_set_client indique au serveur l'encodage que le client utilise, c'est-à-dire dans quel jeu de caractères sont transmises les données du client vers le serveur.
  • character_set_connection indique dans quel jeu de caractères le serveur va convertir les données avant de les traiter. Sauf rares exceptions, on indiquera la même valeur que pour character_set_client, afin d'éviter les conversions intermédiaires qui ne servent pas à grand-chose dans la plupart des cas.
  • collation_connection indique quel interclassement sera utilisé dans la comparaison de chaînes littérales, c'est-à-dire par exemple "machin" = "chose". Cette variable n'a aucun effet sur les comparaisons impliquant des colonnes, car celles-ci ont leur propre interclassement.
  • character_set_results indique au serveur dans quel jeu de caractères celui-ci doit transmettre les résultats. Généralement, on indiquera le même encodage que pour character_set_client.


Une variable peut se définir de la manière suivante :

Code : SQL
1
SET variable = valeur


Évidemment, les 4 variables décrites plus haut doivent être redéfinies à chaque connexion.

Les développeurs de MySQL, qui sont des gens sympas, ont ajouté un "raccourci" pour régler toutes les variables d'un seul coup :

Code : SQL
1
SET NAMES jeu_de_caracteres


Qui a le même effet que :

Code : SQL
1
2
3
SET character_set_client = jeu_de_caracteres;
SET character_set_results = jeu_de_caracteres;
SET character_set_connection = jeu_de_caracteres;


Il existe aussi SET CHARACTER SET, mais il est moins utile.
Retour en haut
En guise de conclusion, voici un petit récapitulatif des bonnes pratiques enseignées dans ce tuto.

  • Définissez les jeux de caractères et interclassements de vos colonnes en fonction de vos besoins ; dans le doute, utilisez UTF-8. Ne stockez pas les données dans un jeu de caractères différent de celui déclaré pour la colonne.
  • Définissez comme étant binaires (BLOB, BINARY VARCHAR, TINYBLOB...) les colonnes comportant des valeurs non textuelles, telles que les URL, les adresses e-mail, les hashs MD5 de mots de passe...
  • Lorsque vous utilisez un client MySQL (par exemple, PHP), spécifiez le jeu de caractères que vous utilisez avec SET NAMES.


Pour plus d'informations, consultez la documention officielle de MySQL sur le sujet.

Bonne chance !

Remerciements : ptipilou pour la zCorrection :)
Retour en haut

Partager

Retour en haut
43 commentaires pour "Comprendre les jeux de caractères et interclassements"
Note moyenne : 4.00 / 4 (14 votes)
Pseudo Commentaire
Hors ligne RyDroid # Posté le 02/07/2010 à 16:29:28
rayquaza devient RyDroid
Avatar

Ville : Reims
Pays : France métropolitaine

 Très peu.
Regarde, le Site du Zéro, Google, Wikipédia l'utilisent et pourtant ils en font des requêtes.
Nous avons eu la gentillesse de vous répondre, ayez la gentillesse de mettre votre topic en Image utilisateur résolu !

Si une âme charitable veut m'aider à résoudre mon problème sur Ubuntu 12.04, c’est par ici.
 
Hors ligne Nanom # Posté le 16/11/2010 à 10:59:34
Avatar

Avis : Très bon

Cour très pertinent et instructif, merci !

Note :
Secret (cliquez pour afficher)
19.99/20 car la perfection n'existe pas
Hors ligne matheod # Posté le 15/06/2011 à 18:38:21

Avatar

Avis : Très bon

Citation : Zed
Je n'y connaissais rien, il y a encore 5 minutes. Et j'étais loin d'apprécier ça.
En cinq minutes, j'ai compris le principe, les causes et les conséquences. Magnifique tuto, concis et très clair, sur un sujet important à priori pas des plus passionnant… BRAVO!

+1
Bon finalement j'avais réglé le tous comme il fallait par hasard mais maintenant je comprend beaucoup mieux comment tout cela marche.

Mais j'ai toujours un problème que je pensais pouvoir résoudre avec ce tutoriel :
Je n'ai aucun problème avec les accents lorsque ceux ci sont entrer sur mon site web, mais quand je passe par phpmyadmin (ajouter/modifier) je suis confronté aux problèmes d'accents décrit au début du tutoriel, c'est à dire que sur ma page web des ? sont affichés. Quand je regarde via phpmyadmin les entrés dans ma base de données, les é sont affiché comme ceci é (d'ailleur si je peux utiliser ce caractère par copier coller pour enregistrer des é mais ce n'est pas pratique).

Y a t il donc un moyen de spécifier à phpmyadmin sous quel encodage il doit fonctionner (aussi bien au niveau de l'enregistrement que de l'affichage) ?

Merci
Un jeux original qui change des mmorpg tout en en gardant le thème : League Of legend!

Un contrôle parentale sur le site du zero pour les admins ?
Image utilisateur
 
Hors ligne Kyle Katarn # Posté le 03/11/2011 à 14:25:42
Avatar

Avis : Très bon

C'est une excellente chose ce tuto, je commence un peu à m'user à force de répéter les mêmes choses à propos de l'encodage. Je me contenterai désormais de renvoyer les gens vers ce très bon tuto, clair et concis.

:soleil: et en favoris !
Image utilisateurMaîtriser les types de colonne SQL (tuto)
Image utilisateur Client FTP Image utilisateur SelfBuild Image utilisateur Holowar Image utilisateur Bmwx Image utilisateur StarPush
Image utilisateur Cinéma "Le Cercle" à Orbey Image utilisateur Chalets d'Orbey Image utilisateur Club ASCV
KyleK
 
Hors ligne bugatti # Posté le 17/02/2012 à 18:00:35

Avis : Très bon

très clair ce tuto, j'ai eu à le relire plus de 3 fois afin de mieux le comprendre et l'assimiler.
-------------------------------------------------------------------------------------------------
Merci Zéro

Voir tous les commentaires