表中的字符串列具有不区分大小写INFORMATION_SCHEMA
的排序规则。utf8_general_ci
但是,对于与文件系统中表示的对象(例如数据库和表)相对应的值,INFORMATION_SCHEMA
字符串列中的搜索可以区分大小写或不区分大小写,具体取决于底层文件系统的特征和lower_case_table_names
系统变量。例如,如果文件系统区分大小写,则搜索可能区分大小写。本节描述此行为以及如何在必要时修改它;另请参见错误 #34921。
假设查询在
SCHEMATA.SCHEMA_NAME
列中
搜索test
数据库。在 Linux 上,文件系统区分大小写,因此
SCHEMATA.SCHEMA_NAME
与
'test'
匹配的比较,但与
'TEST'
不匹配的比较:
mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test |
+-------------+
mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'TEST';
Empty set (0.00 sec)
这些结果在
lower_case_table_names
系统变量设置为 0 时出现。将 的值更改
lower_case_table_names
为 1 或 2 会导致第二个查询返回与第一个查询相同的(非空)结果。
在 Windows 或 macOS 上,文件系统不区分大小写,因此比较匹配'test'
和
'TEST'
:
mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test |
+-------------+
mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'TEST';
+-------------+
| SCHEMA_NAME |
+-------------+
| TEST |
+-------------+
的值
lower_case_table_names
在这种情况下没有区别。
出现上述行为是因为
在搜索与文件系统中表示的对象相对应的值时,utf8_general_ci
排序规则不用于
INFORMATION_SCHEMA
查询。这是为INFORMATION_SCHEMA
搜索实施的文件系统扫描优化的结果。有关这些优化的信息,请参阅
第 8.2.3 节,“优化 INFORMATION_SCHEMA 查询”。
如果列上的字符串操作的结果
INFORMATION_SCHEMA
与预期不同,解决方法是使用显式
COLLATE
子句强制进行合适的排序规则(请参阅第 10.8.1 节,“在 SQL 语句中使用 COLLATE”)。例如,要执行不区分大小写的搜索,请使用COLLATE
列INFORMATION_SCHEMA
名:
mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME COLLATE utf8_general_ci = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test |
+-------------+
mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME COLLATE utf8_general_ci = 'TEST';
+-------------+
| SCHEMA_NAME |
+-------------+
| test |
+-------------+
在前面的查询中,将
COLLATE
子句应用于
INFORMATION_SCHEMA
列名很重要。应用于
COLLATE
比较值没有效果。
WHERE UPPER(SCHEMA_NAME) = 'TEST'
WHERE LOWER(SCHEMA_NAME) = 'test'
尽管不区分大小写的比较甚至可以在具有区分大小写的文件系统的平台上执行,如刚才所示,但它不一定总是正确的做法。在这样的平台上,可能有多个名称仅在字母大小写上不同的对象。例如,名为
city
、CITY
和
的表City
可以同时存在。考虑搜索应该匹配所有这样的名字还是只匹配一个并相应地编写查询。以下比较中的第一个(与utf8_bin
)区分大小写;其他不是:
WHERE TABLE_NAME COLLATE utf8_bin = 'City'
WHERE TABLE_NAME COLLATE utf8_general_ci = 'city'
WHERE UPPER(TABLE_NAME) = 'CITY'
WHERE LOWER(TABLE_NAME) = 'city'
在INFORMATION_SCHEMA
字符串列中搜索引用INFORMATION_SCHEMA
自身的值确实使用utf8_general_ci
排序规则,因为文件系统中没有INFORMATION_SCHEMA
表示
“虚拟”数据库。例如,与
SCHEMATA.SCHEMA_NAME
match
'information_schema'
或
'INFORMATION_SCHEMA'
不管平台的比较:
mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'information_schema';
+--------------------+
| SCHEMA_NAME |
+--------------------+
| information_schema |
+--------------------+
mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'INFORMATION_SCHEMA';
+--------------------+
| SCHEMA_NAME |
+--------------------+
| information_schema |
+--------------------+