COLLATIONS

• 该表提供查询MySQL Server支持的可用校对规则有哪些

• 该表是Memory引擎临时表

• 表定义语句

CREATE TEMPORARY TABLE `COLLATIONS` (
  `COLLATION_NAME` varchar(32) NOT NULL DEFAULT '',
  `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '',
  `ID` bigint(11) NOT NULL DEFAULT '0',
  `IS_DEFAULT` varchar(3) NOT NULL DEFAULT '',
  `IS_COMPILED` varchar(3) NOT NULL DEFAULT '',
  `SORTLEN` bigint(3) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8

• 字段含义如下

• COLLATION_NAME：校对规则名称
• CHARACTER_SET_NAME：校对规则对应的字符集名称，该字段为 "MySQL extension" 列
• ID：校对规则的ID号，该字段为 "MySQL extension" 列
• IS_DEFAULT：是否是字符集的默认校对规则，该字段为 "MySQL extension" 列
• IS_COMPILED：校对规则是否被编译进Server中，如果不为Yes，则表示并没有被编译到Server中，校对规则处于不可用状态，该字段为 "MySQL extension" 列
• SORTLEN：最大排序字节长度，与字符集对应的字符串在排序时所占用的内存大小有关，该字段为 "MySQL extension" 列

• 表记录完整字段内容示例（MySQL 5.6版本共219种校对规则，MySQL 5.7版本共222种，MySQL 8.0共270种）

admin@localhost : information_schema 03:31:58> select * from COLLATIONS limit 20;
+-------------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME    | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-------------------+--------------------+----+------------+-------------+---------+
| big5_chinese_ci  | big5              |  1 | Yes        | Yes        |      1 |
| big5_bin          | big5              | 84 |            | Yes        |      1 |
............
| koi8r_bin        | koi8r              | 74 |            | Yes        |      1 |
+-------------------+--------------------+----+------------+-------------+---------+
20 rows in set (0.01 sec)

• 对于校对规则信息的方式有两种

# select语句方式
SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
  [WHERE COLLATION_NAME LIKE 'wild']

## 例如
root@localhost : information_schema 12:25:04> SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS where COLLATION_NAME='utf8_general_ci';
+-----------------+
| COLLATION_NAME  |
+-----------------+
| utf8_general_ci |
+-----------------+
1 row in set (0.00 sec)

# show语句方式
SHOW COLLATION
  [LIKE 'wild']

## 例如
root@localhost : information_schema 12:26:23> show collation where COLLATION='utf8_general_ci';
+-----------------+---------+----+---------+----------+---------+
| Collation      | Charset | Id | Default | Compiled | Sortlen |
+-----------------+---------+----+---------+----------+---------+
| utf8_general_ci | utf8    | 33 | Yes    | Yes      |      1 |
+-----------------+---------+----+---------+----------+---------+
1 row in set (0.00 sec)

---

上一篇：CHARACTER_SETS表 |下一篇：COLLATION_CHARACTER_SET_APPLICABILITY表