如何設定mysql的表不區分你大小寫

Linux上安裝MySQL預設是資料庫的表大小寫敏感的。修改很簡單，只要該一個mysql的設定檔就可以了。

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_1_7_1                          |
| table_1_7_2                          |
| table_1_7_3                          |
| table_1_7_3a                         |
| table_1_7_3b                         |
| table_1_7_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

從上面可以看出trend表是存在的，只不過是小寫儲存在資料庫裡。

 

 

讓MYSQL不區分表名大小寫方法其實很簡單：

1.用ROOT登入，修改/etc/my.cnf

2.在[mysqld]下加入一行：lower_case_table_names=1

3.重新啟動資料庫即可

 

[root@chicago init.d]# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/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)

從上面可以看出，此時已經不區分大小寫了。