需求描述:

　　今天遇到了修改lower_case_table_names参数的问题,想了下,如果原来里面有表,表名有大小写的,

　　如果将lower_case_table_names从默认的0改为1之后,那么对于原来的表有没有影响.做个实验,在此

　　记录下.

操作过程:

1.原来未修改之前,库中创建大小写区分的表

mysql> drop database test05;Query OK, 2 rows affected (0.05 sec)mysql> create database test05;Query OK, 1 row affected (0.00 sec)mysql> use test05;Database changedmysql> create table tab01(id int); #创建小写的表名Query OK, 0 rows affected (0.05 sec)mysql> create table Tab01(id int); #创建大写的表名Query OK, 0 rows affected (0.04 sec)mysql> insert into tab01 select 1; #分别向两个表中插入数据Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tab01 select 2;Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tab01 select 3;Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tab01 select 4;Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select 4;Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select 5;Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select 6;Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from tab01; #小写表名中的数据+------+| id |+------+| 1 || 2 || 3 || 4 |+------+4 rows in set (0.00 sec)mysql> select * from Tab01; #大写表名中的数据+------+| id |+------+| 4 || 5 || 6 |+------+3 rows in set (0.00 sec)

2.将lower_case_table_name参数修改为1,重启实例

mysql> select * from Tab01; #查询大写表名,发现是小写表的数据ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 2Current database: test05+------+| id |+------+| 1 || 2 || 3 || 4 |+------+4 rows in set (0.01 sec)mysql> select * from tab01; #查询小写表名,还是小写表的数据+------+| id |+------+| 1 || 2 || 3 || 4 |+------+4 rows in set (0.00 sec)mysql> system ls -l /mysql/data/test05 #操作系统上大小写文件都是存在的.total 220-rw-r----- 1 mysql mysql 61 Jun 5 17:03 db.opt-rw-r----- 1 mysql mysql 8556 Jun 5 17:03 tab01.frm-rw-r----- 1 mysql mysql 8556 Jun 5 17:04 Tab01.frm-rw-r----- 1 mysql mysql 98304 Jun 5 17:04 tab01.ibd-rw-r----- 1 mysql mysql 98304 Jun 5 17:05 Tab01.ibdmysql> insert into Tab01 select * from tab01; #继续向大写表名中插数据.Query OK, 4 rows affected (0.05 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> system ls -l /mysql/data/test05total 220-rw-r----- 1 mysql mysql 61 Jun 5 17:03 db.opt-rw-r----- 1 mysql mysql 8556 Jun 5 17:03 tab01.frm-rw-r----- 1 mysql mysql 8556 Jun 5 17:04 Tab01.frm-rw-r----- 1 mysql mysql 98304 Jun 5 17:07 tab01.ibd-rw-r----- 1 mysql mysql 98304 Jun 5 17:05 Tab01.ibdmysql> insert into Tab01 select * from tab01;Query OK, 8 rows affected (0.01 sec)Records: 8 Duplicates: 0 Warnings: 0mysql> system ls -l /mysql/data/test05total 220-rw-r----- 1 mysql mysql 61 Jun 5 17:03 db.opt-rw-r----- 1 mysql mysql 8556 Jun 5 17:03 tab01.frm-rw-r----- 1 mysql mysql 8556 Jun 5 17:04 Tab01.frm-rw-r----- 1 mysql mysql 98304 Jun 5 17:07 tab01.ibd -rw-r----- 1 mysql mysql 98304 Jun 5 17:05 Tab01.ibdmysql> insert into Tab01 select * from tab01;Query OK, 16 rows affected (0.00 sec)Records: 16 Duplicates: 0 Warnings: 0mysql> system ls -l /mysql/data/test05total 220-rw-r----- 1 mysql mysql 61 Jun 5 17:03 db.opt-rw-r----- 1 mysql mysql 8556 Jun 5 17:03 tab01.frm-rw-r----- 1 mysql mysql 8556 Jun 5 17:04 Tab01.frm-rw-r----- 1 mysql mysql 98304 Jun 5 17:07 tab01.ibd-rw-r----- 1 mysql mysql 98304 Jun 5 17:05 Tab01.ibdmysql> insert into Tab01 select * from tab01;Query OK, 32 rows affected (0.01 sec)Records: 32 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select * from tab01;Query OK, 64 rows affected (0.01 sec)Records: 64 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select * from tab01;Query OK, 128 rows affected (0.02 sec)Records: 128 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select * from tab01;Query OK, 256 rows affected (0.00 sec)Records: 256 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select * from tab01;Query OK, 512 rows affected (0.02 sec)Records: 512 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select * from tab01;Query OK, 1024 rows affected (0.03 sec)Records: 1024 Duplicates: 0 Warnings: 0mysql> system ls -l /mysql/data/test05total 288-rw-r----- 1 mysql mysql 61 Jun 5 17:03 db.opt-rw-r----- 1 mysql mysql 8556 Jun 5 17:03 tab01.frm-rw-r----- 1 mysql mysql 8556 Jun 5 17:04 Tab01.frm-rw-r----- 1 mysql mysql 163840 Jun 5 17:08 tab01.ibd #插入多次发现,只有小写表名的文件大小在变化.-rw-r----- 1 mysql mysql 98304 Jun 5 17:05 Tab01.ibdmysql> system ls -l /mysql/data/test05total 288-rw-r----- 1 mysql mysql 61 Jun 5 17:03 db.opt-rw-r----- 1 mysql mysql 8556 Jun 5 17:03 tab01.frm-rw-r----- 1 mysql mysql 8556 Jun 5 17:04 Tab01.frm-rw-r----- 1 mysql mysql 163840 Jun 5 17:08 tab01.ibd-rw-r----- 1 mysql mysql 98304 Jun 5 17:05 Tab01.ibd

备注:发现将lower_case_table_names参数从默认的0改为1之后,对于原来表的使用是有影响的,查询的也只是查询小写的表,插入也只是插入到小写的表中,虽然通过show tables能够看到大小写的表同时存在.

文档创建时间:2018年6月5日17:20:23