Lowercase Table Names in MySQL

By default, MySQL supports case sensitive table names. Uppercase and lowercase versions of a table name point two different tables in reality.

mysql> create table exp_tbl (id int(11)); 

will create MYD, MYI, frm files on the filesystem such as exp_tbl.MYD, exp_tbl.MYI and exp_tbl.frm respectively (unless the storage engine is chosen as InnoDB)

On the contrary,

mysql> create table EXP_TBL (id int(11));

will create EXP_TBL.MYD, EXP_TBL.MYI and EXP_TBL.frm. These are different tables as well.To change the feature and to have mysql to point to the same table when referenced by both uppercase and lowercase versions, we should add/change a feature in my.cnf file. We should add

lower_case_table_names = 1

under [mysqld] section.

For the changes to take effect, we should restart mysql daemon.

If we are changing this feature after we have created some uppercase tables which have upper case filenames in the filesystem, we should make sure that all the files are in the lowercase. Mysql will search for lowercase files (not the extension field, MYD, MYI, frm should stay same) in the filesystem for uppercase and lowercase table-named sql queries.