String columns in INFORMATION_SCHEMA tables have a collation of utf8_general_ci, which is case insensitive. However, searches in INFORMATION_SCHEMA string columns are also affected by file system case sensitivity. For values that correspond to objects that are represented in the file system, such as names of databases and tables, searches may be case sensitive if the file system is case sensitive. This section describes how to work around this issue if necessary; see also Bug #34921.

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        |+-------------+1 row in set (0.01 sec)mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA    -> WHERE SCHEMA_NAME = 'TEST';Empty set (0.00 sec)

On Windows or Mac OS X where file systems are not case sensitive, comparisons match both 'test' and 'TEST':

mysql> SELECT SCHEMA_NAME FROM
        INFORMATION_SCHEMA.SCHEMATA    -> WHERE SCHEMA_NAME =
        'test';+-------------+| SCHEMA_NAME |+-------------+| test        |+-------------+1 row in set (0.00 sec)mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA    -> WHERE SCHEMA_NAME = 'TEST';+-------------+| SCHEMA_NAME |+-------------+| TEST        |+-------------+1 row in set (0.00 sec)

The value of the lower_case_table_names system variable makes no difference in this context.

This behavior occurs because the utf8_general_ci collation is not used for INFORMATION_SCHEMA queries when searching the file system for database objects. It is a result of optimizations implemented for INFORMATION_SCHEMA searches in MySQL. For information about these optimizations, see Section 8.2.4, "Optimizing INFORMATION_SCHEMA Queries".

Searches in INFORMATION_SCHEMA string columns for values that refer to INFORMATION_SCHEMA itself do use the utf8_general_ci collation because INFORMATION_SCHEMA is a "virtual" database and is 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 |+--------------------+1 row in set (0.00 sec)mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA    -> WHERE SCHEMA_NAME = 'INFORMATION_SCHEMA';+--------------------+| SCHEMA_NAME        |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)

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 (Section 10.1.7.2, "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
        utf8_general_ci = 'test';+-------------+| SCHEMA_NAME |+-------------+| test        |+-------------+1 row in set (0.00 sec)mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA    -> WHERE SCHEMA_NAME COLLATE utf8_general_ci = 'TEST';| SCHEMA_NAME |+-------------+| test        |+-------------+1 row in set (0.00 sec)

You can also use the UPPER() or LOWER() function:

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

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:

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'

The first of those comparisons (with utf8_bin) is case sensitive; the others are not.