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?
  3. ShowLikeOrWhere ::=
  4. "LIKE" SimpleExpr
  5. | "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_ciutf8mb4_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 支持资源

另请参阅