MySQL Case Insensitive Table

MySQL

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

[[email protected] ~]# /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.