3

I have created a database that has many scripts and many sprocs, in this db we have not paid attention to worrying about case sensitivity as it was off for my local development machine.

With all of this said, I am trying to figure out how to make the following 2 statements return the same result.

SELECT * FROM companies
SELECT * FROM Companies

Currently the lowercase one doesn't return anything and says that there isn't that table in the schema.

3xGuy
  • 2,235
  • 2
  • 29
  • 51
  • 1
    Possible duplicate of [Are table names in MySQL case sensitive?](https://stackoverflow.com/questions/6134006/are-table-names-in-mysql-case-sensitive) – Havenard Sep 22 '18 at 22:20
  • Probably your table was created with surrounding double quotes `"` which makes it case-sensitive. This is a common problem and should be avoided when naming your tables. Replace spaces in table names if you have them with `_` and get rid of `"`. – Kamil Gosciminski Sep 22 '18 at 22:23
  • does this mean that i need to drop and recreate all of the tables – 3xGuy Sep 22 '18 at 22:26
  • "Probably your table was created with surrounding double quotes " which makes it case-sensitive" @KamilG. MySQL only allows double qoutes as identifier when a certain sql mode (ANSI_QUOTES) is active.. Normally this sql mode is **not** active on a MySQL server unless activated.. – Raymond Nijland Sep 23 '18 at 00:00

1 Answers1

9

There is a variable called lower_case_table_names in my.cnf file which can be set as lower_case_table_names=1 to make the mysql table incasesensitive.

To do this in Linux below are the steps:

Open terminal and edit /etc/mysql/my.cnf

sudo nano /etc/mysql/my.cnf Underneath the [mysqld] section.add if not present in the cnf file:

lower_case_table_names = 1

Restart mysql

sudo /etc/init.d/mysql restart

Abhay
  • 403
  • 3
  • 12
  • 1
    If you have a shared hosting account, would you need to contact support to do this or is there another way to turn off the case sensitivity? – HPWD Mar 30 '20 at 14:35