Aller au contenu


Tutoriel: MySQL et les COLLATE

Voila un petit tutoriel MySQL qui vous permettra de comprendre, je l’espère, l’utilité du COLLATE lors de la création de vos tables.

Problème

Vous avez développé un moteur de recherche qui interroge une base de données MySQL.  Or, lorsque vous effectuez des requêtes avec des termes accentués, MySQL vous retourne également les résultats non-accentués.  Dans certains cas, ce peut-être très utile.  Ceci dit, quelle en est la raison?

Collation = comparaison et regroupement

MySQL (>=4.1.3) se base sur le COLLATE du champ à comparer pour déterminer le type de comparaison qu’il va effectuer.   Une collation (COLLATE) détermine les caractères qui doivent être regroupés et considérés comme équivalents (ex: les caractères accentués ‘à’ et ‘â’ faisant partie du groupe ‘a’, ou encore les caractères ‘A’ et ‘a’.).
Vous déterminez la collation lors de la création de votre table, via le mot-clé COLLATE, ou lors de la création du champ dans cette table (si vous désirez un collation différente pour chacun des champs).

Quelques exemples de COLLATION:
* Dans le charset latin1:
– latin1_swedish_ci (probablement la plus utilisée au monde, étant la collation par défaut de MySQL dans le charset latin1)
– latin1_bin
* Dans le charset utf-8:
– utf8_bin
– utf8_general_ci
… et j’en passe.

Suivant la collation que vous aurez choisie, les comparaisons seront ainsi sensibles (ou non) à la casse et aux accents.
Par exemple, latin1_bin sera sensible à la casse et aux accents tandis que latin1_general_ci ne le sera pas.

Remarque:
Il est à préciser que si vous créez votre table avec une certaine collation, et que vous la changez ensuite (sans utiliser le ALTER TABLE…CONVERT), la collation des champs déjà créés dans cette table ne changera pas, seuls les nouveaux éléments utiliseront la nouvelle collation.  Ce qui pourrait vous induire en erreur…

Testons!

Prenons la table suivante, tout ce qu’il y a de plus banal:

mysql> CREATE TABLE `contacts` (
`lastname` VARCHAR(100) DEFAULT '',
`firstname` VARCHAR(100) DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 ROWS affected (0.02 sec)
 
mysql> INSERT INTO contacts VALUES ('andre','ludovic');
Query OK, 1 ROW affected (0.00 sec)
 
mysql> INSERT INTO contacts VALUES ('Andre','Ludovic');
Query OK, 1 ROW affected (0.00 sec)

=> COLLATE non spécifié, les champs auront donc comme collation: latin1_swedish_ci (vu que nous sommes dans le charset latin1).

Pour s’en assurer:

mysql> SHOW FULL COLUMNS FROM contacts;
+-----------+--------------+-------------------+------+-----+--------...
| FIELD     | TYPE         | Collation         | NULL | KEY | DEFAULT
+-----------+--------------+-------------------+------+-----+--------...
| lastname  | VARCHAR(100) | latin1_swedish_ci | YES  |     |
| firstname | VARCHAR(100) | latin1_swedish_ci | YES  |     |
+-----------+--------------+-------------------+------+-----+--------...
2 ROWS IN SET (0.15 sec)

Tentons notre recherche (les tests ci-dessous sont basés sur la problématique case-sensitive, mais sont tout aussi valables pour celle accents-sensitive):

mysql> SELECT * FROM contacts WHERE lastname = 'andre';
+----------+-----------+
| lastname | firstname |
+----------+-----------+
| andre    | ludovic   |
| Andre    | Ludovic   |
+----------+-----------+
2 ROWS IN SET (0.00 sec)

Les résultats ne sont pas case-sensitive.  Tout à fait normal donc, car comme expliqué plus haut, la collation latin1_swedish_ci n’est pas case-sensitive.

Il est possible de forcer la collation d’un champ lors de la query, pour passer en mode case-sensitive sur un champ spécifique:

mysql> SELECT * FROM contacts WHERE lastname COLLATE latin1_bin = 'andre';
+----------+-----------+
| lastname | firstname |
+----------+-----------+
| andre    | ludovic   |
+----------+-----------+
1 ROW IN SET (0.00 sec)

Pour éviter de devoir le faire à chaque requête de comparaison, on peut évidemment modifier la collation du champ lastname:

mysql> ALTER TABLE contacts MODIFY lastname VARCHAR(100) COLLATE latin1_bin;
Query OK, 2 ROWS affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SHOW FULL COLUMNS FROM contacts;
+-----------+--------------+-------------------+------+-----+--------...
| FIELD     | TYPE         | Collation         | NULL | KEY | DEFAULT
+-----------+--------------+-------------------+------+-----+--------...
| lastname  | VARCHAR(100) | latin1_bin        | YES  |     | NULL
| firstname | VARCHAR(100) | latin1_swedish_ci | YES  |     |
+-----------+--------------+-------------------+------+-----+--------...
2 ROWS IN SET (0.00 sec)

On peut également convertir tous les champs d’un seul coup, ainsi que la table elle-même:

mysql> ALTER TABLE contacts CONVERT TO CHARSET latin1 COLLATE latin1_bin;
Query OK, 2 ROWS affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SHOW CREATE TABLE contacts;
+----------+-----------------------------------------------------+
| TABLE    | CREATE TABLE                                                                                                            |
+----------+-----------------------------------------------------+
| contacts | CREATE TABLE `contacts` (
`lastname` VARCHAR(100) COLLATE latin1_bin DEFAULT NULL,
`firstname` VARCHAR(100) COLLATE latin1_bin DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
+----------+-----------------------------------------------------+
1 ROW IN SET (0.00 sec)
 
mysql> SHOW FULL COLUMNS FROM contacts;
+-----------+--------------+------------+------+-----+--------...
| FIELD     | TYPE         | Collation  | NULL | KEY | DEFAULT |
+-----------+--------------+------------+------+-----+--------...
| lastname  | VARCHAR(100) | latin1_bin | YES  |     | NULL    |
| firstname | VARCHAR(100) | latin1_bin | YES  |     |         |
+-----------+--------------+------------+------+-----+--------...
2 ROWS IN SET (0.00 sec)

Comme mentionné dans l’introduction de cet article, il faut obligatoirement effectuer un ALTER TABLE…CONVERT pour que MySQL prenne en charge la modification du COLLATE des champs dans cette table. Sans quoi, via un ALTER TABLE CHARSET latin1 COLLATE latin1_bin, seul le COLLATE de la table sera modifié.

Voila, j’espère que vous y voyez un peu plus clair sur l’usage de ce COLLATE, bien que je ne sois pas rentré dans les détails très techniques, mais cela constitue une base suffisante pour en comprendre l’usage.  Je vous conseille également de lire ce très bon PDF, qui vous en apprendra encore d’avantage!

A partir de là, vous pourrez vous aventurer dans la problématique du CHARSET (jeu de caractères), encore plus vaste 😉

Ressources

Quelques tableaux décrivants les regroupements de caractères pour certaines collations
De la doc MySQL sur les charset et les collations
Un tutoriel détaillé, en PDF, sur les charset et les collations

Posté dans développement, Web. Tags , .

1 commentaire

  1. Olivier Appéré dit

    Bonjour,
    A propos d’accents, j’ai un soucis
    J’ai un table ‘ma_table’ avec un interclassement latin1_swedish_ci
    et un champs ‘name’ avec une collation latin1_swedish_ci également.
    J’ai une entrée ‘Andre’
    Lorsque je lance la commande
    SELECT * FROM ma_table WHERE name %LIKE% ‘André’
    Je trouve ‘Andre’ ce qui me plait.
    Mais quand je lance la commande
    SELECT * FROM ma_table WHERE name REGEXP ‘^André$’
    Je ne trouve plus rien.
    Pourquoi ?

Quelques tags HTML sont acceptés

(obligatoire)

(obligatoire, mais ne sera pas diffusé)

ou faire un rétrolien depuis votre site.