How to set TiDB content queries to be case-insensitive?

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

Original topic: tibd内容查询区分大小写,怎么设置为不区分大小写?

| username: Running

  1. Table structure:
  2. Data
  3. Query based on conditions
| username: Ming | Original post link

Check if new_collations_enabled_on_first_bootstrap is set to true.

| username: wuxiangdong | Original post link

Sorted set, change it, utf8mb4_bin is case-sensitive, those ending with ci are case-insensitive, and those ending with cs are case-sensitive.

| username: 天蓝色的小九 | Original post link

You can take a look at this, hope it helps:

| username: xfworld | Original post link

It may be necessary to rebuild the cluster… :cowboy_hat_face: Refer to the answers from the friends above.

| username: OnTheRoad | Original post link

  1. Rebuild the cluster and enable the new collation framework in the cluster topology file (TiDB configuration parameter new_collations_enabled_on_first_bootstrap=true). Modify the default collation.
  2. Without rebuilding the cluster, apply the lower(column) or upper(column) function to the field in the query.
| username: weixiaobing | Original post link

You can execute the collation modification:

ALTER TABLE dbawork.test MODIFY name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
| username: Lucien-卢西恩 | Original post link

You can take a look at this, hope it helps:

@Running It is recommended to refer to this document. In the current state, it should not be possible to distinguish between uppercase and lowercase. You can only rebuild the cluster and then import the data.

| username: alfred | Original post link

Learned :+1: