# [FAQ] TiDB 设置 lower_case_table_names 后行为与 MySQL 不一致

## Key Words

lower_case_table_names, incompatible

## Description

TiDB 默认 lower_case_table_names 值为 2, TiDB 对 information_schema.tables 数据时无法忽略大小写查询 ，但 MySQL 可以同其[官网描述](https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html)一样忽略大小写。如下为 TiDB 及 MySQL 的不同测试结果。  

1. MySQL 测试结果(预期)

    ```sql
    mysql> select version();                                                                                +-----------+
    | version() |
    +-----------+
    | 8.0.31    |
    +-----------+
    1 row in set (0.00 sec)

    mysql> show variables like '%lower_case_table_names%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | lower_case_table_names | 2     |
    +------------------------+-------+
    1 row in set (0.03 sec)

    mysql> select table_name from information_schema.tables where table_name = 'TEST';
    +------------+
    | TABLE_NAME |
    +------------+
    | TEST       |
    +------------+
    1 row in set (0.01 sec)

    mysql> select table_name from information_schema.tables where table_name = 'test';
    +------------+
    | TABLE_NAME |
    +------------+
    | TEST       |
    +------------+
    1 row in set (0.01 sec)
    ```

2. TiDB 测试结果(非预期)

    ```sql
    mysql> select version();
    +--------------------+
    | version()          |
    +--------------------+
    | 5.7.25-TiDB-v6.5.0 |
    +--------------------+
    1 row in set (0.01 sec)

    mysql> show variables like '%lower_case_table_names%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | lower_case_table_names | 2     |
    +------------------------+-------+
    1 row in set (0.01 sec)

    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 TEST(id int,name varchar(20));
    Query OK, 0 rows affected (0.12 sec)

    mysql> select table_name from information_schema.tables where table_name = 'test';
    Empty set (0.03 sec)

    mysql> select table_name from information_schema.tables where table_name = 'TEST';
    +------------+
    | table_name |
    +------------+
    | TEST       |
    +------------+
    1 row in set (0.03 sec)
    ```

## Content

1. 该问题属于 TiDB 兼容性 BUG，已在高版本修复 `show table status` 的兼容性，但直接查询 `information_schema` 内部表的兼容性问题还未修复。相关 issue : <https://github.com/pingcap/tidb/issues/7518>，直接查询系统表 BUG 对应 issue : <https://github.com/pingcap/tidb/issues/40424>
2. 
3. 绕过方式一：可选择不存在 `show table status like '{TABLE_NAME}'` 兼容性问题的版本，并执行该语句绕过；  

    ```sql
    mysql> show table status like 'test';
    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
    | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation   | Checksum | Create_options | Comment |
    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
    | TEST | InnoDB |      10 | Compact    |    0 |              0 |           0 |               0 |            0 |         0 |           NULL | 2023-01-13 04:24:50 | NULL        | NULL       | utf8mb4_bin |          |                |         |
    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
    1 row in set (0.05 sec)

    mysql> show table status like 'TEST';
    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
    | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation   | Checksum | Create_options | Comment |
    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
    | TEST | InnoDB |      10 | Compact    |    0 |              0 |           0 |               0 |            0 |         0 |           NULL | 2023-01-13 04:24:50 | NULL        | NULL       | utf8mb4_bin |          |                |         |
    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
    1 row in set (0.02 sec)

    mysql> select version();
    +--------------------+
    | version()          |
    +--------------------+
    | 5.7.25-TiDB-v6.5.0 |
    +--------------------+
    1 row in set (0.01 sec)
    ```

4. 绕过方式二：可手动在应用端使用 [`LOWER()`](https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_lower), [`UPPER`](https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_upper) 2 个函数转换的方式绕过。

## Affected Versions

1. 执行 `show table status` 不兼容 v6.1.0 ~ v6.1.3
2. 直接查询系统表不兼容 v6.1.0 ~ v6.1.3, v6.2.0, v6.3.0, v6.4.0, v6.5.0