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.21, “INFORMATION_SCHEMA
Optimization”.
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.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 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.
User Comments
Add your own comment.