3. COLLATIONS

3. COLLATIONS

表COLLATIONS提供有关每个字符集排序规则的信息。下表中SHOW Name对应SHOW COLLATION。

INFORMATION_SCHEMA Name	SHOW Name	Remarks
COLLATION_NAME	Collation
CHARACTER_SET_NAME	Charset	MySQL extension
ID	Id	MySQL extension
IS_DEFAULT	Default	MySQL extension
IS_COMPILED	Compiled	MySQL extension
SORTLEN	Sortlen	MySQL extension

表COLLATIONS有以下列：

• COLLATION_NAME：排序规则名称
• CHARACTER_SET_NAME：与排序规则关联的字符集的名称。
• ID：排序规则ID
• IS_DEFAULT：排序规则是否为其字符集的默认值
• IS_COMPILED：字符集是否编译到服务器中。
• SORTLEN：这与对字符集中表示的字符串进行排序所需的内存量有关。

示例

mysql> SELECT * FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME LIKE 'utf8mb4%';
+------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME         | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+------------------------+--------------------+-----+------------+-------------+---------+
| utf8mb4_general_ci     | utf8mb4            |  45 | Yes        | Yes         |       1 |
| utf8mb4_bin            | utf8mb4            |  46 |            | Yes         |       1 |
| utf8mb4_unicode_ci     | utf8mb4            | 224 |            | Yes         |       8 |
| utf8mb4_icelandic_ci   | utf8mb4            | 225 |            | Yes         |       8 |
| utf8mb4_latvian_ci     | utf8mb4            | 226 |            | Yes         |       8 |
| utf8mb4_romanian_ci    | utf8mb4            | 227 |            | Yes         |       8 |
| utf8mb4_slovenian_ci   | utf8mb4            | 228 |            | Yes         |       8 |
| utf8mb4_polish_ci      | utf8mb4            | 229 |            | Yes         |       8 |
| utf8mb4_estonian_ci    | utf8mb4            | 230 |            | Yes         |       8 |
| utf8mb4_spanish_ci     | utf8mb4            | 231 |            | Yes         |       8 |
| utf8mb4_swedish_ci     | utf8mb4            | 232 |            | Yes         |       8 |
| utf8mb4_turkish_ci     | utf8mb4            | 233 |            | Yes         |       8 |
| utf8mb4_czech_ci       | utf8mb4            | 234 |            | Yes         |       8 |
| utf8mb4_danish_ci      | utf8mb4            | 235 |            | Yes         |       8 |
| utf8mb4_lithuanian_ci  | utf8mb4            | 236 |            | Yes         |       8 |
| utf8mb4_slovak_ci      | utf8mb4            | 237 |            | Yes         |       8 |
| utf8mb4_spanish2_ci    | utf8mb4            | 238 |            | Yes         |       8 |
| utf8mb4_roman_ci       | utf8mb4            | 239 |            | Yes         |       8 |
| utf8mb4_persian_ci     | utf8mb4            | 240 |            | Yes         |       8 |
| utf8mb4_esperanto_ci   | utf8mb4            | 241 |            | Yes         |       8 |
| utf8mb4_hungarian_ci   | utf8mb4            | 242 |            | Yes         |       8 |
| utf8mb4_sinhala_ci     | utf8mb4            | 243 |            | Yes         |       8 |
| utf8mb4_german2_ci     | utf8mb4            | 244 |            | Yes         |       8 |
| utf8mb4_croatian_ci    | utf8mb4            | 245 |            | Yes         |       8 |
| utf8mb4_unicode_520_ci | utf8mb4            | 246 |            | Yes         |       8 |
| utf8mb4_vietnamese_ci  | utf8mb4            | 247 |            | Yes         |       8 |
+------------------------+--------------------+-----+------------+-------------+---------+
26 rows in set (0.00 sec)


mysql> show collation like 'utf8mb4%';
+------------------------+---------+-----+---------+----------+---------+
| Collation              | Charset | Id  | Default | Compiled | Sortlen |
+------------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci     | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin            | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci     | utf8mb4 | 224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci   | utf8mb4 | 225 |         | Yes      |       8 |
| utf8mb4_latvian_ci     | utf8mb4 | 226 |         | Yes      |       8 |
| utf8mb4_romanian_ci    | utf8mb4 | 227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci   | utf8mb4 | 228 |         | Yes      |       8 |
| utf8mb4_polish_ci      | utf8mb4 | 229 |         | Yes      |       8 |
| utf8mb4_estonian_ci    | utf8mb4 | 230 |         | Yes      |       8 |
| utf8mb4_spanish_ci     | utf8mb4 | 231 |         | Yes      |       8 |
| utf8mb4_swedish_ci     | utf8mb4 | 232 |         | Yes      |       8 |
| utf8mb4_turkish_ci     | utf8mb4 | 233 |         | Yes      |       8 |
| utf8mb4_czech_ci       | utf8mb4 | 234 |         | Yes      |       8 |
| utf8mb4_danish_ci      | utf8mb4 | 235 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci  | utf8mb4 | 236 |         | Yes      |       8 |
| utf8mb4_slovak_ci      | utf8mb4 | 237 |         | Yes      |       8 |
| utf8mb4_spanish2_ci    | utf8mb4 | 238 |         | Yes      |       8 |
| utf8mb4_roman_ci       | utf8mb4 | 239 |         | Yes      |       8 |
| utf8mb4_persian_ci     | utf8mb4 | 240 |         | Yes      |       8 |
| utf8mb4_esperanto_ci   | utf8mb4 | 241 |         | Yes      |       8 |
| utf8mb4_hungarian_ci   | utf8mb4 | 242 |         | Yes      |       8 |
| utf8mb4_sinhala_ci     | utf8mb4 | 243 |         | Yes      |       8 |
| utf8mb4_german2_ci     | utf8mb4 | 244 |         | Yes      |       8 |
| utf8mb4_croatian_ci    | utf8mb4 | 245 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci  | utf8mb4 | 247 |         | Yes      |       8 |
+------------------------+---------+-----+---------+----------+---------+
26 rows in set (0.00 sec)