如何设置mysql的表不区分你大小写_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)

从上面可以看出，此时已经不区分大小写了。