CODEWITHSUNDEEP
mysql
Bruce
Bruce

Reputation: 1071

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.

Upvotes: 3

Views: 2272

Answers (2)

Bruce
Bruce

Reputation: 1071

This is NOT a solution to the question asked, but is a solution to the problem that brought me to ask this question.

The actual problem I was having was that I have dynamic tables in a database called table_.$username These tables are called when a profile user is accessed.

For for example 

http://www.myurl.com/profile.php?user=admin

would return 

table_admin

The problem was

http://www.myurl.com/profile.php?user=Admin

would not return the table_admin as it would search for table_Admin which did not exist.

My work around solution was as follows:

$user = $_GET['user']
$stmt=$db->prepare('SELECT username FROM members WHERE username = :user');
$stmt->bindParam(':user', $user);
$stmt->execute();
$row = $stmt->fetch();
$user = $row['username'];

Upvotes: 0

Rahul
Rahul

Reputation: 77876

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

Upvotes: 3

Related Questions