|
I start mysqld on Linux with --lower-case-table-names=0, which is the default on Linux, and run this script:
CREATE OR REPLACE TABLE t1 (a INT);
|
SELECT count(*) FROM information_schema.tables WHERE table_name='t1';
|
SELECT count(*) FROM information_schema.tables WHERE table_name='T1';
|
SELECT count(*) FROM information_schema.tables WHERE table_name LIKE 't1';
|
SELECT count(*) FROM information_schema.tables WHERE table_name LIKE 'T1';
|
SELECT COLLATION(table_name) FROM information_schema.tables LIMIT 1;
|
+----------+
|
| count(*) |
|
+----------+
|
| 1 |
|
+----------+
|
+----------+
|
| count(*) |
|
+----------+
|
| 0 |
|
+----------+
|
+----------+
|
| count(*) |
|
+----------+
|
| 2 |
|
+----------+
|
+----------+
|
| count(*) |
|
+----------+
|
| 2 |
|
+----------+
|
+-----------------------+
|
| COLLATION(table_name) |
|
+-----------------------+
|
| utf8_general_ci |
|
+-----------------------+
|
The above results look inconsistent:
- The collation of the column table_name is reported as case insensitive
- However the comparison operator (=) is actually done case sensitively
- LIKE is indeed done case insensitively, but this is wrong: tables T1 and t1 must be uniqie
This inconsistency can be a reason of troubles when one wants to make a list of unique table names, e.g. in the following scenario:
CREATE OR REPLACE TABLE t1 (a INT);
|
CREATE OR REPLACE TABLE T1 (a INT);
|
CREATE OR REPLACE TABLE t2 AS
|
SELECT DISTINCT table_name
|
FROM information_schema.tables
|
WHERE table_schema='test'
|
AND table_name LIKE '%1';
|
SELECT * FROM t2;
|
+------------+
|
| table_name |
|
+------------+
|
| t1 |
|
+------------+
|
DISTINCT changes the comparison style to be case sensitive, even for the operator '='.
The above result is wrong. Both tables 't1' and 'T1' should be in the result. The are unique table names!
Note, if I now remove the DISTINCT keyword, it returns a good result:
CREATE OR REPLACE TABLE t1 (a INT);
|
CREATE OR REPLACE TABLE T1 (a INT);
|
CREATE OR REPLACE TABLE t2 AS
|
SELECT table_name
|
FROM information_schema.tables
|
WHERE table_schema='test'
|
AND table_name LIKE '%1';
|
SELECT * FROM t2;
|
+------------+
|
| table_name |
|
+------------+
|
| t1 |
|
| T1 |
|
+------------+
|
Conclusion:
The choice of utf8_general_ci for INFORMATION_SCHEMA.TABLES.TABLE_NAME does not look good..
It does not reflect table name uniqueness.
|