MySQL - SHOW COLLATION Statement

MySQL SHOW COLLATION Statement

MySQL collation is a set of rules to verify/compare each character of a character set. Each character set have at least one collation (it can have more) and a default collation. No two-character sets can have same collation.

The SHOW COLLATION statement displays the list of collations supported by the server. In addition to the names of the available collations this statement also provides the following details −

• Charset − The charset name.

• Id − The Id of the collation

• Default − A Boolean value representing whether the given collation is default value of the character set.

• Compiled − A Boolean value specifying whether the character set is compiled into the server.

• Sortlen − An integer value specifying the amount of memory required to sort strings in the character set.

Syntax

Following is the syntax of the SHOW COLLATION statement −

SHOW COLLATION
   [LIKE 'pattern' | WHERE expr]

Example

You can get all the collations (and other details mention above) available in the current database using the following query −

SHOW COLLATION\G;

Output

Following is the output of the above query −

************ 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
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . .

The LIKE clause

Using the LIKE clause, you can specify a pattern to retrieve names other details of the collations that match the given pattern.

SHOW COLLATION LIKE 'utf32%'\G;

Output

The above query produces the following output −

************* 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

The WHERE clause

You can use the WHERE clause of the SHOW STATUS statements to retrieve names of the collations which match the specified condition.

SHOW COLLATION WHERE Charset = 'cp1251';

Output

After executing the above query, it will generate the following output −