MySQL Case Insensitive Table

In Windows there are no case sensitive table names by default but in UNIX by default tables are case sensitive. You might have problem if you move your database from windows to UNIX platform and your code getting the error ‘no such table exists’ where your code works fine in window. This is because of table name are by default case sensitive in UNIX.

Example:

If you have table name which is uppercase letter named employee but when your code have the name of the table in lower case letter, it’s getting error or vice versa.

mysql> CREATE TABLE EMPLOYEE
(
ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'Autoincremented primary key’,
NAME VARCHAR(20) NULL COMMENT 'Name of the Employee'

) ENGINE=InnoDB;

mysql> Select * from employee;

Error: No such table exist

How to make case insensitive Table Name in UNIX:

mysql> alter table EMPLOYEE rename employee; 

Add /etc/my.cnf 
lower_case_table_names=1 

[root@server ~]# /etc/init.d/mysql restart 
Shutting down MySQL.. [ OK ] 
Starting MySQL.. [ OK ] 

mysql> show variables like 'lower%'; 
+------------------------+-------+ 
| Variable_name | Value | 
+------------------------+-------+ 
| lower_case_file_system | OFF | 
| lower_case_table_names | 1 | 
+------------------------+-------+ 
2 rows in set (0.00 sec) 

 

It’s Done. Now the table name is not case sensitive anymore.

Comments