MySQL 查询中的大小写问题

786 阅读3分钟

   MySQL 中字符串的搜索以及比较是否区分大小写通常取决于所使用的字符序,即 collation

⒈ collation 介绍

   collation 是一组与字符集排序和比较相关的规则的集合。

   在 MySQL 8.0 版本中,目前共支持 270 种字符序,41 种字符集。 MySQL 8.0 中支出的 collation MySQL 8.0 中支持的字符集

   在 MySQL 中:

  • 每个 collation 对应一个特定的字符集
  • 大小写敏感的 collation 都是以 bin 或 cs 结尾,大小写不敏感的 collation 都是以 _ci 结尾
  • 二进制的字符集和 collation 将字符串视为一个字节序列
  • 每个字符集都有一个默认的 collation,默认的 collation 通常都是大小写不敏感的

⒉ collation 设置

   在 MySQL 中,collation 可以被设置到 column 一级,甚至一个 column 可以有特定的 collation 和特定的字符集。但在大多数情况下,一个 table 使用一个 collation,甚至一个 database 使用一个 collation,抑或是所有的 database 都是用同一个 collation。

  • column 一级的 collation 可以通过 CREATE TABLE 语句或 ALTER TABLE 语句设置,默认使用其所属 table 的 collation
  • table 一级的 collation 可以通过 CREATE TABLE 语句或 ALTER TABLE 语句设置,默认使用其所在的 database 的 collation
  • database 一级的 collation 可以通过 CREATE DATABASE 语句或 ALTER DATABASE 语句设置,默认使用的是 MySQL 环境变量 collation_server 的值

   在 where 语句中作为查询条件使用的字符串所使用的 collation 取决于 MySQL 环境变量 collation_connection 的值 MySQL 8.0 中 collation_server 和 collation_connection 的值

对于 unique 索引,如果索引所在的 column 是大小写不敏感的,那么在写入数据时可能会有问题,例如 'a' 和 'A' 不可能同时写入成功

⒊ 通过设置 collation 来决定是否区分大小写

   对于非二进制字符串(char | varchar | text)的比较,使用操作数的 collation 作为依据;对于二进制字符串(binary | varbinary | blob)的比较,根据操作数每个字节的数值来进行。

   非二进制字符串与二进制字符串进行比较,按照二进制字符串比较规则进行处理。

   在 MySQL 8.0 中,默认的字符集和 collation 分别为 utf8mb4 和 utf8mb4_general_ci,所以默认情况下非二进制的比较都是大小写不敏感的。例如,对于 col_name LIKE 'a%',所有以 a 或 A 开头的值都会被检索到。如果要使检索对大小写敏感,则需要将任一操作数的 collation 改为大小写敏感类型的或二进制类型的。

col_name COLLATE utf8mb4_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE utf8mb4_general_cs
col_name COLLATE utf8mb4_general_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE utf8mb4_general_bin

   ⓵ 要使一个大小写敏感的非二进制字符串的比较变成对大小写不敏感,可以使用 collate 函数改变操作数的 collation

   ⓶ 要使一个二进制字符串的比较变成对大小写不敏感,则需要先转换操作数的字符集,然后改变操作数的 collation

   ⓷ 判断一个字符串是作为二进制字符串参与比较,还是作为非二进制字符串参与比较,可以使用 collation 函数