mysql中已经存在某个库中有大小写的表,将lower_case_table_names由0改为1对已经存在的表是否有影响?
需求描述:
今天遇到了修改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 changed mysql> 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: 0 mysql> insert into tab01 select 2; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into tab01 select 3; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into tab01 select 4; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into Tab01 select 4; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into Tab01 select 5; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into Tab01 select 6; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> 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 away No connection. Trying to reconnect... Connection id: 2 Current 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.ibd mysql> insert into Tab01 select * from tab01; #继续向大写表名中插数据. Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0 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:07 tab01.ibd -rw-r----- 1 mysql mysql 98304 Jun 5 17:05 Tab01.ibd mysql> insert into Tab01 select * from tab01; Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0 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:07 tab01.ibd -rw-r----- 1 mysql mysql 98304 Jun 5 17:05 Tab01.ibd mysql> insert into Tab01 select * from tab01; Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 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:07 tab01.ibd -rw-r----- 1 mysql mysql 98304 Jun 5 17:05 Tab01.ibd mysql> insert into Tab01 select * from tab01; Query OK, 32 rows affected (0.01 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> insert into Tab01 select * from tab01; Query OK, 64 rows affected (0.01 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> insert into Tab01 select * from tab01; Query OK, 128 rows affected (0.02 sec) Records: 128 Duplicates: 0 Warnings: 0 mysql> insert into Tab01 select * from tab01; Query OK, 256 rows affected (0.00 sec) Records: 256 Duplicates: 0 Warnings: 0 mysql> insert into Tab01 select * from tab01; Query OK, 512 rows affected (0.02 sec) Records: 512 Duplicates: 0 Warnings: 0 mysql> insert into Tab01 select * from tab01; Query OK, 1024 rows affected (0.03 sec) Records: 1024 Duplicates: 0 Warnings: 0 mysql> system ls -l /mysql/data/test05 total 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 mysql> system ls -l /mysql/data/test05 total 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
