mysql lower case_MySQL参数之lower_case_table_names

lower_case_table_names 参数MySQL 库表 大小写，默认为0

0 库表敏感，区分大小写，指定的大小写保存文件

1 库表不敏感，不区分大小写，文件系统以小写保存

2 使用Create语句指定的大小写保存文件，但MySQL会将之转化为小写,启动的时候日志报警告

参数从0调为1

lower_case_table_names =0下操作库表都敏感

mysql> create database wx;

Query OK, 1 row affected (0.01 sec)

mysql> create database WX;

Query OK, 1 row affected (0.00 sec)

mysql> use wx;

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql> create table wx(id int);

Query OK, 0 rows affected (0.01 sec)

mysql> create table WX(id int);

Query OK, 0 rows affected (0.01 sec)

调整参数lower_case_table_names =1

此时：两表都有表信息，现在虽然有两库，但是在一库中建立表，另一库中也会出现表

mysql> use wx;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+--------------+

| Tables_in_wx |

+--------------+

| WX |

| wx |

+--------------+

2 rows in set (0.00 sec)

mysql> use WX;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+--------------+

| Tables_in_wx |

+--------------+

| WX |

| wx |

+--------------+

2 rows in set (0.00 sec)

参数lower_case_table_names =1下操作

mysql> create database ww;

Query OK, 1 row affected (0.01 sec)

mysql> create database WW;

ERROR 1007 (HY000): Can't create database 'ww'; database exists

mysql> use ww;

Database changed

mysql> create table ww(id int);

Query OK, 0 rows affected (0.01 sec)

mysql> create table WW(id int);

ERROR 1050 (42S01): Table 'ww' already exists

参数从0调到1，变化数据文件仅仅存放在小写的库中

[root@wxtest WX]# ls

db.opt

[root@wxtest wx]# ls

db.opt t1.frm t1.ibd wx.frm WX.frm wx.ibd WX.ibd