(mysql.info) name-case-sensitivity

Info Catalog

(mysql.info) identifier-qualifiers

(mysql.info) legal-names

 
 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 
 extensions-to-ansi. The `lower_case_table_names' system variable also
 affects how the server handles identifier case sensitivity, as
 described later in this section.
 
 * 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.
 
 *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
         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.
 

Info Catalog

(mysql.info) identifier-qualifiers

(mysql.info) legal-names