之前我创建了一个Student1表。后来删除了。但是程序里判断Student1是否存在时发现该表仍然存在。但是在查询其数据时又报该表不存在的错误。
判断是否存在用的以下SQL.
SELECT table_name FROM information_schema.`TABLES` WHERE table_name = 'Student1';我打开MYSQL Query Browser在里面运行这个SQL,真的返回了'Student1'.然后我又运行SELECT * FROM information_schema.`TABLES` T where t.table_name = 'Student1'; 返回0条数据。然后我试着不用*而是逐个添加查询的字段。
一直到SELECT `table_catalog`, `table_schema`, `table_name`, `table_type`, `engine`, `version` FROM information_schema.`TABLES` WHERE table_name = 'Student1'; 都能查到数据(见下)。table_catalog table_schema table_name table_type engine version
japan Student1 BASE TABLE InnoDB 10当继续添加`row_format`字段时,SELECT `table_catalog`, `table_schema`, `table_name`, `table_type`, `engine`, `version`, `row_format` FROM information_schema.`TABLES` WHERE table_name = 'Student1'; 就突然查不到数据了。这个是怎么回事啊?太困扰我了,这是bug吗,求达人指教!!
判断是否存在用的以下SQL.
SELECT table_name FROM information_schema.`TABLES` WHERE table_name = 'Student1';我打开MYSQL Query Browser在里面运行这个SQL,真的返回了'Student1'.然后我又运行SELECT * FROM information_schema.`TABLES` T where t.table_name = 'Student1'; 返回0条数据。然后我试着不用*而是逐个添加查询的字段。
一直到SELECT `table_catalog`, `table_schema`, `table_name`, `table_type`, `engine`, `version` FROM information_schema.`TABLES` WHERE table_name = 'Student1'; 都能查到数据(见下)。table_catalog table_schema table_name table_type engine version
japan Student1 BASE TABLE InnoDB 10当继续添加`row_format`字段时,SELECT `table_catalog`, `table_schema`, `table_name`, `table_type`, `engine`, `version`, `row_format` FROM information_schema.`TABLES` WHERE table_name = 'Student1'; 就突然查不到数据了。这个是怎么回事啊?太困扰我了,这是bug吗,求达人指教!!
请仔细看我的文章:
MySQL中的表名大小写区不区分的问题及解决办法
http://hi.baidu.com/hexiong/blog/item/c586fd1f258c7a0a314e156a.html
有一个启动参数--lower-case-table-names=0
我估计这个真的是bug了,我重复试验了很多遍,这样的问题经常发生,刚删掉就能查到。要重启数据库才行。
mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | ON |
| lower_case_table_names | 1 |
+------------------------+-------+
2 rows in set (0.00 sec)你但到“原表名”,带大小写,有,但并不一定。要以全小写名称为准。尽管最后冗余了一行,让你以为没有删除。请看示例:
mysql> create table Tb(id int);
Query OK, 0 rows affected (0.06 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| Tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.01 sec)mysql> drop table Tb;
Query OK, 0 rows affected (0.05 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| Tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='tb';
Empty set (0.00 sec)mysql>
Query OK, 0 rows affected (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)mysql> drop table Tb;
Query OK, 0 rows affected (0.02 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='Tb';
Empty set (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='tb';
Empty set (0.01 sec)
mysql> show variables like "lower%" ;
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | ON |
| lower_case_table_names | 1 |
+------------------------+-------+
2 rows in set (0.00 sec)楼上你啥系统,啥版本?
我是在xp下测试的。5.x版本都是一样的结果。mysql> select @@version;
+------------------+
| @@version |
+------------------+
| 5.1.46-community |
+------------------+
1 row in set (0.00 sec)顺便说一句, 在windows下,文件名是不区分大小写的。在元信息表里头存储的表名,也只有那个纯小写形式才是最终有效的,如果小写的表名那条记录不存在,则意味着表被删除。至于其它的中间形式,所谓的原始表名,存不存在对结果没有影响。