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.

You must be login first or sign-up for an account to post comments.