mysql删除大写的表格_mysql中已经存在某个库中有大小写的表,将lower_case_table_names由0改为1对已经存在的表是否有影响?...

mysql> select * fromTab01; #查询大写表名,发现是小写表的数据

ERROR2006(HY000): MySQL server has gone away

No connection. Tryingtoreconnect...

Connection id:2

Current database: test05+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.01sec)

mysql> select * fromtab01; #查询小写表名,还是小写表的数据+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.00sec)

mysql> system ls -l /mysql/data/test05 #操作系统上大小写文件都是存在的.

total220

-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 * fromtab01; #继续向大写表名中插数据.

Query OK,4 rows affected (0.05sec)

Records:4 Duplicates: 0 Warnings: 0mysql> system ls -l /mysql/data/test05

total220

-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 * fromtab01;

Query OK,8 rows affected (0.01sec)

Records:8 Duplicates: 0 Warnings: 0mysql> system ls -l /mysql/data/test05

total220

-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 * fromtab01;

Query OK,16 rows affected (0.00sec)

Records:16 Duplicates: 0 Warnings: 0mysql> system ls -l /mysql/data/test05

total220

-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 * fromtab01;

Query OK,32 rows affected (0.01sec)

Records:32 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select * fromtab01;

Query OK,64 rows affected (0.01sec)

Records:64 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select * fromtab01;

Query OK,128 rows affected (0.02sec)

Records:128 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select * fromtab01;

Query OK,256 rows affected (0.00sec)

Records:256 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select * fromtab01;

Query OK,512 rows affected (0.02sec)

Records:512 Duplicates: 0 Warnings: 0mysql> insert into Tab01 select * fromtab01;

Query OK,1024 rows affected (0.03sec)

Records:1024 Duplicates: 0 Warnings: 0mysql> system ls -l /mysql/data/test05

total288

-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

total288

-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