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 plays a part in
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 file system
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.
MySQL Enterprise.
lower_case_table_names
is
just one of the system variables monitored by the MySQL
Enterprise Monitor. For information about subscribing to this
service, see http://www.mysql.com/products/enterprise/advisors.html.
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 and index names are not case sensitive on any platform, nor are column aliases.
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
FROMtbl_name
AS aWHERE a.
col_name
= 1 OR A.col_name
= 2;
If you have trouble remembering the allowable lettercase for database and table names, 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 is 1 before MySQL 4.0.18 and 2 as of 4.0.18.
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. You should not set this
variable to 0 if you are running MySQL on a system that
has case-insensitive file names (such as Windows or Mac
OS X). If you force this variable to 0 with
--lower-case-table-names=0
on a case-insensitive file system 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. |
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 comparisons are not case
sensitive. This works only on file
systems 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 do not normally
have to change the
lower_case_table_names
variable
from its default value. However, you may encounter difficulties
if you want to transfer tables between platforms that differ in
file system 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 identical. To avoid data transfer problems arising
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 do not
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 and you are trying to avoid
these data transfer problems, you should set
lower_case_table_names
to 1
on all platforms to force names to be converted to
lowercase.
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 stopping
mysqld and restarting it with the new
variable setting.
User Comments
How to produce a SQL script that renames all tables in a schema to its lower case form:
select concat('rename table ', table_name, ' to ' , lower(table_name) , ';') from information_schema.tables where table_schema = 'your_schema_name';
/Anders Eriksson
Another way, via the command prompt:
cd /var/lib/mysql
for i in */*.frm; do DBASE="`dirname $i`"; TBL="`basename $i .frm`"; TBLl="`echo $TBL | tr A-Z a-z`"; if [[ "$TBL" != "$TBLl" ]] ; then echo "RENAME TABLE \`$DBASE\`.\`$TBL\` TO \`$DBASE\`.\`$TBLl\`" | mysql; fi; done
Add your own comment.