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.
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).
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).
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.
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.
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).
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 :
SHOW CHARACTER SET
Et pour les interclassements :
SHOW COLLATION
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.
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...).
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) :
SELECT "éEe" = "eee"
Tandis que celle-ci retournera 0 (faux) :
SELECT "éEe" = BINARY "eee"
Notez que j'aurais pu mettre BINARY de n'importe quel côté de la comparaison.
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 :
CREATE DATABASE base_de_donnees [...] CHARACTER SET jeu_de_caracteres COLLATE interclassement
ALTER DATABASE base_de_donnees CHARACTER SET jeu_de_caracteres COLLATE interclassement
pour une table :
CREATE TABLE table [...] CHARACTER SET jeu_de_caracteres COLLATE interclassement
ALTER TABLE table CHARACTER SET jeu_de_caracteres COLLATE interclassement
pour une colonne :
CREATE TABLE table (
colonne [...] CHARACTER SET jeu_de_caracteres COLLATE interclassement
[...]
)
[...]
ALTER TABLE table CHANGE colonne colonne type CHARACTER SET jeu_de_caracteres COLLATE interclassement
Notez que la requête suivante permet de convertir toutes les colonnes de la table à un jeu de caractères précis :
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 :
ALTER TABLE table CHANGE colonne colonne type_binaire;
ALTER TABLE table CHANGE colonne colonne type_texte CHARACTER SET jeu_de_caracteres;
Par exemple :
ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
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 :
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 :
SET NAMES jeu_de_caracteres
Qui a le même effet que :
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.
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.