9.2.2. Identifier Case Sensitivity
In MySQL, databases correspond to directories within the data
directory. Each table within a database corresponds to at least
one file within the database directory (and possibly more,
depending on the storage engine). Consequently, the case
sensitivity of the underlying operating system determines the
case sensitivity of database and table names. This means
database and table names are case sensitive in most varieties of
Unix, and not case sensitive in Windows. One notable exception
is Mac OS X, which is Unix-based but uses a default filesystem
type (HFS+) that is not case sensitive. However, Mac OS X also
supports UFS volumes, which are case sensitive just as on any
Unix. See Section 1.9.4, “MySQL Extensions to Standard SQL”. The
lower_case_table_names system variable also
affects how the server handles identifier case sensitivity, as
described later in this section.
Note: Although database and
table names are not case sensitive on some platforms, you should
not refer to a given database or table using different cases
within the same statement. The following statement would not
work because it refers to a table both as
my_table and as MY_TABLE:
mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
Column, index, stored routine, and trigger names are not case
sensitive on any platform, nor are column aliases.
By default, table aliases are case sensitive on Unix, but not so
on Windows or Mac OS X. The following statement would not work
on Unix, because it refers to the alias both as
a and as A:
mysql> SELECT col_name FROM tbl_name AS a
-> WHERE a.col_name = 1 OR A.col_name = 2;
However, this same statement is permitted on Windows. To avoid
being caught out by such differences, it is best to adopt a
consistent convention, such as always creating and referring to
databases and tables using lowercase names. This convention is
recommended for maximum portability and ease of use.
How table and database names are stored on disk and used in
MySQL is affected by the
lower_case_table_names system variable, which
you can set when starting mysqld.
lower_case_table_names can take the values
shown in the following table. On Unix, the default value of
lower_case_table_names is 0. On Windows the
default value is 1. On Mac OS X, the default value is 2.
If you are using MySQL on only one platform, you don't normally
have to change the lower_case_table_names
variable. However, you may encounter difficulties if you want to
transfer tables between platforms that differ in filesystem case
sensitivity. For example, on Unix, you can have two different
tables named my_table and
MY_TABLE, but on Windows these two names are
considered identical. To avoid data transfer problems stemming
from lettercase of database or table names, you have two
options:
Use lower_case_table_names=1 on all
systems. The main disadvantage with this is that when you
use SHOW TABLES or SHOW
DATABASES, you don't see the names in their
original lettercase.
-
Use lower_case_table_names=0 on Unix and
lower_case_table_names=2 on Windows. This
preserves the lettercase of database and table names. The
disadvantage of this is that you must ensure that your
statements always refer to your database and table names
with the correct lettercase on Windows. If you transfer your
statements to Unix, where lettercase is significant, they do
not work if the lettercase is incorrect.
Exception: If you are using
InnoDB tables, you should set
lower_case_table_names to 1 on all
platforms to force names to be converted to lowercase.
Note that if you plan to set the
lower_case_table_names system variable to 1
on Unix, you must first convert your old database and table
names to lowercase before restarting mysqld
with the new variable setting.
