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';

輸出

執行上述查詢後，它將生成以下輸出：