TiDB support the CI_AS collation

[zhenjiaogao]

Feature Request

Is your feature request related to a problem? Please describe:

In TiDB, the parameter new_collat​​​​ions_enabled_on_first_bootstrap is set to true, but the CI_AS collation is not supported:

mysql> show collation;
+--------------------+---------+------+---------+----------+---------+
| Collation          | Charset | Id   | Default | Compiled | Sortlen |
+--------------------+---------+------+---------+----------+---------+
| ascii_bin          | ascii   |   65 | Yes     | Yes      |       1 |
| binary             | binary  |   63 | Yes     | Yes      |       1 |
| latin1_bin         | latin1  |   47 | Yes     | Yes      |       1 |
| utf8_bin           | utf8    |   83 | Yes     | Yes      |       1 |
| utf8_general_ci    | utf8    |   33 |         | Yes      |       1 |
| utf8_unicode_ci    | utf8    |  192 |         | Yes      |       1 |
| utf8mb4_bin        | utf8mb4 |   46 | Yes     | Yes      |       1 |
| utf8mb4_general_ci | utf8mb4 |   45 |         | Yes      |       1 |
| utf8mb4_unicode_ci | utf8mb4 |  224 |         | Yes      |       1 |
+--------------------+---------+------+---------+----------+---------+
9 rows in set (0.00 sec)

mysql> SELECT VARIABLE_VALUE FROM mysql.tidb WHERE VARIABLE_NAME='new_collation_enabled';
+----------------+
| VARIABLE_VALUE |
+----------------+
| True           |
+----------------+
1 row in set (0.00 sec)

mysql> create database gzj_test;
Query OK, 0 rows affected (0.08 sec)

mysql> use gzj_test;
Database changed

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v5.2.1 |
+--------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t (id int not null, a CHAR(50) COLLATE utf8mb4_general_ci);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t value(1, 'aaaa'), (3, 'āáǎà');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select distinct a from t;
+------+
| a    |
+------+
| aaaa |
+------+
1 row in set (0.00 sec)

mysql> select * from t where a='aaaa   ';
+----+----------+
| id | a        |
+----+----------+
|  1 | aaaa     |
|  3 | āáǎà     |
+----+----------+
2 rows in set (0.01 sec)

In MySQL 8.0.26, using utf8mb4_0900_as_ci collation:

mysql> CREATE TABLE t (id int not null, a CHAR(50) COLLATE utf8mb4_0900_as_ci);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t value(1, 'aaaa'), (3, 'āáǎà');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select distinct a from t;
+----------+
| a        |
+----------+
| aaaa     |
| āáǎà     |
+----------+
2 rows in set (0.00 sec)

mysql> select * from t where a='aaaa   ';
Empty set (0.02 sec)

Describe the feature you'd like:

TiDB support the CI_AS collation similar to utf8mb4_0900_as_ci in MySQL and Pad_attribute is NO PAD：

mysql> SHOW COLLATION WHERE Charset = 'utf8mb4';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
......................
......................

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[dveeden]

Closing this as this is being tracked in #37566