Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: tidb如何设置支持表名大小写
|
username: 宸凡_22
How to configure TiDB to support case-sensitive table names, for example: table names abc and ABC as two different tables.
TiDB supports case-sensitive table names, but compares table names in lowercase.
By default, TiDB is case-insensitive to schema names, i.e., lower_case_table_names:2. However, most upstream MySQL instances run on Linux systems, which are case-sensitive by default. In this case, you need to set case-sensitive to true in the DM data synchronization task settings to ensure the correct synchronization of the upstream schema.
How exactly should the DM synchronization task be set?
I couldn’t find information on how to support case sensitivity for table names.
Not found. It says above that case sensitivity is related to data migration.
Note the question, the original poster is talking about DM data synchronization.
According to the test, it is consistent with the official documentation description that multiple table names differing only in case are not supported.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: lower_case_table_names:
2, and only supports setting this value to 2.0, meaning table names and database names are stored on disk as specified in the CREATE TABLE or CREATE DATABASE statements, and name comparisons are case-sensitive.1, meaning table names are stored on disk in lowercase, and name comparisons are case-insensitive. MySQL converts all table names to lowercase for storage and querying. This behavior also applies to database names and table aliases.2, meaning table names and database names are stored on disk as specified in the CREATE TABLE or CREATE DATABASE statements, but MySQL converts them to lowercase for querying. Name comparisons are case-insensitive.mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table abc (tid int);
Query OK, 0 rows affected (0.19 sec)
mysql> create table ABC (tid int);
ERROR 1050 (42S01): Table 'test.ABC' already exists
mysql> show variables like '%case%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| lower_case_file_system | 1 |
| lower_case_table_names | 2 |
| validate_password.mixed_case_count | 1 |
+------------------------------------+-------+
3 rows in set (0.00 sec)
mysql> select version();
+--------------------+
| version() |
+--------------------+
| 8.0.11-TiDB-v7.5.0 |
+--------------------+
1 row in set (0.00 sec)
mysql> select * from information_schema.tables where table_name='ABC';
Empty set (0.02 sec)
mysql> select * from information_schema.tables where table_name='abc';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | TIDB_TABLE_ID | TIDB_ROW_ID_SHARDING_INFO | TIDB_PK_TYPE | TIDB_PLACEMENT_POLICY_NAME |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| def | test | abc | BASE TABLE | InnoDB | 10 | Compact | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2024-01-19 14:07:25 | NULL | NULL | utf8mb4_bin | NULL | | | 144 | NOT_SHARDED | NONCLUSTERED | NULL |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
1 row in set (0.01 sec)
mysql> drop table abc;
Query OK, 0 rows affected (0.35 sec)
mysql> create table ABC (tid int);
Query OK, 0 rows affected (0.15 sec)
mysql> select * from information_schema.tables where table_name='ABC';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | TIDB_TABLE_ID | TIDB_ROW_ID_SHARDING_INFO | TIDB_PK_TYPE | TIDB_PLACEMENT_POLICY_NAME |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| def | test | ABC | BASE TABLE | InnoDB | 10 | Compact | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2024-01-19 14:15:27 | NULL | NULL | utf8mb4_bin | NULL | | | 147 | NOT_SHARDED | NONCLUSTERED | NULL |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
1 row in set (0.02 sec)
mysql> select * from information_schema.tables where table_name='abc';
Empty set (0.01 sec)
mysql>
This approach might not be very good. The access methods for Linux are relatively simple, and case sensitivity is not a big issue. Database systems need to interact with business systems, often involving middleware, and sometimes there are backup and data migration operations. Making case sensitivity a factor does more harm than good.
Case sensitivity can cause errors even with a single letter mistake. If the case is accidentally reversed, it can lead to more issues. Some domestic databases like MySQL and certain PostgreSQL-based ones specifically emphasize case insensitivity.
I’m a bit curious, what is the significance of distinguishing between uppercase and lowercase table names? Or in what scenarios would you need to distinguish between uppercase and lowercase table names?
It would be better not to differentiate; it can easily cause confusion whether in application or database maintenance.
case-sensitive: false # schema/table case sensitivity
However, it seems better not to make it case-sensitive. After all, it also needs to cooperate with the application, which may not support it. What if it is deployed on a Windows server?
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: