Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 系统表TABLES如何设置大小写不敏感
[Test Environment] Testing environment
[TiDB Version] 5.7.25-TiDB-v6.5.2
[Reproduction Path]
Executing the following query does not return any data. Besides using the UPPER() and LOWER() functions, how can I set this table to be case-insensitive?
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test'
AND TABLE_SCHEMA = 'TEST'
Check system parameters

Your SQL query won’t return any data, that’s impossible, right?
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TEST'
or
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test'
How can it be equal to both at the same time…
It works when case-insensitive. Regular tables are set to be case-insensitive when created. However, I don’t know how to set system tables to be case-insensitive.
The sorting format you need to specify for the table or column is utf8mb4_general_ci, but the default sorting format for the system table ‘tables’ is not this… Moreover, the system table does not support modifying the table structure.
That’s right, so I came to the community to seek guidance from the experts. 
Development standards require table names and database names to be all lowercase. 
A bit late, it’s been running for twenty years~
Set the sorting in the DDL, if the data volume is not large, just create a new database and import the data.
It feels like dead code. Use UPPER() and LOWER(), then concatenate with union all.
Really can’t save it anymore?
Do you mean directly modifying the system table?
utf8mb4_general_ci should be case-insensitive, while utf8mb4_bin is case-sensitive.
That is a system variable, but this system table is utf8mb4_bin.
I haven’t tried it, just followed the specifications.
Historical baggage, just make do with it.
- Setting the Character Set and Collation for Databases and Tables
TiDB supports character sets and collations, and you can choose a case-insensitive collation. Here is an example of how to set it up:
Step 1: Specify collation when creating the database
CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
utf8mb4_general_ci
is a case-insensitive collation.
Step 2: Specify collation when creating the table
If you already have a database, you need to set the collation for the table separately:
CREATE TABLE your_table_name ( your_column_name VARCHAR(255)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
You can also modify the collation of an existing table:
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
- Modifying the System Parameter
lower_case_table_names
In TiDB, you can use system variables to affect case sensitivity. The lower_case_table_names
parameter can have the following values:
0
: Case-sensitive.
1
: Case-insensitive, all table names are stored in lowercase.
2
: Case-sensitive, but table names are stored as given when created.
You can check the current configuration:
SHOW VARIABLES LIKE 'lower_case_table_names';
You can check the official parameter settings.
Good question. It’s already good enough if the legacy code can run, let alone modifying it. Let’s see if any experts have a solution.