6.1.3 Case Sensitivity in Names

In MySQL, databases and tables correspond to directories and files within those directories. 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-insensitive in Windows, and case-sensitive in most varieties of Unix. One prominent exception here is Mac OS X, when the default HFS+ file system is being used. However Mac OS X also supports UFS volumes, those are case sensitive on Mac OS X just like they are on any Unix. See section 1.8.3 MySQL Extensions To The SQL-92 Standard.

Note: although database and table names are case-insensitive for Windows, 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 and column aliases are case-insensitive in all cases.

Aliases on tables are case-sensitive. 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 lettercase for database and table names, adopt a consistent convention, such as always creating databases and tables using lowercase names.

One way to avoid this problem is to start mysqld with -O lower_case_table_names=1. By default this option is 1 on Windows and 0 on Unix.

If lower_case_table_names is 1 MySQL will convert all table names to lowercase on storage and lookup. (From version 4.0.2, this option also applies to database names. From 4.1.1 this also applies for table alias).

Note that if you change this option, you need to first convert your old table names to lower case before starting mysqld.

If you move MyISAM files from a Windows to a Unix disk, you may in some cases need to use the `mysql_fix_extensions' tool to fix-up the case of the file extensions in each specified database directory (lowercase `.frm', uppercase `.MYI' and `.MYD'). `mysql_fix_extensions' can be found in the `scripts' subdirectory.

User Comments

Add your own comment.