Bruce Bruce - 2 months ago 8
MySQL Question

How do I override MySQL Case Sensitivity on per database or table base?

I keep reading solutions about editing the my.cnf file, however I want case sensitivity on all databases except 1 and editing the my.cnf will change it for all databases.

Is there a way to specifically disable case sensitivity via table insert and/or database creation so I can disable sensitivity either per table or per database instead of across all databases?

I am using mysql, php, and pdo database connections in case that is important to a solution.

Answer

Unfortunately there is no per DB setting present. Per MySQL Documentation you can use lower_case_table_names system variable while starting mysqld but that as well Global and not a per DB solution which you are looking for. As already commented by @cris85 ... linked documentation also states below alternative

To convert one or more entire databases, dump them before setting lower_case_table_names, then drop the databases, and reload them after setting lower_case_table_names:

Use mysqldump to dump each database:

mysqldump --databases db1 > db1.sql
mysqldump --databases db2 > db2.sql
...

Do this for each database that must be recreated.

Use DROP DATABASE to drop each database.

Stop the server, set lower_case_table_names, and restart the server.

Reload the dump file for each database. Because lower_case_table_names is set, each database and table name will be converted to lowercase as it is recreated:

mysql < db1.sql
mysql < db2.sql