3

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.

chris85
  • 23,846
  • 7
  • 34
  • 51
Bruce
  • 1,039
  • 1
  • 9
  • 31
  • 1
    see this http://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html – Rahul Sep 10 '16 at 20:10
  • @Rahul that link, is about system wide changes also. I am specifically asking if this is possible to do without a system wide change to all mysql. I need this to be a per database solution. – Bruce Sep 10 '16 at 20:13
  • 2
    Unfortunately there is no per DB setting present. – Rahul Sep 10 '16 at 20:14
  • Do you mind writing that up as an answer, maybe include the links that show there is not a per db or per table setting, as that is SPECIFICALLY the question I was asking. I new how to change it system wide, just wasn't just if there was a work around for per instance usage. – Bruce Sep 10 '16 at 20:15
  • if they ever mark it not duplicate that is. – Bruce Sep 10 '16 at 20:16
  • 2
    `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:`-http://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html – chris85 Sep 10 '16 at 20:16
  • 1
    Thanks for the clarification, @Bruce, I have reversed my vote to close the question as a duplicate. I don't know of any way to make MySQL database names or table names case insensitive on an individual basis. The global config setting is the only option, AFAIK. – Bill Karwin Sep 10 '16 at 20:18

2 Answers2

3

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
Rahul
  • 76,197
  • 13
  • 71
  • 125
0

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'];
Bruce
  • 1,039
  • 1
  • 9
  • 31
  • 2
    You could also just set the table name to `'table_' . strtolower($_GET["user"])` in your PHP code, both when you create the table and later when you read the table. – Bill Karwin Sep 10 '16 at 20:46
  • I like that, that would cut out a bit of overhead from database connections. – Bruce Sep 10 '16 at 20:51