10.8.7 在 INFORMATION_SCHEMA 搜索中使用归类

INFORMATION_SCHEMAtable 中的字符串列的排序规则为utf8_general_ci，不区分大小写。但是，对于与文件系统中 table 示的对象相对应的值(例如数据库和 table)，在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的值在这种情况下没有区别。

出现上述现象的原因是，当搜索与文件系统中 table 示的对象相对应的值时，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应用于比较值无效。

您还可以使用UPPER()或LOWER()函数：

WHERE UPPER(SCHEMA_NAME) = 'TEST'
WHERE LOWER(SCHEMA_NAME) = 'test'

尽管即使在具有区分大小写的文件系统的平台上也可以执行不区分大小写的比较，如所示，但这不一定总是正确的做法。在这样的平台上，可能有多个名称仅在字母大小写不同的对象。例如，名为city，CITY和City的 table 可以同时存在。考虑搜索是应该匹配所有这样的名称还是仅匹配一个，然后相应地编写查询。以下比较中的第一个(使用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是文件系统中未 table 示的“虚拟”数据库。例如，与SCHEMATA.SCHEMA_NAME的比较会匹配'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 |
+--------------------+