How to Set mysql tables to be case insensitive

By default, MySQL installation on Linux is case sensitive to database tables. The modification is simple. You only need to modify the mysql configuration file.

Mysql> show tables;
+ -------------------------------------- +
| Tables_in_cddl |
+ -------------------------------------- +
| A1_equipment |
| A1_equipment_batch |
| Actionby |
| Actionitem |
| Actionitemcomments |
| Actionitemdetail |
| Actionitemstatus |
| Cal_cost_element |
| Cal_cost_element_stat |
| Cal_statistics |
| Changeduedate |
| Commisstionstartup |
| Copq |
| Copq_category |
| Costbreakdown |
| Daily_statistic |
| Dbstudy |
| Dccddlist |
| Define_cost_element |
| Djpmomsactivity |
| Drawing |
| Dsystem_user |
| Dtproperties |
| Duser_rights |
| Edcr |
| Edcr_2week |
| Edcr_status |
| Edcrchild |
| Engineering_action_tracking |
| Engineering_action_tracking_analysis |
| Fincostone |
| Fincostonerpt |
| Fincosttwo |
| Fincosttworpt |
| Fincostvariance |
| Fincostvariancerpt |
| Findataforchar |
| Finemployee |
| Finemployee20120910 |
| Finemployeehist |
| Finemployeehistback |
| Finexportone |
| Finexporttwo |
| Finheadcountone |
| Finheadcountonerpt |
| Finheadcounttwo |
| Finheadcounttworpt |
| Finheadcountvariance |
| Finheadcountvariancerpt |
| Finhistversioncomment |
| Finposition |
| Finpositionhist |
| Finpositionhistback |
| Finpositon20120910 |
| Flight |
| Hotel |
| Hrcontact |
| Hy_temp |
| Hyresponsetime |
| Impacteddrawingnumber |
| Jpmo_temp |
| Jpmoresponsetime |
| Meeting |
| Relatededcrnumber |
| Responsibleperson |
| Revisedscheduledate |
| Sm_temp |
| Smresponsetime |
| Sparepart |
| Sysconstraints |
| Syssegments |
| Systemparameter |
| Table_00007_1 |
| Table_00007_2 |
| Table_3167_3 |
| Table_00007_3a |
| Table_rj7_3b |
| Table_00007_3c |
| Table_appendix28 |
| Trend |
| Trenddetail |
| Visitor |
| Visitprogram |
| Vp_engdeliverablesreport |
+ -------------------------------------- +
84 rows in set (0.00 sec)

Mysql> select count (*) from TREND;
ERROR 1146 (42S02): Table 'cddl. trend' doesn' t exist

From the above we can see that the trend table exists, but it is saved in the database in lower case.

 

 

The case-insensitive MYSQL method is actually very simple:

1. Log On with ROOT and modify/etc/my. cnf

2. Add a row under [mysqld]: lower_case_table_names = 1

3. Restart the database.

 

[Root @ chicago init. d] # vi/etc/my. cnf
[Mysqld]
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
Skip-grant-tables
Lower_case_table_names = 1

 

[Root @ chicago init. d] # service mysql restart
Shutting down MySQL... [OK]
Starting MySQL ...................................... [OK]

 

Mysql> select count (*) from TREND;
+ ---------- +
| Count (*) |
+ ---------- +
| 19 |
+ ---------- +
1 row in set (0.00 sec)

Mysql> select count (*) from trend;
+ ---------- +
| Count (*) |
+ ---------- +
| 19 |
+ ---------- +
1 row in set (0.00 sec)

It can be seen from the above that the case is no longer case sensitive.