mysql> select table_schema,table_name from information_schema.tables where table_name = 'test1' ;
+-----------------+------------+
| table_schema | table_name |
+-----------------+------------+
| workoperations1 | test1 |
| workoperations2 | test1 |
| workoperations3 | test1 |
| workoperations4 | test1 |
+-----------------+------------+
4 rows in set
mysql> select table_schema,table_name from information_schema.tables where table_name = 'TEST1' ;
Empty set
若改为
select * from information_schema.tables where table_name = 'test1' ;
select * from information_schema.tables where table_name = 'TEST1' ;
都能查出4条记录。
原因在于
mysql> explain select table_schema,table_name from information_schema.tables where table_name = 'TEST1' ;
+----+-------------+--------+------+---------------+------------+---------+------+------+--------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------------+---------+------+------+--------------------------------------------------+
| 1 | SIMPLE | tables | ALL | NULL | TABLE_NAME | NULL | NULL | NULL | Using where; Skip_open_table; Scanned 1 database |
+----+-------------+--------+------+---------------+------------+---------+------+------+--------------------------------------------------+
1 row in set
mysql> explain select * from information_schema.tables where table_name = 'TEST1' ;
+----+-------------+--------+------+---------------+------------+---------+------+------+--------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------------+---------+------+------+--------------------------------------------------+
| 1 | SIMPLE | tables | ALL | NULL | TABLE_NAME | NULL | NULL | NULL | Using where; Open_full_table; Scanned 1 database |
+----+-------------+--------+------+---------------+------------+---------+------+------+--------------------------------------------------+
1 row in set
两条语句的执行计划不同。
要避免出现大小写导致查不到数据字典的情况还是用*代替table_name。
附
http://dev.mysql.com/doc/refman/5.1/en/information-schema-optimization.html
TABLE_CATALOG | SKIP_OPEN_TABLE |
TABLE_SCHEMA | SKIP_OPEN_TABLE |
TABLE_NAME | SKIP_OPEN_TABLE |
TABLE_TYPE | OPEN_FRM_ONLY |
ENGINE | OPEN_FRM_ONLY |
VERSION | OPEN_FRM_ONLY |
ROW_FORMAT | OPEN_FULL_TABLE |
TABLE_ROWS | OPEN_FULL_TABLE |
AVG_ROW_LENGTH | OPEN_FULL_TABLE |
DATA_LENGTH | OPEN_FULL_TABLE |
MAX_DATA_LENGTH | OPEN_FULL_TABLE |
INDEX_LENGTH | OPEN_FULL_TABLE |
DATA_FREE | OPEN_FULL_TABLE |
AUTO_INCREMENT | OPEN_FULL_TABLE |
CREATE_TIME | OPEN_FULL_TABLE |
UPDATE_TIME | OPEN_FULL_TABLE |
CHECK_TIME | OPEN_FULL_TABLE |
TABLE_COLLATION | OPEN_FRM_ONLY |
CHECKSUM | OPEN_FULL_TABLE |
CREATE_OPTIONS | OPEN_FRM_ONLY |
TABLE_COMMENT | OPEN_FRM_ONLY |