TiDB 6.0 新特性解读丨 Collation 规则

TiDB [(none)] 18:45:27> select * from mysql.tidb where variable_name = 'new_collation_enabled';
+-----------------------+----------------+----------------------------------------------------+
| VARIABLE_NAME         | VARIABLE_VALUE | COMMENT                                            |
+-----------------------+----------------+----------------------------------------------------+
| new_collation_enabled | True           | If the new collations are enabled. Do not edit it. |
+-----------------------+----------------+----------------------------------------------------+
1 row in set (0.003 sec)

TiDB [(none)] 18:45:51> select version()\G
*************************** 1. row ***************************
version(): 5.7.25-TiDB-v6.0.0
1 row in set (0.001 sec)

TiDB [(none)] 18:46:00> SELECT * FROM information_schema.collations;
+--------------------+--------------------+------+------------+-------------+---------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID   | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------+--------------------+------+------------+-------------+---------+
| ascii_bin          | ascii              |   65 | Yes        | Yes         |       1 |
| binary             | binary             |   63 | Yes        | Yes         |       1 |
| gbk_bin            | gbk                |   87 |            | Yes         |       1 | (#28645)
| gbk_chinese_ci     | gbk                |   28 | Yes        | Yes         |       1 | (#28645)
| 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 | (#18678)
| utf8mb4_bin        | utf8mb4            |   46 | Yes        | Yes         |       1 |
| utf8mb4_general_ci | utf8mb4            |   45 |            | Yes         |       1 |
| utf8mb4_unicode_ci | utf8mb4            |  224 |            | Yes         |       1 | (#18678)
+--------------------+--------------------+------+------------+-------------+---------+
11 rows in set (0.001 sec)

TiDB [test] 19:05:14> SHOW CHARACTER SET;
+---------+-------------------------------------+-------------------+--------+
| Charset | Description                         | Default collation | Maxlen |
+---------+-------------------------------------+-------------------+--------+
| ascii   | US ASCII                            | ascii_bin         |      1 |
| binary  | binary                              | binary            |      1 |
| gbk     | Chinese Internal Code Specification | gbk_chinese_ci    |      2 |
| latin1  | Latin1                              | latin1_bin        |      1 |
| utf8    | UTF-8 Unicode                       | utf8_bin          |      3 |
| utf8mb4 | UTF-8 Unicode                       | utf8mb4_bin       |      4 |
+---------+-------------------------------------+-------------------+--------+
6 rows in set (0.001 sec)

TiDB-v5.4 [test] 10:17:22> select version()\G
*************************** 1. row ***************************
version(): 5.7.25-TiDB-v5.4.0
1 row in set (0.001 sec)

TiDB-v5.4 [test] 10:19:39> SELECT * FROM information_schema.collations;
+----------------+--------------------+------+------------+-------------+---------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID   | IS_DEFAULT | IS_COMPILED | SORTLEN |
+----------------+--------------------+------+------------+-------------+---------+
| utf8mb4_bin    | utf8mb4            |   46 | Yes        | Yes         |       1 |
| latin1_bin     | latin1             |   47 | Yes        | Yes         |       1 |
| binary         | binary             |   63 | Yes        | Yes         |       1 |
| ascii_bin      | ascii              |   65 | Yes        | Yes         |       1 |
| utf8_bin       | utf8               |   83 | Yes        | Yes         |       1 |
| gbk_bin        | gbk                |   87 | Yes        | Yes         |       1 |
+----------------+--------------------+------+------------+-------------+---------+
6 rows in set (0.001 sec)

service_configs:
  tidb:
    new_collations_enabled_on_first_bootstrap: true

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
c1 char(20) CHARACTER SET utf8 COLLATE utf8_bin,
c2 char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);
INSERT INTO t1 VALUES ('UUtJeaV','snRXXCZHBPW');

SET names utf8mb4 collate utf8mb4_bin;
SELECT greatest( c1, c2 ) as expr1 FROM t1;
SELECT least( c1, c2 ) as expr1 FROM t1;

SET names utf8mb4 collate utf8mb4_general_ci;
SELECT greatest( c1, c2 ) as expr1 FROM t1;
SELECT least( c1, c2 ) as expr1 FROM t1;

// The collation of JSON is always utf8mb4_bin in builtin-func which is same as MySQL
// see details https://github.com/pingcap/tidb/issues/31320#issuecomment-1010599311
if isJSON {
   dstCharset, dstCollation = charset.CharsetUTF8MB4, charset.CollationUTF8MB4
}

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (c1 json);
INSERT INTO t2 VALUES ('{\"测试\": \"你好\"}');

SELECT collation(c1), collation(upper(c1)), collation(elt(1, c1, 0x12)) FROM t2;

TiDB [test] 23:54:42> select version()\G
*************************** 1. row ***************************
version(): 5.7.25-TiDB-v6.0.0
1 row in set (0.001 sec)

TiDB [test] 00:03:51> set names utf8mb4;
Query OK, 0 rows affected (0.000 sec)

TiDB [test] 00:03:58> select charset(1);
+------------+
| charset(1) |
+------------+
| binary     |
+------------+
1 row in set (0.001 sec)

TiDB [test] 00:04:03> select charset('1');
+--------------+
| charset('1') |
+--------------+
| utf8mb4      |
+--------------+
1 row in set (0.001 sec)