Geek Answers Handbook

Can I make case-sensitive MySql table name on file systems that are not case-sensitive

So our target environment is Linux, which makes mysql case sensitive by default. I know that we can make our linux environment not case sensitive with the variable lower_case_table_names, but we would prefer. We were bitten several times by the inconsistency of the case, because our installation for developers is OSX, and mysql is not case sensitive.

Is there a way that table names can be case sensitive on my OSX MySql installation (5.0.83, if that matters), so that we catch the case of the table name mismatch before deployment on integration servers running on Linux?

+7
mysql case-sensitive
source share
2 answers

Set lower_case_table_names=0 in my.cnf .

If you installed via homebrew, the file is here: /usr/local/Cellar/mysql/<version>/my.cnf

Queries with tables should now be case sensitive: mysql> select count(*) from user; ERROR 1146 (42S02): Table 'xxx.user' doesn't exist mysql> select count(*) from User; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from user; ERROR 1146 (42S02): Table 'xxx.user' doesn't exist mysql> select count(*) from User; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)

+1
source share

The best thing you can do here is to correct the names of your tables so that there are no conflicts. Differentiating solely in the case is a bad idea and leads to confusion (as you probably know).

But try using single quotes around table names at creation time. This works on SUSE / Linux / MySQL 5.0 with a query browser running on Windows.

 CREATE TABLE `MySchema`.`test` ( `COMMENT` text ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `MySchema`.`Test` ( `COMMENT` text ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into MySchema.test values ('this is table test' ); insert into MySchema.Test values ('this is table Test' ); select * from MySchema.test; select * from MySchema.Test;

Do you want it to fail if a case-independent client requests a table using the wrong case? I believe that it should fail if the MySQL database is running on Linux.

Check out this link “One notable exception is Mac OS X, which is Unix-based but uses a default file system type (HFS +) that is not case sensitive. However, Mac OS X also supports UFS volumes that are case sensitive. same as any Unix. "

-one
source share

More articles:


All Articles