mysql case sensitive table names in queries

问题:

I have php code where first letter of database table names is in capital letter every where but table names in database are in lowercase.

What global setting should I use so that no need to change in every code file for making table names in same case as in database.

回答1:

MySQL metadata is case sensitive by default on Linux. That is if you have a table my_table, in lower case, then

select * from my_table

will succeed while

select * from MY_TABLE

will fail with some sort of table doesn't exist message.

If you want both statements to succeed, you need to put the following line lower_case_table_names = 1 in your /etc/my.cnf or wherever you keep your MySQL configuration. Be sure to add the system variable to [mysqld] section of the configuration file.

回答2:

You need to check for the mysql identifier:

 lower_case_table_names

You need to check your my.ini file located in your mysql directory. For more details on source and destination operating systems check:

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

AS EXTRACTED FROM THE URL ABOVE:

If you are using MySQL on only one platform, you do not normally have to change the lower_case_table_names variable from its default value. However, you may encounter difficulties if you want to transfer tables between platforms that differ in file system 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 arising 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 do

not 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.