tidb如何设置支持表名大小写

🪐 TiDB 技术问题 备份&数据迁移
12

经测试，与官方文档描述一致，是不支持仅大小写不同的多个表名存在的。

  • lower_case_table_names：
    • TiDB 默认：2，且仅支持设置该值为 2。
    • MySQL 默认如下：
      • Linux 系统中该值为 0，表示表名和数据库名按照在 CREATE TABLE 或 CREATE DATABASE 语句中指定的字母大小写存储在磁盘上，且名称比较时区分大小写。
      • Windows 系统中该值为 1，表示表名按照小写字母存储在磁盘上，名称比较时不区分大小写。MySQL 在存储和查询时将所有表名转换为小写。该行为也适用于数据库名称和表的别名。
      • macOS 系统中该值为 2，表示表名和数据库名按照在 CREATE TABLE 或 CREATE DATABASE 语句中指定的字母大小写存储在磁盘上，但 MySQL 在查询时将它们转换为小写。名称比较时不区分大小写。
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>
1 个赞