MySQL Identifier Case Sensitivity

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.

MySQL Enterprise.
lower_case_table_names is just one of the system variables monitored by
the MySQL Network Monitoring and Advisory Service. For information about
subscribing to this service see,

http://www.mysql.com/products/enterprise/advisors.html.

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 and stored routine names are not case sensitive on any
platform, nor are column aliases. Trigger names are case sensitive.

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 problems
caused 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.

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. Note that if you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive filesystem and access MyISAM tablenames using different lettercases, index corruption may result.
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 and table aliases.
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. Note: This works only on filesystems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.

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.

Object names may be considered duplicates if their uppercase forms are
equal according to a binary collation. That is true for names of cursors,
conditions, functions, procedures, savepoints, and routine local variables. It
is not true for names of columns, constraints, databases, statements prepared
with PREPARE, tables, triggers, users, and
user-defined variables.