(mysql.info.gz) Name case sensitivity
Info Catalog
(mysql.info.gz) Identifier qualifiers
(mysql.info.gz) Legal names
9.2.2 Identifier Case Sensitivity
---------------------------------
In MySQL, databases correspond to directories within the data directory.
Tables within a database correspond 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 not case sensitive in Windows, and
case sensitive in most varieties of Unix. 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.
Extensions to ANSI.
* 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 query. The following query 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 names, index names, and column aliases are not case sensitive on
any platform.
Table aliases are case sensitive before MySQL 4.1.1. The following
query would not work 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;
If you have trouble remembering the allowable lettercase for database
and table names, adopt a consistent convention, such as always creating
databases and tables using lowercase names.
How table and database names are stored on disk and used in MySQL is
defined by the `lower_case_table_names' system variable, which you can
set when starting `mysqld'. `lower_case_table_names' can take one of
the following values:
*Value* *Meaning*
`0' Table and database names are stored on disk using the lettercase
specified in the `CREATE TABLE' or `CREATE DATABASE' statement.
Name comparisons are case sensitive. This is the default on
Unix systems. Note that if you force this to 0 with
`--lower-case-table-names=0' on a case-insensitive filesystem
and access `MyISAM' tablenames using different lettercases, this
may lead to index corruption.
`1' Table names are stored in lowercase on disk and name comparisons
are not case sensitive. MySQL converts all table names to
lowercase on storage and lookup. This behavior also applies to
database names as of MySQL 4.0.2, and to table aliases as of
4.1.1. This value is the default on Windows and Mac OS X systems.
`2' Table and database names are stored on disk using the lettercase
specified in the `CREATE TABLE' or `CREATE DATABASE' statement,
but MySQL converts them to lowercase on lookup. Name
on filesystems that are not case sensitive! `InnoDB' table names
are stored in lowercase, as for `lower_case_table_names=1'.
Setting `lower_case_table_names' to `2' can be done as of MySQL
4.0.18.
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 those names are considered the same. To avoid data transfer
problems stemming from database or table name lettercase, 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 queries always refer to your
database and table names with the correct lettercase on Windows. If
you transfer your queries to Unix, where lettercase is
significant, they will not work if the lettercase is incorrect.
Note that before setting `lower_case_table_names' to 1 on Unix, you must
first convert your old database and table names to lowercase before
restarting `mysqld'.
Info Catalog
(mysql.info.gz) Identifier qualifiers
(mysql.info.gz) Legal names
automatically generated byinfo2html