(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