MySQL - SHOW COLLATION 语句

MySQL SHOW COLLATION 语句

MySQL 校对是一组用于验证/比较字符集每个字符的规则。每个字符集至少有一个校对（它可以有多个）和一个默认校对。没有两个字符集可以具有相同的校对。

SHOW COLLATION 语句显示服务器支持的校对列表。除了可用校对的名称外，此语句还提供以下详细信息：

• Charset - 字符集名称。

• Id - 校对的 ID

• Default - 一个布尔值，表示给定的校对是否为字符集的默认值。

• Compiled - 一个布尔值，指定字符集是否已编译到服务器中。

• Sortlen - 一个整数值，指定对字符集中的字符串进行排序所需的内存量。

语法

以下是 SHOW COLLATION 语句的语法：

SHOW COLLATION
   [LIKE 'pattern' | WHERE expr]

示例

您可以使用以下查询获取当前数据库中所有可用的校对（以及上面提到的其他详细信息）：

SHOW COLLATION\G;

输出

以下是上述查询的输出：

************ 1. row ************
    Collation: armscii8_bin
      Charset: armscii8
           Id: 64
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 2. row ************
    Collation: armscii8_general_ci
      Charset: armscii8
           Id: 32
      Default: Yes
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 3. row ************
    Collation: ascii_bin
      Charset: ascii
           Id: 65
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 4. row ************
    Collation: ascii_general_ci
      Charset: ascii
           Id: 11
      Default: Yes
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 5. row ************
    Collation: big5_bin
      Charset: big5
           Id: 84
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 6. row ************
    Collation: big5_chinese_ci
      Charset: big5
           Id: 1
      Default: Yes
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 7. row ************
    Collation: binary
      Charset: binary
           Id: 63
      Default: Yes
     Compiled: Yes
      Sortlen: 1
Pad_attribute: NO PAD
************ 8. row ************
    Collation: cp1250_bin
      Charset: cp1250
           Id: 66
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 9. row ************
    Collation: cp1250_croatian_ci
      Charset: cp1250
           Id: 44
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 10. row ************
    Collation: cp1250_czech_cs
      Charset: cp1250
           Id: 34
      Default:
     Compiled: Yes
      Sortlen: 2
Pad_attribute: PAD SPACE
************ 11. row ************
    Collation: cp1250_general_ci
      Charset: cp1250
           Id: 26
      Default: Yes
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 12. row ************
    Collation: cp1250_polish_ci
      Charset: cp1250
           Id: 99
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 13. row ************
    Collation: cp1251_bin
      Charset: cp1251
           Id: 50
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 14. row ************
    Collation: cp1251_bulgarian_ci
      Charset: cp1251
           Id: 14
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 15. row ************
    Collation: cp1251_general_ci
      Charset: cp1251
           Id: 51
      Default: Yes
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 16. row ************
    Collation: cp1251_general_cs
      Charset: cp1251
           Id: 52
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 17. row ************
    Collation: cp1251_ukrainian_ci
      Charset: cp1251
           Id: 23
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 18. row ************
    Collation: cp1256_bin
      Charset: cp1256
           Id: 67
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 19. row ************
    Collation: cp1256_general_ci
      Charset: cp1256
           Id: 57
      Default: Yes
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************ 20. row ************
    Collation: cp1257_bin
      Charset: cp1257
           Id: 58
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . .

LIKE 子句

使用 LIKE 子句，您可以指定一个模式来检索与给定模式匹配的校对的名称和其他详细信息。

SHOW COLLATION LIKE 'utf32%'\G;

输出

上述查询产生以下输出：

************* 1. row *************
    Collation: utf32_bin
      Charset: utf32
           Id: 61
      Default:
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************* 2. row *************
    Collation: utf32_croatian_ci
      Charset: utf32
           Id: 181
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 3. row *************
    Collation: utf32_czech_ci
      Charset: utf32
           Id: 170
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 4. row *************
    Collation: utf32_danish_ci
      Charset: utf32
           Id: 171
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 5. row *************
    Collation: utf32_esperanto_ci
      Charset: utf32
           Id: 177
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 6. row *************
    Collation: utf32_estonian_ci
      Charset: utf32
           Id: 166
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 7. row *************
    Collation: utf32_general_ci
      Charset: utf32
           Id: 60
      Default: Yes
     Compiled: Yes
      Sortlen: 1
Pad_attribute: PAD SPACE
************* 8. row *************
    Collation: utf32_german2_ci
      Charset: utf32
           Id: 180
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 9. row *************
    Collation: utf32_hungarian_ci
      Charset: utf32
           Id: 178
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 10. row *************
    Collation: utf32_icelandic_ci
      Charset: utf32
           Id: 161
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 11. row *************
    Collation: utf32_latvian_ci
      Charset: utf32
           Id: 162
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 12. row *************
    Collation: utf32_lithuanian_ci
      Charset: utf32
           Id: 172
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 13. row *************
    Collation: utf32_persian_ci
      Charset: utf32
           Id: 176
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 14. row *************
    Collation: utf32_polish_ci
      Charset: utf32
           Id: 165
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 15. row *************
    Collation: utf32_romanian_ci
      Charset: utf32
           Id: 163
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 16. row *************
    Collation: utf32_roman_ci
      Charset: utf32
           Id: 175
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 17. row *************
    Collation: utf32_sinhala_ci
      Charset: utf32
           Id: 179
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 18. row *************
    Collation: utf32_slovak_ci
      Charset: utf32
           Id: 173
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 19. row *************
    Collation: utf32_slovenian_ci
      Charset: utf32
           Id: 164
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 20. row *************
    Collation: utf32_spanish2_ci
      Charset: utf32
           Id: 174
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 21. row *************
    Collation: utf32_spanish_ci
      Charset: utf32
           Id: 167
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 22. row *************
    Collation: utf32_swedish_ci
      Charset: utf32
           Id: 168
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 23. row *************
    Collation: utf32_turkish_ci
      Charset: utf32
           Id: 169
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 24. row *************
    Collation: utf32_unicode_520_ci
      Charset: utf32
           Id: 182
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 25. row *************
    Collation: utf32_unicode_ci
      Charset: utf32
           Id: 160
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE
************* 26. row *************
    Collation: utf32_vietnamese_ci
      Charset: utf32
           Id: 183
      Default:
     Compiled: Yes
      Sortlen: 8
Pad_attribute: PAD SPACE

WHERE 子句

您可以使用 SHOW STATUS 语句的 WHERE 子句来检索与指定条件匹配的校对的名称。

SHOW COLLATION WHERE Charset = 'cp1251';

输出

执行上述查询后，它将生成以下输出：