How to Set Case Insensitivity for System Table TABLES

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 系统表TABLES如何设置大小写不敏感

| username: Kongdom

[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

image

| username: tidb菜鸟一只 | Original post link

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…

| username: Kongdom | Original post link

:yum: 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.

| username: Kongdom | Original post link

Seeing is believing~

| username: tidb菜鸟一只 | Original post link

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.

| username: Kongdom | Original post link

That’s right, so I came to the community to seek guidance from the experts. :yum:

| username: DBAER | Original post link

Development standards require table names and database names to be all lowercase. :slight_smile:

| username: Kongdom | Original post link

:joy: A bit late, it’s been running for twenty years~

| username: 健康的腰间盘 | Original post link

Set the sorting in the DDL, if the data volume is not large, just create a new database and import the data.

| username: DBAER | Original post link

It feels like dead code. Use UPPER() and LOWER(), then concatenate with union all.

| username: Kongdom | Original post link

:joy: Really can’t save it anymore?

| username: Kongdom | Original post link

:thinking: Do you mean directly modifying the system table?

| username: zhanggame1 | Original post link

utf8mb4_general_ci should be case-insensitive, while utf8mb4_bin is case-sensitive.

| username: Kongdom | Original post link

That is a system variable, but this system table is utf8mb4_bin.

| username: 友利奈绪 | Original post link

I haven’t tried it, just followed the specifications.

| username: yiduoyunQ | Original post link

Historical baggage, just make do with it.

| username: zhh_912 | Original post link

  1. 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;
  1. 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';
| username: yytest | Original post link

You can check the official parameter settings.

| username: TiDBer_H5NdJb5Q | Original post link

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.

| username: zhang_2023 | Original post link

Cannot be changed.