Explication détaillée du rôle de COLLATE dans MYSQL et des différences entre les différents COLLATE

Qu'est-ce que COLLATE dans MYSQL ?

Exécutez la commande show create table dans mysql, et vous pourrez voir l'instruction de création de table d'une table. L'exemple est le suivant :

CREATE TABLE `table1` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `field1` text COLLATE utf8_unicode_ci NOT NULL COMMENT '字段1',
    `field2` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '字段2',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci;

Nous pouvons comprendre la plupart des champs, mais aujourd'hui nous y allons. pour discuter de la clé du personnage COLLATE. Que signifie le utf8_unicode_ci correspondant à cette valeur ? Si vous utilisez cette question pour passer le test DBA pendant l’entretien, cela devrait pouvoir surprendre la plupart des gens.

À quoi sert COLLATE ?

Les développeurs utilisant phpmyadmin peuvent sembler très familiers, car l'en-tête chinois a déjà donné la réponse :

capture d'écran de phpmyadmin

Ce qu'on appelle utf8_unicode_ci est en fait une règle utilisée pour le tri. Pour les colonnes de type caractère dans MySQL, telles que les colonnes de type VARCHAR, CHAR et TEXT, un type COLLATE est requis pour indiquer à MySQL comment trier et comparer les colonnes. En bref, COLLATE affectera l'ordre de l'instruction ORDER BY, les résultats filtrés par le signe supérieur ou inférieur dans la condition WHERE, ainsi que **DISTINCT**, **GROUP BY** et **HAVING*. * déclarations. résultats de la requête. De plus, lorsque MySQL construit un index, si la colonne d'index est de type caractère, cela affectera également la création de l'index, mais nous ne pouvons pas percevoir cet impact. En bref, chaque fois qu'une comparaison ou un tri de types de caractères est impliqué, cela sera lié à COLLATE.

La différence entre les différents COLLATE

COLLATE est généralement liée à l'encodage des données (CHARSET). De manière générale, chaque CHARSET prend en charge plusieurs COLLATE, et chaque CHARSET spécifie un COLLATE comme valeur par défaut. Par exemple, le COLLATE par défaut pour le codage Latin1 est latin1_swedish_ci, le COLLATE par défaut pour le codage GBK est gbk_chinese_ci et la valeur par défaut pour le codage utf8mb4 est utf8mb4_general_ci.

Permettez-moi de faire une digression en passant. Il y a deux encodages dans MySQL : utf8 et utf8mb4. Dans MySQL, veuillez oublier **utf8** et utilisez toujours **utf8mb4**. Il s'agit d'un problème hérité de MySQL. UTF8 dans MySQL ne peut prendre en charge que les codages de caractères d'une longueur maximale de 3 octets. Pour certains textes devant occuper 4 octets, l'UTF8 de MySQL ne le prend pas en charge.

De nombreux COLLATE contiennent le mot _ci, qui est l'abréviation de Case Insensitive, ce qui signifie que « A » et « a » sont traités de la même manière lors du tri et de la comparaison. sélection * de la table1 où field1="a" peut également sélectionner la valeur de field1 comme "A". Dans le même temps, pour les COLLATE avec le suffixe _cs, il est sensible à la casse, c'est-à-dire sensible à la casse.

Utilisez la commande show collation dans MySQL pour afficher tous les COLLATE pris en charge par MySQL. En prenant utf8mb4 comme exemple, tous les COLLATE pris en charge par cet encodage sont comme indiqué dans la figure ci-dessous.

Tous les COLLATE liés à utf8mb4 dans mysql

Sur l'image, nous pouvons voir les règles de classement des langues de nombreux pays. Les trois fichiers couramment utilisés en Chine sont utf8mb4_general_ci (par défaut), utf8mb4_unicode_ci et utf8mb4_bin. Explorons les différences entre ces trois :

Tout d'abord, la méthode de comparaison de utf8mb4_bin consiste à traiter directement tous les caractères comme des chaînes binaires, puis à les comparer du bit le plus élevé au bit le plus bas. Donc évidemment, c'est sensible à la casse.

Il n'y a en fait aucune différence entre utf8mb4_unicode_ci et utf8mb4_general_ci pour le chinois et l'anglais. Pour le système que nous avons développé pour un usage domestique, vous pouvez en choisir un. C'est juste que pour les lettres de certains pays occidentaux, utf8mb4_unicode_ci est plus conforme à leurs habitudes linguistiques que utf8mb4_general_ci General est un standard plus ancien de MySQL. Par exemple, la lettre allemande "ß" équivaut aux deux lettres "ss" dans utf8mb4_unicode_ci (cela est conforme aux habitudes allemandes), mais dans utf8mb4_general_ci, elle équivaut à la lettre "s". Cependant, les différences subtiles entre les deux codages sont difficiles à percevoir pour un développement normal. Nous utilisons rarement des champs de texte pour trier directement. Pour prendre du recul, même si une ou deux lettres sont mal alignées, cela peut-il vraiment avoir des conséquences catastrophiques sur le système ? À en juger par divers articles et discussions trouvés sur Internet, de plus en plus de personnes recommandent d'utiliser utf8mb4_unicode_ci, mais elles ne sont pas très résistantes aux systèmes qui utilisent la valeur par défaut et ne pensent pas qu'il y ait de gros problème. Conclusion : Il est recommandé d'utiliser utf8mb4_unicode_ci Pour les systèmes qui utilisent déjà utf8mb4_general_ci, il n'est pas nécessaire de passer du temps à le modifier.

Une autre chose à noter est qu'à partir de MySQL 8.0, le CHARSET par défaut de MySQL n'est plus Latin1, mais a été modifié en utf8mb4 (lien de référence), et le COLLATE par défaut a également été modifié en utf8mb4_0900_ai_ci. utf8mb4_0900_ai_ci est généralement une autre subdivision d'Unicode. 0900 fait référence au numéro de l'algorithme de comparaison Unicode (version Unicode Collation Algorithm), et ai signifie insensible à l'accent (par exemple, e, è, é, ê et ë sont). traités de manière égale. Lien de référence associé 1, lien de référence associé 2

Niveau de réglage COLLATE et sa priorité

设置COLLATE可以在示例级别、库级别、表级别、列级别、以及SQL指定。实例级别的COLLATE设置就是mysql配置文件或启动指令中的collation_connection系统变量。

库级别设置COLLATE的语句如下：

CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

如果库级别没有设置CHARSET和COLLATE，则库级别默认的CHARSET和COLLATE使用实例级别的设置。在mysql8.0以下版本中，你如果什么都不修改，默认的CHARSET是Latin1，默认的COLLATE是latin1_swedish_ci。从mysql8.0开始，默认的CHARSET已经改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。

表级别的COLLATE设置，则是在CREATE TABLE的时候加上相关设置语句，例如：

CREATE TABLE (
……
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

如果表级别没有设置CHARSET和COLLATE，则表级别会继承库级别的CHARSET与COLLATE。

列级别的设置，则在CREATE TABLE中声明列的时候指定，例如

CREATE TABLE (
`field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
……
) ……

如果列级别没有设置CHARSET和COLATE，则列级别会继承表级别的CHARSET与COLLATE。

最后，你也可以在写SQL查询的时候显示声明COLLATE来覆盖任何库表列的COLLATE设置，不太常用，了解即可：

SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;

如果全都显示设置了，那么优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。也就是说列上所指定的COLLATE可以覆盖表上指定的COLLATE，表上指定的COLLATE可以覆盖库级别的COLLATE。如果没有指定，则继承下一级的设置。即列上面没有指定COLLATE，则该列的COLLATE和表上设置的一样。

以上就是关于mysql的COLLATE相关知识。不过，在系统设计中，我们还是要尽量避免让系统严重依赖中文字段的排序结果，在mysql的查询中也应该尽量避免使用中文做查询条件。

推荐学习：《mysql视频教程》

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!