Explications sur les CHARSET et COLLATION en MySQL 5

Intro

Qui ne s’est pas pris la tête pour que d’un part les tris tiennent compte ou pas de la casse et des accents, et d’autre part les recherches ?

L’affaire est loin d’être simple, mais je vous propose de l’éclaircir avec un script SQL qui réalise quelques exemples, plus parlants que de longues explications.

En bref, le charset et la collation peuvent être spécifiés au niveau du serveur, de la base, des tables et des colonnes : du plus général vers le plus précis.
Les charset les plus courants sont latin1 et utf8, du moins en Europe de l’ouest.
La collation dépend du charset. Son nom se compose ainsi : <charset>_<langage>_<suffixe>, le suffixe pouvant être « ci » pour CASE INSENSITIVE, « cs » pour CASE SENSITIVE, « bin » pour BINARY (c’est la valeur octale qui sera utilisée pour les tris et recherches).

Le script SQL

drop table if exists test_utf8_bin;
drop table if exists test_utf8_swedish_ci;
drop table if exists test_utf8_general_ci;
drop table if exists test_latin1_bin;
drop table if exists test_latin1_swedish_ci;

create table test_utf8_bin          (l varchar(10)) CHARACTER SET utf8 COLLATE utf8_bin;
create table test_utf8_swedish_ci   (l varchar(10)) CHARACTER SET utf8 COLLATE utf8_swedish_ci;
create table test_utf8_general_ci   (l varchar(10)) CHARACTER SET utf8 COLLATE utf8_general_ci;
create table test_latin1_bin        (l varchar(10)) CHARACTER SET latin1 COLLATE latin1_bin;
create table test_latin1_swedish_ci (l varchar(10)) CHARACTER SET latin1 COLLATE latin1_swedish_ci;

insert into test_utf8_bin          values ('émilie'), ('emilie'), ('Émilie'), ('Etan'), ('Étan'), ('etan'), ('étan'), ('etienne'), ('étienne'), ('Étienne'), ('Etienne');
insert into test_utf8_swedish_ci   values ('émilie'), ('emilie'), ('Émilie'), ('Etan'), ('Étan'), ('etan'), ('étan'), ('etienne'), ('étienne'), ('Étienne'), ('Etienne');
insert into test_utf8_general_ci   values ('émilie'), ('emilie'), ('Émilie'), ('Etan'), ('Étan'), ('etan'), ('étan'), ('etienne'), ('étienne'), ('Étienne'), ('Etienne');
insert into test_latin1_bin        values ('émilie'), ('emilie'), ('Émilie'), ('Etan'), ('Étan'), ('etan'), ('étan'), ('etienne'), ('étienne'), ('Étienne'), ('Etienne');
insert into test_latin1_swedish_ci values ('émilie'), ('emilie'), ('Émilie'), ('Etan'), ('Étan'), ('etan'), ('étan'), ('etienne'), ('étienne'), ('Étienne'), ('Etienne');

select * from test_utf8_bin          order by l; -- idem latin1_bin
select * from test_utf8_swedish_ci   order by l; -- idem latin1_swedish_ci
select * from test_utf8_general_ci   order by l; -- idem utf8_bin et latin1_bin
select * from test_latin1_bin        order by l; -- idem utf8_bin
select * from test_latin1_swedish_ci order by l; -- idem utf8_swedish_ci

select * from test_utf8_bin          where l='etienne'; -- idem latin1_bin
select * from test_utf8_swedish_ci   where l='etienne'; -- idem latin1_swedish_ci
select * from test_utf8_general_ci   where l='etienne'; --
select * from test_latin1_bin        where l='etienne'; -- idem utf8_bin
select * from test_latin1_swedish_ci where l='etienne'; -- idem utf8_swedish_ci

ALTER TABLE `test_utf8_bin` CHARACTER SET utf8 COLLATE utf8_swedish_ci;
select * from test_utf8_bin where l='etienne'; -- la conversion ne change rien, le résultat reste le meme

ALTER TABLE `test_utf8_swedish_ci` CHARACTER SET utf8 COLLATE utf8_bin;
select * from test_utf8_swedish_ci where l='etienne'; -- la conversion ne change rien, le résultat reste le meme

Et le résultat (je ne mets que l’essentiel) :

mysql> select * from test_utf8_bin          order by l;
+---------+
| l       |
+---------+
| Etan    |
| Etienne |
| emilie  |
| etan    |
| etienne |
| Émilie  |
| Étan    |
| Étienne |
| émilie  |
| étan    |
| étienne |
+---------+
11 rows in set (0.00 sec)

mysql> select * from test_utf8_swedish_ci   order by l; -- idem latin1_swedish_ci
+---------+
| l       |
+---------+
| émilie  |
| étan    |
| étienne |
| emilie  |
| etan    |
| Etan    |
| etienne |
| Etienne |
| Émilie  |
| Étan    |
| Étienne |
+---------+
11 rows in set (0.01 sec)

mysql>
mysql> select * from test_utf8_bin          where l='etienne'; -- idem latin1_bin
+---------+
| l       |
+---------+
| etienne |
+---------+
1 row in set (0.00 sec)

mysql> select * from test_utf8_swedish_ci   where l='etienne'; -- idem latin1_swedish_ci
+---------+
| l       |
+---------+
| etienne |
| Etienne |
+---------+
2 rows in set (0.00 sec)

mysql>
mysql> ALTER TABLE `test_utf8_bin` CHARACTER SET utf8 COLLATE utf8_swedish_ci;
Query OK, 11 rows affected (0.07 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from test_utf8_bin where l='etienne'; -- la conversion ne change rien, le résultat reste le meme
+---------+
| l       |
+---------+
| etienne |
+---------+
1 row in set (0.00 sec)

mysql>
mysql> ALTER TABLE `test_utf8_swedish_ci` CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 11 rows affected (0.06 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from test_utf8_swedish_ci where l='etienne'; -- la conversion ne change rien, le résultat reste le meme
+---------+
| l       |
+---------+
| etienne |
| Etienne |
+---------+
2 rows in set (0.01 sec)

Conclusion :

  • en binaire, les caractères sont tous distincts : les E majuscules et minuscules d’abord, puis les É majuscules et minuscules.
  • avec une collation swedish ou general (contient tous les caractères de l’Europe de l’ouest), les caractères accentués posent encore problèmes, ils sont traités comme distincts : d’abord les é puis les e/E puis les É.

Quelques ressources :

mysql> select * from test_utf8_bin          order by l; — idem latin1_bin
+———+
| l       |
+———+
| Etan    |
| Etienne |
| emilie  |
| etan    |
| etienne |
| Émilie  |
| Étan    |
| Étienne |
| émilie  |
| étan    |
| étienne |
+———+
11 rows in set (0.00 sec)

mysql> select * from test_utf8_swedish_ci   order by l; — idem latin1_swedish_ci
+———+
| l       |
+———+
| émilie  |
| étan    |
| étienne |
| emilie  |
| etan    |
| Etan    |
| etienne |
| Etienne |
| Émilie  |
| Étan    |
| Étienne |
+———+
11 rows in set (0.01 sec)

mysql> select * from test_utf8_general_ci   order by l; — idem utf8_bin et latin1_bin
+———+
| l       |
+———+
| emilie  |
| etan    |
| Etan    |
| etienne |
| Etienne |
| Émilie  |
| Étan    |
| Étienne |
| émilie  |
| étan    |
| étienne |
+———+
11 rows in set (0.00 sec)

mysql> select * from test_latin1_bin        order by l; — idem utf8_bin
+———+
| l       |
+———+
| Etan    |
| Etienne |
| emilie  |
| etan    |
| etienne |
| émilie  |
| étan    |
| étienne |
| Émilie  |
| Étan    |
| Étienne |
+———+
11 rows in set (0.00 sec)

mysql> select * from test_latin1_swedish_ci order by l; — idem utf8_swedish_ci
+———+
| l       |
+———+
| emilie  |
| etan    |
| Etan    |
| etienne |
| Etienne |
| émilie  |
| étan    |
| étienne |
| Émilie  |
| Étan    |
| Étienne |
+———+
11 rows in set (0.00 sec)

mysql>
mysql> select * from test_utf8_bin          where l=’etienne’; — idem latin1_bin
+———+
| l       |
+———+
| etienne |
+———+
1 row in set (0.00 sec)

mysql> select * from test_utf8_swedish_ci   where l=’etienne’; — idem latin1_swedish_ci
+———+
| l       |
+———+
| etienne |
| Etienne |
+———+
2 rows in set (0.00 sec)

mysql> select * from test_utf8_general_ci   where l=’etienne’; —
+———+
| l       |
+———+
| etienne |
| Etienne |
+———+
2 rows in set (0.00 sec)

mysql> select * from test_latin1_bin        where l=’etienne’; — idem utf8_bin
+———+
| l       |
+———+
| etienne |
+———+
1 row in set (0.00 sec)

mysql> select * from test_latin1_swedish_ci where l=’etienne’; — idem utf8_swedish_ci
+———+
| l       |
+———+
| etienne |
| Etienne |
+———+
2 rows in set (0.00 sec)

mysql>
mysql> ALTER TABLE `test_utf8_bin` CHARACTER SET utf8 COLLATE utf8_swedish_ci;
Query OK, 11 rows affected (0.07 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from test_utf8_bin where l=’etienne’; — la conversion ne change rien, le résultat reste le meme
+———+
| l       |
+———+
| etienne |
+———+
1 row in set (0.00 sec)

mysql>
mysql> ALTER TABLE `test_utf8_swedish_ci` CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 11 rows affected (0.06 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from test_utf8_swedish_ci where l=’etienne’; — la conversion ne change rien, le résultat reste le meme
+———+
| l       |
+———+
| etienne |
| Etienne |
+———+
2 rows in set (0.01 sec)