MySQL is case-insensitive, but TiDB is case-sensitive

Translated
1

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

Original topic: mysql是不区分大小写的，但TiDB是区分大小写的

| username: jerry

[TiDB Usage Environment] Production
[TiDB Version] 5.0
[Encountered Problem] MySQL is case-insensitive, but TiDB is case-sensitive.
[Reproduction] When using DM to extract data to TiDB, some data cannot be queried because TiDB is case-sensitive.

2
| username: db_user | Original post link

Case sensitivity is determined by collation.
Use show create table to check if the collation of your table is consistent. If it’s inconsistent, you need to specify it.

  1. First, check if new_collations_enabled_on_first_bootstrap in TiDB is set to true.
  2. If 1 is true, then check if the table structures in the upstream and downstream are consistent, i.e., if the collations are consistent. If they are inconsistent, this issue will occur.
  3. If they are inconsistent, you need to check the synchronization method. If the upstream table structure or the table creation statement only specifies the character set, such as utf8mb4, then the corresponding default collation in MySQL 5.7 is _general_ci, while in TiDB it is _bin. This is why one is case-sensitive and the other is not.
3
| username: jerry | Original post link

To answer your first question, new_collations_enabled_on_first_bootstrap=false. I see that the official website says changing this value to true requires creating a new cluster, which is too costly. Is there any other way, or a way to make it effective locally?

4
| username: db_user | Original post link

There is no way to make it effective locally. If the query can be modified, there is a way to solve it; otherwise, it really can’t be resolved.

5
| username: jerry | Original post link

What do you mean by “query allows modification”?

6
| username: db_user | Original post link

For example, writing select upper("abc")="ABC" will not use the index. Actually, the best way is to enable a new collation, which indeed requires redeployment. This needs to be weighed.

7
| username: Raymond | Original post link

It is recommended to plan the database well in the future and set the MySQL collation to utf8_bin or utf8mb4_bin to avoid these issues. Keeping the database collation consistent will make it easier to migrate the database in the future.

8
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.