Un guide des chaînes dans MySQL

Les chaînes sont l’un des types de données les plus courants que vous utiliserez dans MySQL. De nombreux utilisateurs insèrent et lisent des chaînes dans leurs bases de données sans trop y penser. Cet article a pour but de vous donner un aperçu de la façon dont MySQL stocke et affiche vos variables de chaîne afin que vous puissiez avoir un meilleur contrôle sur vos données.

Vous pouvez diviser les chaînes en deux catégories : binaires et non binaires. Vous pensez probablement aux chaînes non binaires la plupart du temps. Les chaînes non binaires ont des jeux de caractères et des classements. Les chaînes binaires, en revanche, stockent des éléments tels que des fichiers MP3 ou des images. Même si vous stockez un mot dans une chaîne binaire, telle que chansonil n’est pas stocké de la même manière que dans une chaîne non binaire.

Je vais me concentrer sur les chaînes non binaires. Toutes les chaînes non binaires dans MySQL sont associées à un jeu de caractères et à un classement. Le jeu de caractères d’une chaîne contrôle les caractères pouvant être stockés dans la chaîne, et son classement contrôle l’ordre des chaînes lorsque vous les affichez.

Jeux de caractères

Pour afficher les jeux de caractères sur votre système, exécutez la commande suivante :

SHOW CHARACTER SET;

Cette commande affichera quatre colonnes de données, y compris le jeu de caractères :

• Nom
• Brève description
• Classement par défaut
• Taille maximale de chaque caractère dans le jeu de caractères

MySQL utilisé par défaut pour le latin1 jeu de caractères, mais depuis la version 8.0, la valeur par défaut est utf8mb4. Le classement par défaut est maintenant utf8mb4_0900_ai_ci. Les ai indique que ce classement est insensible aux accents (á = un), et le ci spécifie qu’il est insensible à la casse (un = un).

Différents jeux de caractères stockent leurs caractères dans des blocs de mémoire de différentes tailles. Par exemple, comme vous pouvez le voir dans la commande ci-dessus, les caractères stockés dans utf8mb4 sont stockés en mémoire d’une taille de un à quatre octets. Si vous voulez voir si une chaîne contient des caractères multi-octets, vous pouvez utiliser le CHAR_LENGTH() et LONGUEUR() les fonctions. CHAR_LENGTH() affiche le nombre de caractères contenus dans une chaîne, tandis que LONGUEUR() indique le nombre d’octets d’une chaîne, qui peut ou non être identique à la longueur d’une chaîne en caractères, selon le jeu de caractères. Voici un exemple:

SET @a = CONVERT('data' USING latin1);
SELECT LENGTH(@a), CHAR_LENGTH(@a);
+------------+-----------------+
| LENGTH(@a) | CHAR_LENGTH(@a) |
+------------+-----------------+
|     4      |       4         |
+------------+-----------------+

Cet exemple montre que le latin1 jeu de caractères stocke les caractères dans des unités à un octet. D’autres jeux de caractères, tels que utf16autoriser les caractères multioctets :

SET @b = CONVERT('data' USING utf16);
SELECT LENGTH(@b), CHAR_LENGTH(@b);
+------------+------------------+
| LENGTH(@b) | CHAR_LENGTH(@b)  |
+------------+------------------+
|       8    |        4         |
+------------+------------------+

Collation

Le classement d’une chaîne déterminera comment les valeurs sont affichées lorsque vous exécutez une instruction SQL avec un COMMANDÉ PAR clause. Votre choix de classements est déterminé par le jeu de caractères que vous sélectionnez. Lorsque vous avez exécuté la commande SHOW CHARACTER SET ci-dessus, vous avez vu les classements par défaut pour chaque jeu de caractères. Vous pouvez facilement voir tous les classements disponibles pour un jeu de caractères particulier. Par exemple, si vous voulez voir quels classements sont autorisés par le utf8mb4 jeu de caractères, exécutez :

SHOW COLLATION LIKE 'utf8mb4%';

Un classement peut être insensible à la casse, sensible à la casse ou binaire. Construisons un tableau simple, insérons-y quelques valeurs, puis visualisons les données en utilisant différents classements pour voir en quoi le résultat diffère :

CREATE TABLE sample (s CHAR(5));
INSERT INTO sample (s) VALUES 
 ('AAAAA'), ('ccccc'),  ('bbbbb'), ('BBBBB'), ('aaaaa'), ('CCCCC');
SELECT * FROM sample;
+-----------+
| s         |
+-----------+
| AAAAA     |
| ccccc     |
| bbbbb     |
| BBBBB     |
| aaaaa     |
| CCCCC     |
+-----------+

Avec les classements insensibles à la casse, vos données sont renvoyées dans l’ordre alphabétique, mais il n’y a aucune garantie que les mots en majuscules viendront avant les mots en minuscules, comme indiqué ci-dessous :

SELECT * FROM sample ORDER BY s COLLATE utf8mb4_turkish_ci;
+-----------+
| s         |
+-----------+
| AAAAA     |
| aaaaa     |
| bbbbb     |
| BBBBB     |
| ccccc     |
| CCCCC     |
+-----------+

D’autre part, lorsque MySQL exécute une recherche sensible à la casse, les minuscules viendront avant les majuscules pour chaque lettre :

SELECT * FROM sample ORDER BY s COLLATE utf8mb4_0900_as_cs;
+-----------+
| s         |
+-----------+
| aaaaa     |
| AAAAA     |
| bbbbb     |
| BBBBB     |
| ccccc     |
| CCCCC     |
+-----------+

Et les classements binaires renverront tous les mots en majuscules avant les mots en minuscules :

SELECT * FROM sample ORDER BY s COLLATE utf8mb4_0900_bin;
+-----------+
| s         |
+-----------+
| AAAAA     |
| ccccc     |
| bbbbb     |
| BBBBB     |
| aaaaa     |
| CCCCC     |
+-----------+

Si vous voulez savoir quel jeu de caractères et classement une chaîne utilise, vous pouvez utiliser le bien nommé jeu de caractères et collation les fonctions. Un serveur exécutant MySQL version 8.0 ou supérieure utilisera par défaut le utf8mb4 jeu de caractères et utf8mb4_0900_ai-ci collation:

SELECT charset('data');
+-------------------+
| charset('data')   |
+-------------------+
| utf8mb4           |
+-------------------+
 SELECT collation('data');
+--------------------+
| collation('data')  |
+--------------------+
| utf8mb4_0900_ai_ci |
+--------------------+

Vous pouvez utiliser le SET NAMES commande pour modifier le jeu de caractères ou le classement utilisé.

Pour changer de utf8mb4 jeu de caractères sur utf16exécutez cette commande :

SET NAMES 'utf16';

Si vous souhaitez également choisir un classement autre que celui par défaut, vous pouvez ajouter un COLLATIONNER clause à la SET NAMES commande.

Par exemple, supposons que votre base de données stocke des mots en espagnol. Le classement par défaut pour MySQL (utf8mb4_0900_ai_ci) voit ch et ll comme deux caractères différents et les trie comme tels. Mais en espagnol, ch et ll sont des lettres individuelles, donc si vous voulez qu’elles soient triées dans le bon ordre (après c et l, respectivement), vous devez utiliser un classement différent. Une option consiste à utiliser le utf8mb4_espagnol2_ci collation.

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_spanish2-ci';

Stockage des chaînes

MySQL vous permet de choisir entre plusieurs types de données pour vos valeurs de chaîne. (Encore plus que d’autres bases de données populaires telles que PostgreSQL et MongoDB.)

Voici une liste des types de données de chaîne binaire de MySQL, leurs équivalents non binaires et leur longueur maximale :

• binaire: omble chevalier (255)
• varbinaire : varchar (65 535)
• petitblob : petit texte (255)
• goutte: texte (65 535)
• blob moyen : texte moyen (16 777 215)
• longblob : texte long (4 294 967 295)

Une chose importante à retenir est que, contrairement aux types varbinary, varchar, text et blob, qui sont stockés dans des champs de longueur variable (c’est-à-dire en utilisant uniquement l’espace nécessaire), MySQL stocke les types binary et char dans des champs de longueur fixe. Ainsi une valeur telle que caractère(20) ou alors binaire(20) occupera toujours 20 octets, même si vous y stockez moins de 20 caractères. MySQL remplit les valeurs avec le ASCII NUL évaluer (0x00) pour les types binaires et les espaces pour les types char.

Une autre chose à considérer lors du choix des types de données est de savoir si vous souhaitez que les espaces après la chaîne soient conservés ou supprimés. Lors de l’affichage des données, MySQL supprime les espaces des données stockées avec le type de données char, mais pas varchar.

CREATE TABLE sample2 (s1 CHAR(10), s2 VARCHAR(10));
INSERT INTO sample2 (s1, s2) VALUES ('cat       ', 'cat       ');
SELECT s1, s2, CHAR_LENGTH(s1), CHAR_LENGTH(s2) FROM sample2;
+---------+---------+-----------------------------------+
| s1      | s2      | CHAR_LENGTH(s1) | CHAR_LENGTH(s2) |
+---------+---------+-----------------------------------+
| cat     | cat     |        3        |       10        |
+---------+---------+-----------------------------------+

Emballer

Les chaînes sont l’un des types de données les plus couramment utilisés dans les bases de données, et MySQL reste l’un des systèmes de base de données les plus populaires utilisés aujourd’hui. J’espère que vous avez appris quelque chose de nouveau grâce à cet article et que vous pourrez utiliser vos nouvelles connaissances pour améliorer vos compétences en matière de bases de données.