INFORMATION_SCHEMA 表中的字符串列使用 utf8mb3_general_ci 排序规则，它是不区分大小写的。然而，对于对应于文件系统中表示的对象（例如数据库和表）的值，INFORMATION_SCHEMA 字符串列中的搜索可以是区分大小写的，也可以是不区分大小写的，这取决于底层文件系统的特性以及 lower_case_table_names 系统变量的设置。例如，如果文件系统区分大小写，搜索可能会区分大小写。本节描述了这种行为以及必要时如何修改它。String columns in INFORMATION_SCHEMA tables have a collation of utf8mb3_general_ci, which is case-insensitive. However, for values that correspond to objects that are represented in the file system, such as databases and tables, searches in INFORMATION_SCHEMA string columns can be case-sensitive or case-insensitive, depending on the characteristics of the underlying file system and the lower_case_table_names system variable setting. For example, searches may be case-sensitive if the file system is case-sensitive. This section describes this behavior and how to modify it if necessary.

假设一个查询在 SCHEMATA.SCHEMA_NAME 列中搜索 test 数据库。在 Linux 上，文件系统区分大小写，所以 SCHEMATA.SCHEMA_NAME 与 'test' 的比较匹配，但与 'TEST' 的比较不匹配：Suppose that a query searches the SCHEMATA.SCHEMA_NAME column for the test database. On Linux, file systems are case-sensitive, so comparisons of SCHEMATA.SCHEMA_NAME with 'test' match, but comparisons with 'TEST' do not:

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 会导致第二个查询返回与第一个查询相同的（非空）结果。These results occur with the lower_case_table_names system variable set to 0. A lower_case_table_names setting of 1 or 2 causes the second query to return the same (nonempty) result as the first query.

在 Windows 或 macOS 上，文件系统不区分大小写，因此比较匹配 'test' 和 'TEST'：On Windows or macOS, file systems are not case-sensitive, so comparisons match both 'test' and '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 的值无关紧要。The value of lower_case_table_names makes no difference in this context.

上述行为之所以发生，是因为在为 INFORMATION_SCHEMA 查询查找对应于文件系统中表示的对象的值时，没有使用 utf8mb3_general_ci 排序规则。The preceding behavior occurs because the utf8mb3_general_ci collation is not used for INFORMATION_SCHEMA queries when searching for values that correspond to objects represented in the file system.

如果在 INFORMATION_SCHEMA 列上进行字符串操作的结果与预期不同，一种解决方法是使用显式的 COLLATE 子句来强制使用合适的排序规则（参见 第 12.8.1 节，“在 SQL 语句中使用 COLLATE”）。例如，为了执行不区分大小写的搜索，请在 INFORMATION_SCHEMA 列名上使用 COLLATE：If the result of a string operation on an INFORMATION_SCHEMA column differs from expectations, a workaround is to use an explicit COLLATE clause to force a suitable collation (see Section 12.8.1, “Using COLLATE in SQL Statements”). For example, to perform a case-insensitive search, use COLLATE with the INFORMATION_SCHEMA column name:

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME COLLATE utf8mb3_general_ci = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME COLLATE utf8mb3_general_ci = 'TEST';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+

您还可以使用 UPPER() 或 LOWER() 函数：You can also use the UPPER() or LOWER() function:

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

尽管如上所示，即使在区分大小写的文件系统平台上也可以进行不区分大小写的比较，但这不一定总是正确的做法。在这样的平台上，可能存在多个仅在字母大小写上不同的对象。例如，名为 city、CITY 和 City 的表可以同时存在。请考虑搜索是应该匹配所有这些名称还是仅匹配其中一个，并相应地编写查询。以下比较中的第一个（使用 utf8mb3_bin）是区分大小写的；其他的则不区分大小写：Although a case-insensitive comparison can be performed even on platforms with case-sensitive file systems, as just shown, it is not necessarily always the right thing to do. On such platforms, it is possible to have multiple objects with names that differ only in lettercase. For example, tables named city, CITY, and City can all exist simultaneously. Consider whether a search should match all such names or just one and write queries accordingly. The first of the following comparisons (with utf8mb3_bin) is case-sensitive; the others are not:

WHERE TABLE_NAME COLLATE utf8mb3_bin = 'City'
WHERE TABLE_NAME COLLATE utf8mb3_general_ci = 'city'
WHERE UPPER(TABLE_NAME) = 'CITY'
WHERE LOWER(TABLE_NAME) = 'city'

在 INFORMATION_SCHEMA 的字符串列中搜索引用 INFORMATION_SCHEMA 本身的值时，确实使用 utf8mb3_general_ci 排序规则，因为 INFORMATION_SCHEMA 是一个 “虚拟” 数据库，并未在文件系统中表示。例如，与 SCHEMATA.SCHEMA_NAME 的比较会匹配 'information_schema' 或 'INFORMATION_SCHEMA'，而不考虑平台：Searches in INFORMATION_SCHEMA string columns for values that refer to INFORMATION_SCHEMA itself do use the utf8mb3_general_ci collation because INFORMATION_SCHEMA is a “virtual” database not represented in the file system. For example, comparisons with SCHEMATA.SCHEMA_NAME match 'information_schema' or 'INFORMATION_SCHEMA' regardless of platform:

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 |
+--------------------+