Comment faire la distinction entre les majuscules et les minuscules lors de l'interrogation des données stockées dans MySQL

Description de la scène

Après la synchronisation de la table Hive avec MySQL aujourd'hui, l'une des colonnes est la seule colonne, mais lors de l'interrogation dans MySQL, count et distinct count Les valeurs interrogées sont différentes De ce point de vue, il y a des données en double (ce qui ne devrait pas être le cas, car dans Hive, les deux valeurs sont les mêmes), alors vérifiez les données en double. et jetez un oeil, j'ai trouvé que c'était un problème de casse, puis je l'ai vérifié et j'ai découvert que par défaut dans la base de données MySQL, toutes les opérations associées sur les champs de chaîne sont "insensibles à la casse". count 与 distinct count 查询出来的数值是不一样的，这么来看的话是有重复的数据（按理说不应该的，因为在 Hive 中，这两个数值是一样的），那么将重复的数据查出来看了一下，发现是大小写的问题，然后查了一下，发现 MySQL 数据库默认情况下，字符串字段的所有相关运算是大小写"不敏感"的。

这一点与其它流行的数据库都不相同。

解决办法

1. 查询时指定大小写敏感

MySQL 允许在查询的时候指定以大小写敏感方式，需要使用关键字 BINARY，查询如下：

SELECT * FROM student WHERE BINARY name = 'ZhangSan';
--或者
SELECT * FROM student WHERE name = BINARY 'ZhangSan';

很多时候当发现 MySQL 数据库存在上述问题时，系统已经运行了一段时间，如果采用方法二或方法三的代价可能会很大。

使用此方法最大的好处便是可以快速实现功能。

但是这个方法也存在很大的限制：如此可能因为无法使用索引导致查询性能下降。

原因很好理解，因为此时针对查询字段的索引也是按照大小写不敏感方式建立的。

除非数据量不大，或者在你的应用中不在乎这点性能上的损失，那么只能选择方法二或方法三了。

2. 定义表结构时指定字段大小写敏感

在创建表时指定具体的字段大小写敏感，示例如下：

CREATE TABLE student (
  ...
  name VARCHAR(64) BINARY NOT NULL,
  ...

)

关键字 BINARY 指定 name 字段大小写敏感。

如此在查询时就算不使用 BINARY 关键字，查询语句也是大小写敏感的。

在此基础上创建的 name 相关的索引也是大小写敏感的，也就能够使用索引来提高性能。

MySQL 允许在大多数字符串类型上使用 BINARY 关键字，用于指明所有针对该字段的运算是大小写敏感的，更多信息请参见 MySQL 官方文档。

这种方法使得设计者可以精确地控制每个字段是否大小写敏感。在许多系统的设计中，通常期望所有字段都是大小写敏感的，甚至大多数字段都是如此。MySQL 也提供了解决方案，这就要用到方法三。

3. 修改排序规则（COLLATE）

在 MySQL 中执行 show create table <tablename></tablename> 指令，可以看到一张表的建表语句，example 如下：

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

大部分字段我们都能看懂，但是今天要看的是 COLLATE 关键字。这个值后面对应的 utf8_general_ci 是什么意思呢？下面我们就来了解一下。

COLLATE是用来做什么的？

使用 Navicat 开发的可能会比较眼熟，因为其中的选项中已经给出了答案：

所谓 utf8_general_ci，其实是用来排序的规则。对于 MySQL 中那些字符类型的列，如VARCHAR，CHAR，TEXT 类型的列，都需要有一个 COLLATE 类型来告知 MySQL 如何对该列进行排序和比较。简而言之，COLLATE 会影响到 ORDER BY 语句的顺序，会影响到 WHERE 条件中大于小于号筛选出来的结果，会影响 DISTINCT、GROUP BY、HAVING 语句的查询结果。另外，MySQL 建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都会和 COLLATE 有关。

涉及字符串的各种运算其核心必然涉及到采用何种字符排序规则（COLLATE，也有翻译为"核对"）。MySQL的字符串运算是否区分大小写，本质上取决于它所使用的COLLATE排序规则。

utf8_general_ci 是一个具体的 COLLATE 取值。每个具体的 COLLATE 都对应唯一的字符集，可以看出该 COLLATE 对应字符集为 utf8。而与大小写敏感问题相关的是其后缀 _ci，MySQL 官方文档对其的解释是 Case Ignore 的缩写，即大小写不敏感。由于 MySQL 将 utf8_general_ci 指定作为字符集 utf8 的默认 COLLATE，这也就导致文章开头所说的现象。与此同时，MySQL 也提供了其它的 COLLATE 取值选项，utf8_bin 就是大小写敏感的。事实上所有大小写敏感的 COLLATE 都以 _bin或 _cs 为后缀，前者是 Binary 的缩写，后者是 Case Sensitive

Ceci est différent des autres bases de données populaires. 🎜🎜Solution🎜

1. Spécifiez le respect de la casse lors de l'interrogation

🎜MySQL vous permet de spécifier le respect de la casse lors de l'interrogation. Vous devez utiliser le mot-clé BINARY. 🎜

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

🎜Souvent, lorsque les problèmes ci-dessus sont détectés dans la base de données MySQL, le système fonctionne depuis un certain temps et le coût d'utilisation de la méthode deux ou trois peut être très élevé. 🎜🎜Le plus grand avantage de l'utilisation de cette méthode est qu'elle peut rapidement mettre en œuvre la fonction. 🎜🎜Mais cette méthode a également de grandes limites : elle peut entraîner une diminution des performances des requêtes car l'index ne peut pas être utilisé. 🎜🎜La raison est facile à comprendre, car l'index du champ de requête est également établi sans tenir compte de la casse. 🎜🎜À moins que la quantité de données ne soit pas importante ou que vous ne vous souciez pas de la perte de performances de votre application, vous ne pouvez choisir que la méthode deux ou trois. 🎜

2. Spécifiez la sensibilité à la casse du champ lors de la définition de la structure de la table

🎜Spécifiez la sensibilité à la casse du champ spécifique lors de la création de la table, l'exemple est le suivant : 🎜

CREATE TABLE table_name (
……
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT = '表注释';

🎜Mot clé BINARY Spécifiez le nom Les champs sont sensibles à la casse. 🎜🎜De cette façon, même si le mot-clé BINARY n'est pas utilisé dans la requête, l'instruction de requête est sensible à la casse. 🎜🎜L'index lié au nom créé sur cette base est également sensible à la casse, il peut donc être utilisé pour améliorer les performances. 🎜🎜MySQL permet l'utilisation du mot-clé BINARY sur la plupart des types de chaînes, qui est utilisé pour indiquer que toutes les opérations sur ce champ sont sensibles à la casse. Pour plus d'informations, veuillez consulter la documentation officielle de MySQL. 🎜🎜Cette approche permet aux concepteurs de contrôler avec précision si chaque champ est sensible à la casse. Dans la conception de nombreux systèmes, on s'attend souvent à ce que tous les champs, voire la plupart des champs, soient sensibles à la casse. MySQL fournit également une solution qui nécessite l'utilisation de la troisième méthode. 🎜

3. Modifier les règles de classement (COLLATE)

🎜Exécutez la commande show create table <tablename></tablename> dans MySQL, vous pouvez voir l'instruction de création de table d'une table, l'exemple est le suivant :🎜

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

🎜Nous pouvons comprendre la plupart des champs, mais ce que nous voulons examiner aujourd'hui est le mot-clé COLLATE. Que signifie le utf8_general_ci correspondant à cette valeur ? Découvrons-le ci-dessous. 🎜

A quoi sert COLLATE ?

🎜Ceux développés avec Navicat peuvent sembler familiers, car les réponses ont été données dans les options : 🎜🎜🎜🎜Ce qu'on appelle utf8_general_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 la colonne. 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 et la DISTINCT, GROUP BY, HAVING. De plus, lorsque MySQL construit un index, si la colonne d'index est de type caractère, cela affectera également la création d'index, mais nous ne pouvons pas percevoir cet impact. En bref, Tout ce qui implique une comparaison ou un tri de types de caractères sera lié à COLLATE. 🎜🎜Le cœur des diverses opérations impliquant des chaînes doit impliquer la règle de tri des caractères (COLLATE, également traduit par « vérification »). Le fait que les opérations de chaîne de MySQL soient sensibles à la casse dépend essentiellement du classement COLLATE qu'il utilise. 🎜🎜utf8_general_ci est une valeur COLLATE spécifique. Chaque COLLATE spécifique correspond à un jeu de caractères unique. On constate que le jeu de caractères correspondant à ce COLLATE est utf8. Son suffixe _ci est lié au problème de sensibilité à la casse. La documentation officielle de MySQL l'explique comme l'abréviation de Case Ignore, ce qui signifie qu'il n'est pas sensible à la casse. Puisque MySQL spécifie utf8_general_ci comme COLLATE par défaut du jeu de caractères utf8, cela conduit également au phénomène mentionné au début de l'article. En même temps, MySQL fournit également d'autres options de valeur COLLATE, utf8_bin étant sensible à la casse. En fait, tous les COLLATE sensibles à la casse sont suffixés par _bin ou _cs. Le premier est l'abréviation de Binary et le second est . Abréviation de casse pour Sensible. 🎜

La différence entre les différents COLLATE

COLLATE est généralement liée au codage 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. latin1_swedish_ci，GBK 编码的默认 COLLATE 为 gbk_chinese_ci，utf8mb4 编码的默认值为 utf8mb4_general_ci。

这里顺便讲个题外话，MySQL 中有 utf8 和 utf8mb4 两种编码，在 MySQL 中请大家忘记 utf8，永远使用 utf8mb4。这是 MySQL 的一个遗留问题，MySQL 中的 utf8 最多只能支持 3bytes 长度的字符编码，对于一些需要占据 4bytes 的文字，MySQL 的 utf8 就不支持了，要使用 utf8mb4 才行。

很多 COLLATE 都带有 _ci 字样，这是 Case Insensitive 的缩写，即大小写无关，也就是说 "A" 和 "a" 在排序和比较的时候是一视同仁的。selection * from table1 where field1="a" 同样可以把 field1 为 "A" 的值选出来。与此同时，对于那些 _cs 后缀的 COLLATE，则是 Case Sensitive，即大小写敏感的。

在 MySQL 中使用 show collation 指令可以查看到 MySQL 所支持的所有 COLLATE。以 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"

Au fait, voici une digression. Il existe deux encodages dans MySQL : utf8 et utf8mb4. Veuillez oublier

utf8

et utilisez toujours

utf8mb4

dans MySQL. Il s'agit d'un problème hérité de MySQL. L'utf8 dans MySQL ne peut prendre en charge que des 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 les mots _ci, qui est l'abréviation de Case Insensitive, c'est-à-dire insensible à la casse, c'est-à-dire "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". En même temps, pour les COLLATE avec le suffixe _cs, il est Case Sensitive, 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.

Sur l'image, nous pouvons voir les règles de tri des langues de nombreux pays. Les trois 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 : la méthode de comparaison de UTF8mb4_bin consiste à traiter 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), tandis que dans utf8mb4_unicode_ci dans utf8mb4_general_ci , c'est l'équivalent de 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é remplacé par 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), ai signifie insensible à l'accent (la prononciation n'a pas d'importance), comme e, è, é, ê ; sont traités de la même manière. Lien de référence associé 1, lien de référence associé 2Niveau de réglage COLLATE et sa priorité

La base de données MySQL permet de spécifier le classement à trois niveaux :

bibliothèque
• ,

  table

  et
colonne
• . Lorsqu'elles sont spécifiées ensemble, la relation de priorité est : colonne >
  Le paramètre COLLATE peut être spécifié au 🎜niveau de l'instance🎜, au 🎜niveau de la bibliothèque🎜, au 🎜niveau de la table🎜, au 🎜niveau de la colonne🎜 et 🎜SQL 🎜. Lorsqu'elle est spécifiée simultanément, la relation de priorité est : spécification SQL > colonne > bibliothèque > 🎜🎜🎜🎜Le paramètre COLLATE au niveau de l'instance est la variable système collation_connection dans le fichier de configuration MySQL ou la commande de démarrage. 🎜🎜🎜🎜L'instruction pour définir COLLATE au niveau de la bibliothèque est la suivante : 🎜

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

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

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

CREATE TABLE table_name (
……
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT = '表注释';

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

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

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

如果列级别没有设置 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 和表上设置的一样。

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!