跳转到主要内容

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
除非您另有要求,MySQL 的字符串比较会忽略字母大小写。这让人意外一次,之后又会咬人一口,所以值得了解是什么决定了它,以及如何为单个查询改变它。 开始之前,请用 mysql -u root -p sakila 打开客户端。

大小写敏感性的工作原理

您的查询中没有任何东西决定它。决定它的是排序规则,而每个文本列都带有一个。字符集说明一列可以保存哪些字符,它的排序规则说明如何比较它们。您可以用 COLLATE 为单个比较覆盖排序规则:
把 COLLATION_NAME 换成您希望这次比较使用的排序规则,把 SEARCH_TEXT 换成要匹配的值。

示例

  1. 2 个只有大小写不同的字符串是相等的

  1. 对列也是一样

小写的搜索词找到了大写的标题。

  1. 读取列的排序规则

请从末尾读排序规则的名称。ci 是不区分大小写,这就是示例 2 能匹配的原因。ai 是不区分重音,它做的是另一件事:在这个排序规则下 'e' = 'é' 为真,而在 utf8mb4_0900_as_cs 下为假。 这个表也说明了您自己的列的排序规则名称的含义。对任何表和列运行上面的查询,就能看到您在哪些规则之下工作。

  1. 要求区分大小写的比较

它对列的作用方式相同。

列上的 COLLATE 放弃了索引查找

列上的索引是按该列自己的排序规则构建的,所以用另一种排序规则做的比较无法用它来查找匹配的值。对上面的查询运行 EXPLAIN,报告的是扫描全部 1,000 行,而不是一次查找。这在 1,000 部电影上不花什么代价,在大表上代价很大。当您需要在每个查询上做区分大小写的匹配时,字符集和排序规则指南介绍了改为以那种方式存储该列的方法。

默认值从哪里来

创建时没有指定排序规则的数据库采用服务器的,表采用数据库的,列采用表的。所以一个在其历史中任何地方都没有指定排序规则的列,是因为一个服务器设置才在做不区分大小写的比较。名称前面的 @@ 读取一个服务器设置,这一对会告诉您继承了哪一个:
您的服务器会用它启动时的设置来回答,所以请把结果当作关于您自己安装的事实,而不是关于 MySQL 的事实。

标识符遵循不同的规则

列名从不区分大小写。数据库名和表名是另一个问题,由 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 是什么,每个数据库名和表名都保持一种拼写。

另请参阅