Que signifie assembler dans MySQL ?

Collate de MySQL signifie ensemble de classement, qui peut être compris comme une règle de tri. Dans MySQL, les données de caractères stockées peuvent avoir différents classements, en fonction du jeu de caractères et des règles de classement utilisées. Le jeu de caractères détermine les types de caractères pouvant être stockés et les règles d'assemblage déterminent la manière dont ces caractères sont triés.

Le système d'exploitation de ce tutoriel : système Windows 10, mysql version 8.0, ordinateur Dell G3.

mysql COLLATE signifie ensemble de classement, qui peut être compris comme des règles de tri, etc.

Dans MySQL, les données de caractères stockées peuvent avoir différents classements, en fonction du jeu de caractères et des règles de classement utilisées. Le jeu de caractères détermine les types de caractères pouvant être stockés et les règles d'assemblage déterminent la manière dont ces caractères sont triés.

Normalement, lors de la création d'une base de données ou d'une table, vous pouvez définir la méthode de tri des données de caractères en spécifiant différents jeux de caractères et règles d'assemblage. Si aucune règle de classement spécifique n'est spécifiée, MySQL utilisera la règle de classement par défaut. Les jeux de caractères courants et les règles d'assemblage incluent utf8mb4_general_ci (insensible à la casse, insensible aux accents), utf8mb4_unicode_ci (insensible à la casse, sensible aux accents), etc.

La sélection des règles d'assemblage est déterminée en fonction de besoins spécifiques. Différentes règles d'assemblage conviennent à différents scénarios. Par exemple, lors de la recherche et du tri des données, certaines règles d'assemblage effectuent des comparaisons sensibles à la casse, tandis que d'autres ne le font pas. Par conséquent, lors de la conception d’une base de données, des règles de collecte appropriées doivent être sélectionnées en fonction des besoins réels et des règles métier.

Différentes méthodes de tri peuvent être obtenues en utilisant différentes règles d'assemblage. Par exemple, les données de caractères peuvent être triées sans tenir compte de la casse, ce qui signifie que « A » et « a » seront considérés comme égaux. En revanche, si des règles d'assemblage sensibles à la casse étaient utilisées, alors « A » et « a » seraient traités comme des caractères différents.

De plus, les règles d'assemblage peuvent également affecter les opérations de comparaison de chaînes. Selon différentes règles d'assemblage, les résultats de la comparaison entre les caractères peuvent être différents. Par exemple, selon certaines règles d'assemblage, la lettre « a » peut être considérée comme plus grande que la lettre « Z », tandis que selon d'autres règles, c'est l'inverse.

Il est important de noter que les règles d'assemblage s'appliquent non seulement au tri et à la comparaison des données de caractères, mais également aux résultats lorsque des fonctions et des opérateurs de chaîne sont utilisés dans les requêtes. Par conséquent, lors de l’écriture de requêtes SQL, vous devez prendre en compte les règles d’assemblage utilisées pour garantir l’obtention des résultats attendus.

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 ce dont nous voulons discuter aujourd'hui est. le mot-clé COLLATE. Que signifie le utf8_unicode_ci correspondant à cette valeur ? Si vous utilisez cette question pour passer l’examen DBA lors de l’entretien, elle 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 :

La soi-disant 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 l'encodage Latin1 est latin1_swedish_ci, le COLLATE par défaut pour l'encodage GBK est gbk_chinese_ci et la valeur par défaut pour l'encodage 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.

在mysql中使用show collation指令可以查看到mysql所支持的所有COLLATE。以utf8mb4为例，该编码所支持的所有COLLATE如下图所示。

imgmysql中和utf8mb4相关的所有COLLATE

图中我们能看到很多国家的语言自己的排序规则。在国内比较常用的是utf8mb4_general_ci（默认）、utf8mb4_unicode_ci、utf8mb4_bin这三个。我们来探究一下这三个的区别：

首先utf8mb4_bin的比较方法其实就是直接将所有字符看作二进制串，然后从最高位往最低位比对。所以很显然它是区分大小写的。

而utf8mb4_unicode_ci和utf8mb4_general_ci对于中文和英文来说，其实是没有任何区别的。对于我们开发的国内使用的系统来说，随便选哪个都行。只是对于某些西方国家的字母来说，utf8mb4_unicode_ci会比utf8mb4_general_ci更符合他们的语言习惯一些，general是mysql一个比较老的标准了。例如，德语字母“ß”，在utf8mb4_unicode_ci中是等价于"ss"两个字母的（这是符合德国人习惯的做法），而在utf8mb4_general_ci中，它却和字母“s”等价。不过，这两种编码的那些微小的区别，对于正常的开发来说，很难感知到。本身我们也很少直接用文字字段去排序，退一步说，即使这个字母排错了一两个，真的能给系统带来灾难性后果么？从网上找的各种帖子讨论来说，更多人推荐使用utf8mb4_unicode_ci，但是对于使用了默认值的系统，也并没有非常排斥，并不认为有什么大问题。结论：推荐使用utf8mb4_unicode_ci，对于已经用了utf8mb4_general_ci的系统，也没有必要花时间改造。

另外需要注意的一点是，从mysql 8.0开始，mysql默认的CHARSET已经不再是Latin1了，改为了utf8mb4（参考链接），并且默认的COLLATE也改为了utf8mb4_0900_ai_ci。utf8mb4_0900_ai_ci大体上就是unicode的进一步细分，0900指代unicode比较算法的编号（ Unicode Collation Algorithm version），ai表示accent insensitive（发音无关），例如e, è, é, ê 和 ë是一视同仁的。相关参考链接1，相关参考链接2

COLLATE设置级别及其优先级

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

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

CREATE DATABASE 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的查询中也应该尽量避免使用中文做查询条件。

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!