VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
mysql -u root -p sakila 打开客户端。
大小写敏感性的工作原理
您的查询中没有任何东西决定它。决定它的是排序规则,而每个文本列都带有一个。字符集说明一列可以保存哪些字符,它的排序规则说明如何比较它们。您可以用COLLATE 为单个比较覆盖排序规则:
COLLATION_NAME 换成您希望这次比较使用的排序规则,把 SEARCH_TEXT 换成要匹配的值。
示例
- 2 个只有大小写不同的字符串是相等的
- 对列也是一样
小写的搜索词找到了大写的标题。
- 读取列的排序规则
ci 是不区分大小写,这就是示例 2 能匹配的原因。ai 是不区分重音,它做的是另一件事:在这个排序规则下 'e' = 'é' 为真,而在 utf8mb4_0900_as_cs 下为假。
这个表也说明了您自己的列的排序规则名称的含义。对任何表和列运行上面的查询,就能看到您在哪些规则之下工作。
- 要求区分大小写的比较
列上的 COLLATE 放弃了索引查找
列上的索引是按该列自己的排序规则构建的,所以用另一种排序规则做的比较无法用它来查找匹配的值。对上面的查询运行EXPLAIN,报告的是扫描全部 1,000 行,而不是一次查找。这在 1,000 部电影上不花什么代价,在大表上代价很大。当您需要在每个查询上做区分大小写的匹配时,字符集和排序规则指南介绍了改为以那种方式存储该列的方法。
默认值从哪里来
创建时没有指定排序规则的数据库采用服务器的,表采用数据库的,列采用表的。所以一个在其历史中任何地方都没有指定排序规则的列,是因为一个服务器设置才在做不区分大小写的比较。名称前面的@@ 读取一个服务器设置,这一对会告诉您继承了哪一个:
标识符遵循不同的规则
列名从不区分大小写。数据库名和表名是另一个问题,由lower_case_table_names 决定:
运行
SELECT @@lower_case_table_names; 查看您的情况。您无法从会话中更改它:SET GLOBAL lower_case_table_names=1 的回答是 ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable。它是一个启动设置,与服务器最初的设置方式绑定,所以请把答案当作您所在服务器的一个属性。
实用的建议不取决于这个答案。每个数据库名和表名都用同一种大小写来写,并在所有地方使用那种拼写,这样在 2 台服务器之间移动同一段 SQL 时,一个能用的查询不会变成 ERROR 1146 (42S02),也就是表不存在的错误。
总结
- 预期字符串比较忽略大小写,因为常用的排序规则以
_ci结尾。 - 从
INFORMATION_SCHEMA.COLUMNS读取列的排序规则。 - 从末尾读排序规则的名称:
_ci和_ai忽略,_cs、_as和_bin区分。 - 用
COLLATE做一次性的区分大小写比较,并接受它放弃索引查找。 - 不管
lower_case_table_names是什么,每个数据库名和表名都保持一种拼写。
另请参阅
- LIKE — 这个问题最先出现的地方
- REGEXP — 同样的规则,不同的覆盖方式
- MySQL 中的字符集和排序规则 — 为列选择一个排序规则
- 在 MySQL 中使用 utf8mb4 — 常用排序规则背后的字符集