SHOW COLLATION

SHOW COLLATION 语句用于提供一个静态的排序规则列表，确保与 MySQL 客户端库的兼容性。

SHOW COLLATION - 图1

注意

SHOW COLLATION 所展示的排序规则列表与 TiDB 集群是否开启新排序规则框架有关，详情请见 TiDB 字符集和排序规则。

语法图

ShowCollationStmt

SHOW COLLATION - 图2

ShowLikeOrWhere

SHOW COLLATION - 图3

1. ShowCollationStmt ::=
2. "SHOW" "COLLATION" ShowLikeOrWhere?

4. ShowLikeOrWhere ::=
5. "LIKE" SimpleExpr
6. |   "WHERE" Expression

示例

若未开启新排序规则框架，仅展示二进制排序规则：

1. SHOW COLLATION;

1. +-------------+---------+------+---------+----------+---------+
2. | Collation   | Charset | Id   | Default | Compiled | Sortlen |
3. +-------------+---------+------+---------+----------+---------+
4. | utf8mb4_bin | utf8mb4 |   46 | Yes     | Yes      |       1 |
5. | latin1_bin  | latin1  |   47 | Yes     | Yes      |       1 |
6. | binary      | binary  |   63 | Yes     | Yes      |       1 |
7. | ascii_bin   | ascii   |   65 | Yes     | Yes      |       1 |
8. | utf8_bin    | utf8    |   83 | Yes     | Yes      |       1 |
9. +-------------+---------+------+---------+----------+---------+
10. 5 rows in set (0.02 sec)

若开启了新排序规则框架，则在二进制排序规则之外，额外支持 utf8_general_ci 和 utf8mb4_general_ci 两种大小写和口音不敏感的排序规则：

1. SHOW COLLATION;

1. +--------------------+---------+------+---------+----------+---------+
2. | Collation          | Charset | Id   | Default | Compiled | Sortlen |
3. +--------------------+---------+------+---------+----------+---------+
4. | ascii_bin          | ascii   |   65 | Yes     | Yes      |       1 |
5. | binary             | binary  |   63 | Yes     | Yes      |       1 |
6. | gbk_bin            | gbk     |   87 |         | Yes      |       1 |
7. | gbk_chinese_ci     | gbk     |   28 | Yes     | Yes      |       1 |
8. | latin1_bin         | latin1  |   47 | Yes     | Yes      |       1 |
9. | utf8_bin           | utf8    |   83 | Yes     | Yes      |       1 |
10. | utf8_general_ci    | utf8    |   33 |         | Yes      |       1 |
11. | utf8_unicode_ci    | utf8    |  192 |         | Yes      |       1 |
12. | utf8mb4_bin        | utf8mb4 |   46 | Yes     | Yes      |       1 |
13. | utf8mb4_general_ci | utf8mb4 |   45 |         | Yes      |       1 |
14. | utf8mb4_unicode_ci | utf8mb4 |  224 |         | Yes      |       1 |
15. +--------------------+---------+------+---------+----------+---------+
16. 11 rows in set (0.001 sec)

要过滤字符集，可以添加 WHERE 子句。

1. SHOW COLLATION WHERE Charset="utf8mb4";

1. +--------------------+---------+-----+---------+----------+---------+
2. | Collation          | Charset | Id  | Default | Compiled | Sortlen |
3. +--------------------+---------+-----+---------+----------+---------+
4. | utf8mb4_0900_ai_ci | utf8mb4 | 255 |         | Yes      |       1 |
5. | utf8mb4_0900_bin   | utf8mb4 | 309 |         | Yes      |       1 |
6. | utf8mb4_bin        | utf8mb4 |  46 | Yes     | Yes      |       1 |
7. | utf8mb4_general_ci | utf8mb4 |  45 |         | Yes      |       1 |
8. | utf8mb4_unicode_ci | utf8mb4 | 224 |         | Yes      |       1 |
9. +--------------------+---------+-----+---------+----------+---------+
10. 5 rows in set (0.00 sec)

MySQL 兼容性

SHOW COLLATION 语句功能与 MySQL 完全兼容。注意，TiDB 中字符集的默认排序规则与 MySQL 有所不同，具体可参考与 MySQL 兼容性对比。如发现任何其他兼容性差异，请尝试 TiDB 支持资源。

另请参阅