WampServer

MySQL, bases de données, tables, colonnes, jeux de caractères (charset) et interclassement (collation)

Un jeu de caractères (character set ou charset) est un ensemble de lettres, signes de ponctuations et autres symboles auquels on a associè un numéro de code.
Par exemple, le jeu « standard ASCII » (American Standard Code for Information Interchange) donne le numéro 65 au A majuscule, le 44 à la virgule et le 13 au retour chariot. C'est un jeu créé pour la langue anglaise, et qui ne comporte que 128 caractères. Il ignore donc les caractères accentués, les ligatures, le signe euro et, bien sûr, les caractères grecs, arabes ou japonais.

Remarque : un « jeu de caractères » n'est pas une « police de caractères » (character font) : celle-ci associe à chaque caractère un « glyphe », c'est-à-dire une représentation graphique. Ainsi, les lettres A, A et A sont-elles trois glyphes du même caractère ASCII 65.

L'information est codée informatiquement sous forme de bits, valant chacun zéro ou un. Une série ordonnée de 8 bits est un octet (byte), qui peut donc avoir 2^8 combinaisons différentes de 1 et de 0, numérotées de 0 à 255. Avec le code ASCII, puis ses extensions, un octet suffit pour enregistrer un caractère.
Ce principe « un octet = un caractère » a très longtemps prédominé. Il a été décliné en de nombreux jeux de 256 caractères (certains reprenant l'ASCII pour les 128 premières positions et complétant les 128 suivantes). La série des jeux de caractères normalisés ISO 8859 fonctionne de cette manière. L'ISO 8859-1, également appelé latin-1 ou occidental (western) comprend (presque) tous les caractères nécessaires pour écrire une vingtaine de langues d'Europe occidentale comme le français et l'anglais, tandis que l'ISO 8859-6 fournit les caractères arabes et que l'ISO 8859-15 (latin-9) est une version réactualisée du latin-1 comprenant, entre autres, le caractère euro et la ligature oe.

Unicode a pour but de fournir un jeu de caractères unique pour représenter toutes les langues. Cela permet notamment de gérer des textes multi-alphabétiques, par exemple ceux d'un dictionnaire français-arabe. Comme cela nécessite beaucoup plus de 256 caractères, les jeux Unicode utilisent plusieurs octets. Pour rester comptabible avec les jeux mono-octet, Unicode permet d'utiliser des caractères de taille variable, de 1 à 4 octets pour le jeu UTF-8, potentiellement plus de 4 milliards de caractères différents.

Remarque : Si, à cause de mauvais paramétrages, un caractère Unicode codé sur plusieurs octets est interprété comme mono-octet, il sera représenté par plusieurs caractères, d'où par exemple la transformation du é en A©.

MySQL vous permet d'utiliser différents jeux de caractères, mono-octet et multi-octets.

Quelques commandes pour interroger la liste des jeux installés sur votre serveur MySQL, à utiliser, par exemple, dans une fenêtre SQL de PhpMyAdmin :
- Liste de tous les jeux de caractères disponibles sur votre MySQL
SHOW CHARACTER SET ;
- Liste des jeux de caractères fondés sur l'alphabet latin
SHOW CHARACTER SET LIKE 'latin%' ;
- Liste des jeux de caractères multi-octets
SELECT *
FROM Information Schema.Character_Sets
WHERE MaxLen > 1;

En plus du jeu de caractère, MySQL permet de choisir comment les données seront classées (ORDER BY) par ce qui est appelé une « collation » (COLLATE). Ceci permet de répondre, par exemple, au problème classique de la sensibilité à la casse :
- Les majuscules doivent-elles précéder les minuscules, ou bien faut-il considérer A et a comme de même valeur ?
- La sensibilité aux accents : comptent-ils dans le tri ? Font-ils une différence lors de la recherche ?
- La possibilité qu'un caractère (ligature oe) puisse correspondre à plusieurs (o suivi de e) : c'est ce qu'Unicode appelle « l'expansion ».
C'est pour formaliser ces choix qu'ont été inventées les collations (Le terme anglais signifie à la fois « rassemblement » et « comparaison » ; collation est parfois traduit par « interclassement »). Une collation est liée à un jeu de caractères. Elle donne à la fois l'ordre dans lequel classer les caractères, et si certains d'entre eux doivent être considérés comme équivalents. Chaque jeu de caractère possède plusieurs collations, dont une par défaut.
Quelques commandes pour interroger la liste des collation installées sur votre serveur MySQL, à utiliser, par exemple, dans une fenêtre SQL de PhpMyAdmin :
- Liste des collations du jeu latini
SHOW COLLATION LIKE 'latinl%' ;
- Liste des collations du jeu utf8
SHOW COLLATION LIKE 'utf8%' ;

Toutes les collations ont un nom qui commence par le jeu de caractère auquel elles sont liées, et se terminent par l'une de ces trois abréviations :
- _bin comme binary : les caractères sont dans l'ordre de leurs numéros de code (ce qui donne d'abord toutes les majuscules, puis toutes les minuscules, puis les lettres accentuées, en vrac).
- _cs comme case sensitive : les caractères sont triés selon le ou les langages de référence, mais de manière sensible à la casse.
- _ci comme case insensitive : idem, mais en ignorant la casse.

Pour que vous puissiez faire les choix qui conviennent, il faut explicitement déclarer le jeu de caractère et la collation utilisés jusqu'au niveau de la colonne (Champ), sinon, ce sont les valeurs par défaut du niveau supérieur qui s'appliquent : Les données utilisent le jeu de caractères et la collation de leur colonne. Si le jeu et la collation n'ont pas été spécifiés pour la colonne, ce sont ceux de la table. Si ceux de la table n'ont pas été spécifiés, ce sont ceux de la base, et si ceux de la base n'ont pas non plus été spécifiés, ce sont le jeu et la collation par défaut du serveur. Voilà comment on se retrouve à avoir, presque partout, latin1_swedish_ci comme collation, MySQL AB étant une société suédoise.

- Changer le jeu de caractères et la collation aux niveaux supérieurs (serveur, base ou table) n'a pas de conséquence immédiate : seuls les nouveaux objets créés utiliseront ces valeurs par défaut.
- Changer la collation d'une colonne, en restant dans le même jeu de caractères, se fait sans difficulté. En effet, cela n'affecte pas les données elles-mêmes, mais la façon dont elles sont traitées. Lors du prochain Select, les tris et recherches se comportent différemment.
- Changer le jeu de caractères d'une colonne convertit les données vers le nouveau jeu. Cela peut poser un problème si certains caractères de l'ancien jeu n'ont pas d'équivalent dans le nouveau jeu.

Le jeu de caractères et la collation des requêtes

Ces valeurs dépendent des paramètres du client MySQL.
À chaque ouverture de sessions, MySQL renseigne quatre variables système :

- Le jeu de caractères que le client utilise en saisie, cette indication est enregistrée dans la variable @@character_set_client.
- Le jeu de caractères utilisé pour la communication entre le client et MySQL (@@character_set_connection) : la collation par défaut de ce jeu de caractères détermine la @@collation_connection.
- Le jeu de caractères utilisé pour afficher le résultat des requêtes dans le client (@@character_set_results).

Le texte de la requête est interprété selon le jeu du client, puis converti dans le jeu de la connexion (@@character_set_connection et @@collation_connection). MySQL envoie ensuite le résultat en utilisant à nouveau le @@characterset_connection, puis en le convertissant ensuite en @@character_set_results.
Pour connaître la valeur des variables système liées aux jeux et collations, lancer les requêtes suivantes :
- Affichage de l'ensemble des variables systèmes liées aux jeux de caractères et collations :
SHOW VARIABLES LIKE 'char%';
SHOW VARIABLES LIKE 'colla%'';

Outre les quatre variables citées ci-dessus, on retrouvera les jeux et collations par défaut du serveur et de la base de données en cours. Quant au @@character_set_system, c'est celui que MySQL utilise pour enregistrer les métadonnées, c'est-à-dire les noms des bases, tables, colonnes, etc. Il s'agit toujours d'utf8.

Sur un serveur local, par exemple sous Windows avec Wampserver, les valeurs par défaut POURRAIENT être modifiées dans le fichier my.ini :
# CLIENT SECTION
[client]
[mysql]
default-character-set=latin1
# SERVER SECTION
[mysqld]
#Jeu de caractères par défaut utilisé lors de la création de tables
#lorsque celui-ci n'est pas explicitement défini.
default-character-set=latin1
Mais ce n'est pas le cas : Avec Wampserver aucune définition d'un jeu de caractères n'est effectuée pour toutes les versions de MySQL.

Sous Windows 10 avec dans le fichier my.ini AUCUNE définition d'un jeu de caractère par défaut, voici les résultats des commandes montrant le contenu des variables système jeu de caractères et collations dans la console MySQL :
- 1 - PHP 8.2.7 et MySQL 8.0.33

mysql> SHOW VARIABLES LIKE 'CHAR%';
+--------------------------+-------------------------------------------------+
| Variable_name            | Value                                           |
+--------------------------+-------------------------------------------------+
| character_set_client     | cp850                                           |
| character_set_connection | cp850                                           |
| character_set_database   | utf8mb4                                         |
| character_set_filesystem | binary                                          |
| character_set_results    | cp850                                           |
| character_set_server     | utf8mb4                                         |
| character_set_system     | utf8mb3                                         |
| character_sets_dir       | E:\wamp64\bin\mysql\mysql8.0.33\share\charsets\ |
+--------------------------+-------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)

mysql> SHOW VARIABLES LIKE 'colla%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | cp850_general_ci   |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set, 1 warning (0.00 sec)

- 2 - PHP 8.2.7 et MySQL 5.7.42

mysql> SHOW VARIABLES LIKE 'CHAR%';
+--------------------------+-------------------------------------------------+
| Variable_name            | Value                                           |
+--------------------------+-------------------------------------------------+
| character_set_client     | cp850                                           |
| character_set_connection | cp850                                           |
| character_set_database   | latin1                                          |
| character_set_filesystem | binary                                          |
| character_set_results    | cp850                                           |
| character_set_server     | latin1                                          |
| character_set_system     | utf8                                            |
| character_sets_dir       | E:\wamp64\bin\mysql\mysql5.7.42\share\charsets\ |
+--------------------------+-------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

mysql> SHOW VARIABLES LIKE 'colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | cp850_general_ci  |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set, 1 warning (0.00 sec)

On peut déjà noter qu'il y a des différences dans les jeux de caratères par défaut entre MySQL 5.7 et MySQL 8.0

Nota : utf8mb4 c'est-à-dire le support Unicode V6 des caractères utf-8 sur quatre octets n'est possible que depuis MySQL 5.5.3.

--- Éventuels problèmes avec la console MySQL
L'utilisation de la console MySQL texte sous Windows peut être "vicieux" et réserver des surprises !
Le jeu de caractère du client texte, tout en étant déclaré latin1 est en fait cp850 (Enfin, pas toujours, ça dépend des API).

Pour s'en convaincre, il suffit de lancer dans une console MySQL :
SHOW VARIABLES LIKE 'char%';
USE test;
Puis choisir une champ d'une table CHARSET=latin1 et comportant des caractères accentués.
Pour cela j'utilise une table test avec un champ test (latin1) contenant "éèàù ÉÈÀÙ ç Ç" et rempli avec PhpMyAdmin, donc en "vrai" latin1.
SELECT test FROM test;
Les "é" sont transformés en "ù" et les autres caractères en ÚÞÓ¨ ++++ þ Ã
Si le jeu de caractères de la console était bien latin1, cela ne devrait pas arriver.
SET NAMES cp850;
Et refaire le select. Les caractères accentués sont bons.

Un bon moyen de voir quel est le jeu de caractères de la console est de lancer une requête est d'indiquer à MySQL quel est le jeu de caractère d'une chaîne littérale en la faisont précéder d'un introducteur :

SELECT _latin1'été', _utf8'été', _cp850'été';

Une chaîne littérale précédée d'un introducteur est envoyée telle quelle à MySQL, quelles que soient les variables @@character_set_client et @@character_set_connexion, MySQl l'interprète selon le jeu indiqué par l'introducteur, puis la renvoie.

--- Règles relatives aux noms des « objets » MySQL : bases, tables, colonnes, etc.
- Longueur de 64 caractères maximum.
- Certains caractères (en particulier l'espace) y sont soit interdits, soit déconseillés ; pour éviter tous les problèmes, n'utilisez pas les caractères diacritiques, utilisez les lettres de a à z, les chiffres et le tiret de soulignement ou underscore (_) ; ne commencez pas par un chiffre et adoptez une politique de casse cohérente et commune à tous vos objets. (À mon humble avis, le plus simple est le « tout minuscules », mais vous pouvez adopter le type « Nom Propre », c'est-à-dire la première lettre en majuscule).
- Ils ne doivent pas être identiques â des termes du langage SQL, comme Create, Use, Select, Join, etc.

--- Majuscules, minuscules ---
Les règles relatives à la casse (c'est-à-dire le choix entre majuscules et minuscules) changent avec le système d'exploitation :
- Les mots-clés du langage SQL et les noms de colonnes sont insensibles à la casse, c'est-à-dire qu'ils peuvent s'écrire indifféremment en majuscules ou en minuscules.
- Les noms des tables et des bases de données sont :
-- sensibles à la casse si MySQL est installé sur Linux
-- insensibles si MySQL est installé sur Windows
Cette différence est due au fait que MySQL enregistre bases et tables sous forme de fichiers ; or, Windows est insensible à la casse des noms de fichiers, tandis que Linux y est sensible.

Comme on peut changer d'hébergeur et donc, changer de système d'exploitation, il est préférable de toujours considérer les noms des objets MySQL comme sensibles, afin que les requêtes fonctionnent quel que soit le système d'exploitation.

En employant les mêmes règles pour les noms de variables et fichiers PHP, on s'affranchit d'erreurs potentielles.

MySQL permet de déclarer explicitement les jeux de caractères utilisés pour les bases, tables et colonnes. Différents jeux peuvent très bien cohabiter au sein d'une même base de données ou d'une table :

DROP TABLE IF EXISTS table_test;
CREATE TABLE IF NOT EXISTS table_test (
  champ1 varchar(50) COLLATE latin1_general_ci NOT NULL,
  champ2 varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  champ3 varchar(50) CHARACTER SET latin2 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Il est tout-à-fait possible et même recommandé, tout en ayant des bases et tables déclarées latin1 (iso-8859-1) d'utiliser, pour les exportations et importations, des fichiers avec jeu de caractère utf-8 ; c'est d'ailleurs, (presque) toujours, le jeu de caractère par défaut des connexions MySQL.

Note 1 : Caractères UTF-8 composés de quatre octets

Le jeu de caractères actuellement nommé utf8 utilise un maximum de trois octets par caractère et ne contient que des caractères BMP : Basic Multilingual Plane, c'est-à-dire les 65534 premiers codes (0x0000 à 0xFFFD) du standard UCS (ISO 10646) qui définit le Universal Character Set (UCS).

UCS contient tous les caractères de tous les autres jeux de caractères standards. Il garantit également une compatibilité circulaire, ce qui signifie que les tables de conversions permettent de ne perdre aucune information quand une chaîne de caractères est convertie dans un autre codage, puis reconvertie en sens inverse.

UCS contient les caractères nécessaires pour représenter presque tous les langages connus. En plus des langues utilisant une extension de l'alphabet latin, il inclut : Grec, Cyrillique, Hébreu, Arabe, Arménien, Grégorien, Japonais, Chinois, Hiragana, Katakana, Coréen, Hangul, Devangari, Bengali, Gurmukhi, Gujarati, Oriya, Tamil, Telugu, Kannada, Malayam, Thai, Lao, Bopomofo, et d'autres encore. Depuis MySQL 5.5.3 sont supporté les extensions Unicode aux caractères BMP : les caractères utf8 composés de quatre octets, c'est-à-dire les codes du standard UCS (ISO 10646) supérieurs à 65534 (> 0xFFFF) et ce support se nomme utf8mb4.
- l'ensemble utf8mb4 utilise un maximum de quatre octets par caractère et supporte les caractères complémentaires, par exemple ceux de Unicode version 6, pour peu qu'une police de caractère (Fonte) dispose des glyphes associés.
- l'ensemble utf8mb3 est un alias de utf8 et permet de différentier plus visiblement le codage à trois octets de celui à quatre octets.

Néanmoins, pour les commandes telles que SHOW, CREATE TABLE or SELECT l'alias utf8mb3 est systématiquement convertit en utf8.

Pour un caractère BMP, utf8 et utf8mb4 fonctionnent de manière identique : même code, même encodage, même longueur (Nombre d'octets), même façon d'enregistrer dans la base de données.

Pour les caractères supplémentaires, par exemple Unicode version 6, utf8 ne peut pas stocker le caractère et générera une erreur car utf8mb4 nécessite quatre octets. Donc, si on veut supporter les caractères uft8 à quatre octets, il est impératif de le déclarer explicitement pour les bases, tables et colonnes le nécessitant.
Si une table avait été précédemment créée pour supporter utf8, par exemple :

CREATE TABLE t1 (
  col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  col2 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
) CHARACTER SET utf8;

La requête suivante permettra, sans aucune perte de données, de transcoder la table pour qu'elle accepte les caractères utf8 à quatre octets Il est indispensable de préciser le nom des colonnes à transcoder)

ALTER TABLE t1
  DEFAULT CHARACTER SET utf8mb4,
  MODIFY col1 CHAR(10)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  MODIFY col2 CHAR(10)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

Plus simplement, on peut utiliser :

ALTER TABLE t1
  CONVERT TO CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci (ou utf8mb4_bin);

Sans être obligé de donner les noms des colonnes ; seules les colonnes nécessitant d'être transcodées le seront automatiquement.

Pour que MySQL accepte les requêtes contenant des données avec des caractères utf8 à quatre octets, il est impératif de la déclarer explicitement, donc d'envoyer la commande :

SET NAMES utf8mb4;

au lieu de

SET NAMES utf8;

avant les requêtes.



Modifie 3 fois. Derniere modification le 16/06/2023 à 11:15 par Otomatic.

Un pti up pour ne pas perdre ce précieux topo

Florian

WampServer Patch (Screenshots)
Topic EN : www.wampserver.com
Topic FR : www.wampserver.com