WampServer
Apache, PHP, MySQL
on Windows
Aller à : Liste des Forums•Liste des messages•Nouvelle discussion•Recherche•Connexion•Imprimer la vue
[topo] MySQL et jeux de caractères
Envoyé par:
Otomatic
(---.fbx.proxad.net)
Date: 09 June 2009 à 14:07
MySQL, jeux de caractères et collation
Un jeu de caractères (character set) 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 et A sont-elles deux 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 œ.
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 avec Wampserver ou EasyPHP, les valeurs par défaut peuvent ê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
Sous Windows XP SP3 avec PhpMyAdmin 3.1.5 et MySQL 5.1.34 avec, dans le fichier my.ini les valeurs ci-dessus :
SHOW VARIABLES LIKE 'char%';
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
SHOW VARIABLES LIKE 'colla%';
collation_connection utf8_unicode_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
--- É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 que les lettres sans accent, 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.
Modifie 1 fois. Derniere modification le 31/01/2019 à 14:55 par Otomatic.
Un jeu de caractères (character set) 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 et A sont-elles deux 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 œ.
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 avec Wampserver ou EasyPHP, les valeurs par défaut peuvent ê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
Sous Windows XP SP3 avec PhpMyAdmin 3.1.5 et MySQL 5.1.34 avec, dans le fichier my.ini les valeurs ci-dessus :
SHOW VARIABLES LIKE 'char%';
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
SHOW VARIABLES LIKE 'colla%';
collation_connection utf8_unicode_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
--- É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 que les lettres sans accent, 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.
Modifie 1 fois. Derniere modification le 31/01/2019 à 14:55 par Otomatic.
Re: [topo] MySQL et jeux de caractères
Envoyé par:
toumimi
(---.111.116-78.rev.gaoland.net)
Date: 11 December 2009 à 14:14
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
Florian
WampServer Patch (Screenshots)
Topic EN : www.wampserver.com
Topic FR : www.wampserver.com
Désolé, seuls les utilisateurs connectés peuvent envoyer des messages dans ce forum.
