SHOW COLLATION

This statement provides a static list of collations, and is included to provide compatibility with MySQL client libraries.

SHOW COLLATION - 图1

Note

Results of SHOW COLLATION vary when the “new collation framework” is enabled. For new collation framework details, refer to Character Set and Collation.

Synopsis

ShowCollationStmt

SHOW COLLATION - 图2

ShowLikeOrWhere

SHOW COLLATION - 图3

  1. ShowCollationStmt ::=
  2.     "SHOW" "COLLATION" ShowLikeOrWhere?
  4. ShowLikeOrWhere ::=
  5.     "LIKE" SimpleExpr
  6. |   "WHERE" Expression

Examples

When new collation framework is disabled, only binary collations are displayed.

  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)

When new collation framework is enabled, utf8_general_ci and utf8mb4_general_ci are additionally supported.

  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)

To filter on the character set, you can add a WHERE clause.

  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 compatibility

The usage of the SHOW COLLATION statement in TiDB is fully compatible with MySQL. However, charsets in TiDB might have different default collations compared with MySQL. For details, refer to Compatibility with MySQL. If you find any compatibility differences, report a bug.

See also