Table des matières de l'article :
Lorsque vous travaillez avec MySQL ou des dérivés tels que Percona Server ou MariaDB, vous rencontrez souvent les concepts de Jeu de caractères e collation, indispensables pour gérer correctement la sauvegarde et la manipulation des données textuelles au sein des bases de données. Cependant, pour de nombreux développeurs débutant avec ces SGBD, ces concepts peuvent être complexes ou déroutants.
Dans cet article, nous explorerons en détail ce que Jeu de caractères et collation dans MySQL et MariaDB, pourquoi ils sont importants et comment ils affectent le stockage et la gestion des données. Nous aborderons les personnages principaux tels que UTF8, UTF8MB3, UTF8MB4, nous parlerons de l'importance de collation comment utf8mb4_general_ci
, utf8mb4_unicode_ci
et utf8mb4_unicode_520_ci
et nous comprendrons comment ces paramètres peuvent avoir un impact sur la vitesse des requêtes.
Qu'est-ce qu'un jeu de caractères ?
Un Jeu de caractères (jeu de caractères) est un ensemble de symboles et leur représentation binaire. Chaque base de données relationnelle comme MySQL ou MariaDB utilise i Jeu de caractères pour gérer la manière dont les caractères sont codés et enregistrés dans les champs du tableau.
Exemples de jeux de caractères
Il y a plusieurs Jeu de caractères utilisés dans les bases de données, parmi les plus courants figurent :
- latin1: Un ensemble de caractères à un octet représentant le codage ISO-8859-1 (courant dans les langues d'Europe occidentale).
- utf8 : un ensemble de caractères qui code les données à l'aide du codage UTF-8. Chaque caractère peut prendre entre 1 et 3 octets. Cependant, dans MySQL, le nom « utf8 » est un peu trompeur car il ne représente que des caractères jusqu'à 3 octets (nous en parlerons plus tard).
- utf8mb4: Une variante d'UTF-8 qui prend entièrement en charge tous les caractères Unicode, y compris les émojis et les symboles nécessitant jusqu'à 4 octets.
UTF8 contre UTF8MB4 : quelle est la différence ?
L'un des points les plus importants à comprendre est la différence entre utf8
e utf8mb4
dans MySQL et MariaDB.
- utf8: Est-ce qu'un Jeu de caractères qui prend en charge les caractères UTF-8, mais seulement jusqu'à 3 octets par caractère. Cela signifie qu'il ne peut représenter qu'un sous-ensemble de caractères Unicode (environ 1.112.064 4 XNUMX caractères au total), mais il ne prend pas en charge les caractères tels que de nombreux emoji et certains symboles asiatiques qui nécessitent XNUMX octets.
- utf8mb4: est l'implémentation complète de l'encodage UTF-8 dans MySQL et MariaDB.
utf8mb4
prend en charge tous les caractères Unicode, y compris ceux qui nécessitent 4 octets. C'est le Jeu de caractères que vous devez utiliser si votre base de données doit gérer correctement les emojis ou autres caractères nécessitant plus de 3 octets.
Exemple pratique :
Si vous essayez d'enregistrer un emoji (par exemple 😊) dans une colonne qui utilise le Jeu de caractères utf8
, vous recevrez une erreur ou les données seront tronquées, puisque ce caractère nécessite 4 octets, alors que utf8
ne prend en charge que jusqu'à 3 octets. En utilisant utf8mb4
, cependant, l'emoji sera correctement enregistré.
Utilisation d'UTF8MB3
Parfois, vous pouvez voir le terme utf8mb3, qui est un nom alternatif pour le Jeu de caractères utf8
dans MySQL. Ce nom a été introduit pour indiquer plus clairement que utf8
dans MySQL ne prend en charge que les caractères jusqu'à 3 octets, contrairement à utf8mb4
, qui prend en charge l'ensemble du jeu de caractères Unicode, y compris les caractères à 4 octets, tels que les emoji ou certains caractères asiatiques plus complexes. Donc, essentiellement, utf8mb3
e utf8
ils sont équivalents, mais l'utilisation de utf8mb3
sert à mettre en évidence la limitation inhérente de MySQL à ne prendre en charge qu'un sous-ensemble de caractères Unicode sous l'ancien nom utf8
.
Ces dernières années, le paysage technologique évolue de plus en plus vers la prise en charge complète des caractères Unicode, y compris les caractères à 4 octets. Pour cette raison, le le monde s’oriente vers l’adoption universelle de utf8mb4
, à la fois pour des raisons de compatibilité avec les nouveaux standards et pour garantir une gestion plus complète des personnages.
Le « changement de vitesse » vers utf8mb4
Dans certaines configurations, notamment dans versions plus récentes de MariaDB, on peut observer un « changement de vitesse » dans la gestion des Jeu de caractères. Traditionnellement, utf8
(o utf8mb3
) a été jugé suffisant pour la plupart des applications ne nécessitant pas la gestion de caractères complexes. Cependant, avec la nécessité croissante de gérer des contenus multilingues, des emojis et autres caractères spéciaux, le jeu de caractères utf8mb4
a commencé à s’imposer comme la nouvelle norme.
Un exemple de ce changement peut être vu dans le comportement par défaut des bases de données. Alors que par le passé le Jeu de caractères utf8
a été largement utilisé, bon nombre des configurations prédéfinies des nouvelles versions de MySQL et MariaDB migrent vers utf8mb4
comme option par défaut pour garantir une prise en charge des polices plus large et plus moderne.
Dans certaines versions récentes, il peut arriver que, sans configuration explicite, une base de données historiquement utilisée utf8
pour stocker des chaînes, peut implicitement passer à utf8mb4
. Cela peut entraîner des changements inattendus dans la gestion des données, tels qu'une augmentation de la taille de stockage des colonnes. VARCHAR
o TEXT
, et a potentiellement un impact sur les performances concernant les opérations d'indexation et de comparaison sur des caractères complexes.
Implications de la configuration de MySQL et MariaDB
Pour gérer correctement cette étape, Il est essentiel de vérifier et de configurer soigneusement les paramètres de votre base de données, à la fois au niveau du serveur et de la table ou de la colonne individuelle. Dans MySQL et MariaDB, de nombreux paramètres concernant le Jeu de caractères et collation ils peuvent être définis dans les principaux fichiers de configuration, tels que my.cnf
dans MySQL ou server.cnf
dans MariaDB.
Qu'est-ce qu'un classement ?
Une collation est un ensemble de règles qui déterminent comment comparer et trier les caractères dans une base de données. Tout va bien Jeu de caractères a un ou plusieurs collation associés, qui spécifient comment les caractères sont comparés pour des opérations telles que ORDER BY
, GROUP BY
ou pour effectuer des comparaisons d'égalité.
Principaux classements dans MySQL
Le collation ils ont des noms qui suivent une convention spécifique. Par exemple, utf8mb4_general_ci
est divisé en trois parties :
- utf8mb4: indique le Jeu de caractères auquel il appartient collation.
- général: Indique le type de classement.
- ci: représente insensible à la casse, À savoir la collation Ce n'est pas sensible à la casse.
Voici quelques-uns des principaux collation utilisé dans MySQL et MariaDB :
- utf8mb4_general_ci: C'est l'un des collation par défaut pour
utf8mb4
et n'est pas sensible à la casse (insensible à la casse). Il utilise des règles de comparaison générales et simplifiées, ce qui le rend particulièrement efficace en termes de rapidité pour des opérations telles que le tri et la comparaison de chaînes. Cependant, en raison de sa nature simplifiée, il est moins rigoureux et moins précis pour traiter certaines complexités linguistiques que le standard Unicode. Pour les applications où la vitesse est critique et où la précision linguistique n’est pas critique, c’est souvent le choix préféré. - utf8mb4_unicode_ci: Ce collation suit strictement les règles Unicode standard pour la comparaison des caractères. C'est plus précis que
utf8mb4_general_ci
lorsque vous travaillez avec différentes langues, accents, symboles complexes et caractères spéciaux. Cependant, sa précision a un coût en termes de performances : elle peut être légèrement plus lente dans les requêtes, en particulier sur les grands ensembles de données, en raison d'un classement plus détaillé. Il est recommandé pour les applications nécessitant une grande précision linguistique. - utf8mb4_unicode_520_ci: Il s'agit d'une variante mise à jour de
utf8mb4_unicode_ci
qui implémente les règles de la norme Unicode 5.2. En plus de conserver les fonctionnalités de la version précédente, il prend en charge les nouveaux caractères et symboles introduits avec cette version du protocole Unicode, ce qui en fait un choix approprié pour la gestion des caractères récents ou spéciaux. Encore une fois, la précision signifie que les requêtes peuvent être plus lentes que collation moins précis.
Différences entre les classements
utf8mb4_general_ci contre utf8mb4_unicode_ci
utf8mb4_general_ci
il est plus rapide car il applique des règles de comparaison plus simples, notamment pour les langues européennes. Cependant, il ne gère pas bien toutes les complexités linguistiques. Par exemple, il ne distingue pas correctement certaines variations de caractères dans les langues non européennes, comme les ligatures ou certains accents dans les langues asiatiques.
D'un autre côté, utf8mb4_unicode_ci
il suit strictement les règles Unicode, gérant correctement les caractères spéciaux, les accents et les symboles, ce qui le rend plus adapté aux situations où la précision linguistique est essentielle.
Impact sur les performances
L'utilisation d'un collation peut avoir un impact significatif sur les performances des requêtes. Collation plus complexe, comme utf8mb4_unicode_ci
o utf8mb4_unicode_520_ci
, leur comparaison et leur tri peuvent prendre plus de temps car ils doivent suivre des règles plus détaillées.
Par exemple, si vous avez une table contenant des millions de lignes et que vous effectuez une ORDER BY
sur une colonne avec le collation utf8mb4_unicode_ci
, cela peut prendre plus de temps qu'une table qui utilise utf8mb4_general_ci
. Cela est dû au fait que le collation Unicode doit gérer correctement les caractères complexes, les accents et autres symboles spéciaux, tandis que utf8mb4_general_ci
appliquer des règles de comparaison plus simples.
Le graphique montre une comparaison des performances entre différents collation dans MySQL 5.7, mesurez en débit (tps) par rapport au nombre de threads utilisés (4, 24, 64, 128). Le collation comparés sont :
- utf8mb4_general_ci (par défaut) (en bleu)
- utf8mb4_bin (en rouge)
- utf8mb4_unicode_ci (en jaune)
- utf8mb4_unicode_520_ci (en vert)
Remarques:
- utf8mb4_bin (rouge) a le débit le plus élevé avec toutes les quantités de threads, affichant les meilleures performances.
- utf8mb4_general_ci (bleu), le collation Par défaut, c'est le deuxième plus rapide, avec des performances qui restent constantes et très proches de celles de
utf8mb4_bin
avec 128 fils. - utf8mb4_unicode_ci (jaune) a des performances inférieures à celles
utf8mb4_bin
eutf8mb4_general_ci
, avec un débit visiblement inférieur surtout à partir de 24 threads. - utf8mb4_unicode_520_ci (vert) est le collation avec les pires performances, d'autant plus que le nombre de threads augmente, confirmant une baisse notable du débit.
Si vous utilisez un collation comment utf8mb4_unicode_ci
o utf8mb4_unicode_520_ci
, il y aura un impact significatif sur les performances, en particulier dans les situations à nombre de threads élevé, par rapport à l'utilisation de collation plus léger que utf8mb4_general_ci
o utf8mb4_bin
.
Cas d'utilisation pratiques
Si vous développez une application qui doit prendre en charge les langues d'Europe occidentale et que vous ne vous souciez pas trop de l'exactitude des classements pour d'autres langues, utf8mb4_general_ci
cela pourrait être un choix raisonnable. Si, toutefois, votre base de données doit prendre en charge plusieurs langues et que vous devez vous assurer que les comparaisons de caractères sont effectuées conformément aux règles Unicode standard, alors utf8mb4_unicode_ci
o utf8mb4_unicode_520_ci
ce sont de meilleurs choix.
Choisir le bon jeu de caractères et le bon classement
Le choix d' Jeu de caractères et collation Cela dépend fortement des exigences de votre application et du type de données que vous envisagez de gérer dans la base de données.
Quand utiliser UTF8MB4
En général, si vous travaillez sur un nouveau projet, tu devrais utiliser utf8mb4
comme police par défaut. Même si vous ne pensez pas pouvoir gérer les emojis ou les symboles Unicode à 4 octets pour le moment, utilisez utf8mb4
vous donne la flexibilité de gérer n’importe quelle police Unicode à l’avenir. Il n'y a pas d'inconvénients majeurs à utiliser utf8mb4
par rapport à utf8
, sauf une légère augmentation de l'espace de stockage pour les caractères qui nécessitent plus d'octets.
Exemple de mise en œuvre pratique :
CREATE DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Dans cet exemple, vous créez une base de données appelée testdb
le Jeu de caractères utf8mb4
et la collation utf8mb4_unicode_ci
. Cette configuration garantit que la base de données prend en charge tous les caractères Unicode, y compris les emoji, et qu'elle suit les règles Unicode standard pour comparer et trier les caractères.
Rassemblement et performances
Comme nous l'avons déjà mentionné, l'utilisation d'un collation plus complexe peut avoir un impact sur les performances. Par conséquent, si vous développez une application où la vitesse des requêtes est critique et que vous ne vous souciez pas trop de la précision linguistique, vous souhaiterez peut-être choisir un collation plus simple comme utf8mb4_general_ci
.
En revanche, si votre application doit gérer plusieurs langues et nécessite une précision linguistique rigoureuse, vous devriez en opter pour une seule. collation plus complexe comme utf8mb4_unicode_ci
.
Impact des classements sur les index et les recherches
Un autre domaine où le collation peut influencer est la création d’index. Lorsque vous créez un index sur une colonne qui utilise un collation, les règles de collation déterminer comment l'index est trié. Cela peut affecter les performances des recherches dans la base de données, comme nous pouvons le voir dans l'exemple ci-dessous. extrait du blog de Percona où il parle des performances de collation.
Par exemple, un index créé sur une colonne avec utf8mb4_general_ci
peut être plus efficace qu'un index sur une colonne avec utf8mb4_unicode_ci
, puisque les règles de comparaison du collation en général, ils sont plus simples.
CREATE INDEX idx_name ON users (name COLLATE utf8mb4_general_ci);
Dans cet exemple, l'index de la colonne name
utiliser le collation utf8mb4_general_ci
, qui peut être plus performant dans les recherches qu'un index qu'il utilise utf8mb4_unicode_ci
.
Conclusions
I Jeu de caractères et collation ce sont des composants cruciaux pour gérer correctement les données textuelles dans MySQL et MariaDB. Choisissez le Jeu de caractères correct (de préférence utf8mb4
pour les nouveaux projets) et le collation de manière adéquate peut avoir un impact significatif sur la capacité de la base de données à gérer des caractères complexes, tels que les emoji, et sur la manière dont les opérations telles que le tri et la comparaison des données sont effectuées.
Être conscient des implications de ces choix vous aidera à optimiser la gestion de vos données texte et à garantir que votre application fonctionne correctement et efficacement.
Si votre base de données ou votre installation WordPress ne parvient pas à enregistrer les caractères spéciaux, contactez-nous pour obtenir des conseils et résoudre le problème.