Description:
In this document https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html
at point 12 regarding "lower_case_table_names" it is suggested to identify tables with uppercase with this statement:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != LOWER(TABLE_NAME) AND TABLE_TYPE = 'BASE TABLE';
However; this will not return any rows because the comparison isn't case sensitive.
How to repeat:
Createing a database with tables having both uppercase and lower case.
mysql> create database test;
Query OK, 1 row affected (0.06 sec)
mysql> use test;
Database changed
mysql> create table UPPERCASE (id int, PRIMARY KEY (`id`)) engine=InnoDB;
Query OK, 0 rows affected (0.21 sec)
mysql> create table lowercase (id int, PRIMARY KEY (`id`)) engine=InnoDB;
Query OK, 0 rows affected (0.19 sec)
mysql> create table CamelCase (id int, PRIMARY KEY (`id`)) engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != LOWER(TABLE_NAME) AND TABLE_TYPE = 'BASE TABLE';
Empty set (0.01 sec)
To illustrate:
mysql> select TABLE_NAME, if(TABLE_NAME != lower(TABLE_NAME),'Yes','No') as UpperCase from information_schema.tables where TABLE_SCHEMA = 'test';
+------------+-----------+
| TABLE_NAME | UpperCase |
+------------+-----------+
| CamelCase | No |
| UPPERCASE | No |
| lowercase | No |
+------------+-----------+
3 rows in set (0.01 sec)
Suggested fix:
A very simple way is to compare a checksum of the values.
mysql> select TABLE_NAME, if(sha(TABLE_NAME) != sha(lower(TABLE_NAME)),'Yes','No') as UpperCase from information_schema.tables where TABLE_SCHEMA = 'test';
+------------+-----------+
| TABLE_NAME | UpperCase |
+------------+-----------+
| CamelCase | Yes |
| UPPERCASE | Yes |
| lowercase | No |
+------------+-----------+
3 rows in set (0.00 sec)
The SQL recommended for identifing tables with uppercase should be
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE sha(TABLE_NAME) != sha(LOWER(TABLE_NAME)) AND TABLE_TYPE = 'BASE TABLE';
