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 7.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 9.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.