MySQL and Character Sets - Page 2

Thirty-six character sets may seem a lot, but there are even more collations (as each character set can have one or more collations). The SHOW COLLATION statement will allow us to take a look at the available collations - there're quite a few of them!

mysql> SHOW COLLATION\G
*************************** 1. row ***************************
Collation: big5_chinese_ci
  Charset: big5
       Id: 1
  Default: Yes
 Compiled: Yes
  Sortlen: 1
*************************** 2. row ***************************
Collation: big5_bin
  Charset: big5
       Id: 84
  Default:
 Compiled: Yes
  Sortlen: 1
*************************** 3. row ***************************
Collation: dec8_swedish_ci
  Charset: dec8
       Id: 3
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 4. row ***************************
Collation: dec8_bin
  Charset: dec8
       Id: 69
  Default:
 Compiled:
  Sortlen: 0
*************************** 5. row ***************************
Collation: cp850_general_ci
  Charset: cp850
       Id: 4
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 6. row ***************************
Collation: cp850_bin
  Charset: cp850
       Id: 80
  Default:
 Compiled:
  Sortlen: 0
*************************** 7. row ***************************
Collation: hp8_english_ci
  Charset: hp8
       Id: 6
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 8. row ***************************
Collation: hp8_bin
  Charset: hp8
       Id: 72
  Default:
 Compiled:
  Sortlen: 0
*************************** 9. row ***************************
Collation: koi8r_general_ci
  Charset: koi8r
       Id: 7
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 10. row ***************************
Collation: koi8r_bin
  Charset: koi8r
       Id: 74
  Default:
 Compiled:
  Sortlen: 0
*************************** 11. row ***************************
Collation: latin1_german1_ci
  Charset: latin1
       Id: 5
  Default:
 Compiled:
  Sortlen: 0
*************************** 12. row ***************************
Collation: latin1_swedish_ci
  Charset: latin1
       Id: 8
  Default: Yes
 Compiled: Yes
  Sortlen: 1
*************************** 13. row ***************************
Collation: latin1_danish_ci
  Charset: latin1
       Id: 15
  Default:
 Compiled:
  Sortlen: 0
*************************** 14. row ***************************
Collation: latin1_german2_ci
  Charset: latin1
       Id: 31
  Default:
 Compiled: Yes
  Sortlen: 2
*************************** 15. row ***************************
Collation: latin1_bin
  Charset: latin1
       Id: 47
  Default:
 Compiled: Yes
  Sortlen: 1
*************************** 16. row ***************************
Collation: latin1_general_ci
  Charset: latin1
       Id: 48
  Default:
 Compiled:
  Sortlen: 0
*************************** 17. row ***************************
Collation: latin1_general_cs
  Charset: latin1
       Id: 49
  Default:
 Compiled:
  Sortlen: 0
*************************** 18. row ***************************
Collation: latin1_spanish_ci
  Charset: latin1
       Id: 94
  Default:
 Compiled:
  Sortlen: 0
*************************** 19. row ***************************
Collation: latin2_czech_cs
  Charset: latin2
       Id: 2
  Default:
 Compiled: Yes
  Sortlen: 4
*************************** 20. row ***************************
Collation: latin2_general_ci
  Charset: latin2
       Id: 9
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 21. row ***************************
Collation: latin2_hungarian_ci
  Charset: latin2
       Id: 21
  Default:
 Compiled:
  Sortlen: 0
*************************** 22. row ***************************
Collation: latin2_croatian_ci
  Charset: latin2
       Id: 27
  Default:
 Compiled:
  Sortlen: 0
*************************** 23. row ***************************
Collation: latin2_bin
  Charset: latin2
       Id: 77
  Default:
 Compiled:
  Sortlen: 0
*************************** 24. row ***************************
Collation: swe7_swedish_ci
  Charset: swe7
       Id: 10
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 25. row ***************************
Collation: swe7_bin
  Charset: swe7
       Id: 82
  Default:
 Compiled:
  Sortlen: 0
*************************** 26. row ***************************
Collation: ascii_general_ci
  Charset: ascii
       Id: 11
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 27. row ***************************
Collation: ascii_bin
  Charset: ascii
       Id: 65
  Default:
 Compiled:
  Sortlen: 0
*************************** 28. row ***************************
Collation: ujis_japanese_ci
  Charset: ujis
       Id: 12
  Default: Yes
 Compiled: Yes
  Sortlen: 1
*************************** 29. row ***************************
Collation: ujis_bin
  Charset: ujis
       Id: 91
  Default:
 Compiled: Yes
  Sortlen: 1
*************************** 30. row ***************************
Collation: sjis_japanese_ci
  Charset: sjis
       Id: 13
  Default: Yes
 Compiled: Yes
  Sortlen: 1
*************************** 31. row ***************************
Collation: sjis_bin
  Charset: sjis
       Id: 88
  Default:
 Compiled: Yes
  Sortlen: 1
*************************** 32. row ***************************
Collation: hebrew_general_ci
  Charset: hebrew
       Id: 16
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 33. row ***************************
Collation: hebrew_bin
  Charset: hebrew
       Id: 71
  Default:
 Compiled:
  Sortlen: 0
*************************** 34. row ***************************
Collation: tis620_thai_ci
  Charset: tis620
       Id: 18
  Default: Yes
 Compiled: Yes
  Sortlen: 4
*************************** 35. row ***************************
Collation: tis620_bin
  Charset: tis620
       Id: 89
  Default:
 Compiled: Yes
  Sortlen: 1
*************************** 36. row ***************************
Collation: euckr_korean_ci
  Charset: euckr
       Id: 19
  Default: Yes
 Compiled: Yes
  Sortlen: 1
*************************** 37. row ***************************
Collation: euckr_bin
  Charset: euckr
       Id: 85
  Default:
 Compiled: Yes
  Sortlen: 1
*************************** 38. row ***************************
Collation: koi8u_general_ci
  Charset: koi8u
       Id: 22
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 39. row ***************************
Collation: koi8u_bin
  Charset: koi8u
       Id: 75
  Default:
 Compiled:
  Sortlen: 0
*************************** 40. row ***************************
Collation: gb2312_chinese_ci
  Charset: gb2312
       Id: 24
  Default: Yes
 Compiled: Yes
  Sortlen: 1
*************************** 41. row ***************************
Collation: gb2312_bin
  Charset: gb2312
       Id: 86
  Default:
 Compiled: Yes
  Sortlen: 1
*************************** 42. row ***************************
Collation: greek_general_ci
  Charset: greek
       Id: 25
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 43. row ***************************
Collation: greek_bin
  Charset: greek
       Id: 70
  Default:
 Compiled:
  Sortlen: 0
*************************** 44. row ***************************
Collation: cp1250_general_ci
  Charset: cp1250
       Id: 26
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 45. row ***************************
Collation: cp1250_czech_cs
  Charset: cp1250
       Id: 34
  Default:
 Compiled: Yes
  Sortlen: 2
*************************** 46. row ***************************
Collation: cp1250_croatian_ci
  Charset: cp1250
       Id: 44
  Default:
 Compiled:
  Sortlen: 0
*************************** 47. row ***************************
Collation: cp1250_bin
  Charset: cp1250
       Id: 66
  Default:
 Compiled:
  Sortlen: 0
*************************** 48. row ***************************
Collation: gbk_chinese_ci
  Charset: gbk
       Id: 28
  Default: Yes
 Compiled: Yes
  Sortlen: 1
*************************** 49. row ***************************
Collation: gbk_bin
  Charset: gbk
       Id: 87
  Default:
 Compiled: Yes
  Sortlen: 1
*************************** 50. row ***************************
Collation: latin5_turkish_ci
  Charset: latin5
       Id: 30
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 51. row ***************************
Collation: latin5_bin
  Charset: latin5
       Id: 78
  Default:
 Compiled:
  Sortlen: 0
*************************** 52. row ***************************
Collation: armscii8_general_ci
  Charset: armscii8
       Id: 32
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 53. row ***************************
Collation: armscii8_bin
  Charset: armscii8
       Id: 64
  Default:
 Compiled:
  Sortlen: 0
*************************** 54. row ***************************
Collation: utf8_general_ci
  Charset: utf8
       Id: 33
  Default: Yes
 Compiled: Yes
  Sortlen: 1
*************************** 55. row ***************************
Collation: utf8_bin
  Charset: utf8
       Id: 83
  Default:
 Compiled: Yes
  Sortlen: 1
*************************** 56. row ***************************
Collation: utf8_unicode_ci
  Charset: utf8
       Id: 192
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 57. row ***************************
Collation: utf8_icelandic_ci
  Charset: utf8
       Id: 193
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 58. row ***************************
Collation: utf8_latvian_ci
  Charset: utf8
       Id: 194
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 59. row ***************************
Collation: utf8_romanian_ci
  Charset: utf8
       Id: 195
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 60. row ***************************
Collation: utf8_slovenian_ci
  Charset: utf8
       Id: 196
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 61. row ***************************
Collation: utf8_polish_ci
  Charset: utf8
       Id: 197
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 62. row ***************************
Collation: utf8_estonian_ci
  Charset: utf8
       Id: 198
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 63. row ***************************
Collation: utf8_spanish_ci
  Charset: utf8
       Id: 199
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 64. row ***************************
Collation: utf8_swedish_ci
  Charset: utf8
       Id: 200
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 65. row ***************************
Collation: utf8_turkish_ci
  Charset: utf8
       Id: 201
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 66. row ***************************
Collation: utf8_czech_ci
  Charset: utf8
       Id: 202
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 67. row ***************************
Collation: utf8_danish_ci
  Charset: utf8
       Id: 203
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 68. row ***************************
Collation: utf8_lithuanian_ci
  Charset: utf8
       Id: 204
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 69. row ***************************
Collation: utf8_slovak_ci
  Charset: utf8
       Id: 205
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 70. row ***************************
Collation: utf8_spanish2_ci
  Charset: utf8
       Id: 206
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 71. row ***************************
Collation: utf8_roman_ci
  Charset: utf8
       Id: 207
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 72. row ***************************
Collation: utf8_persian_ci
  Charset: utf8
       Id: 208
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 73. row ***************************
Collation: utf8_esperanto_ci
  Charset: utf8
       Id: 209
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 74. row ***************************
Collation: ucs2_general_ci
  Charset: ucs2
       Id: 35
  Default: Yes
 Compiled: Yes
  Sortlen: 1
*************************** 75. row ***************************
Collation: ucs2_bin
  Charset: ucs2
       Id: 90
  Default:
 Compiled: Yes
  Sortlen: 1
*************************** 76. row ***************************
Collation: ucs2_unicode_ci
  Charset: ucs2
       Id: 128
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 77. row ***************************
Collation: ucs2_icelandic_ci
  Charset: ucs2
       Id: 129
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 78. row ***************************
Collation: ucs2_latvian_ci
  Charset: ucs2
       Id: 130
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 79. row ***************************
Collation: ucs2_romanian_ci
  Charset: ucs2
       Id: 131
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 80. row ***************************
Collation: ucs2_slovenian_ci
  Charset: ucs2
       Id: 132
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 81. row ***************************
Collation: ucs2_polish_ci
  Charset: ucs2
       Id: 133
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 82. row ***************************
Collation: ucs2_estonian_ci
  Charset: ucs2
       Id: 134
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 83. row ***************************
Collation: ucs2_spanish_ci
  Charset: ucs2
       Id: 135
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 84. row ***************************
Collation: ucs2_swedish_ci
  Charset: ucs2
       Id: 136
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 85. row ***************************
Collation: ucs2_turkish_ci
  Charset: ucs2
       Id: 137
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 86. row ***************************
Collation: ucs2_czech_ci
  Charset: ucs2
       Id: 138
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 87. row ***************************
Collation: ucs2_danish_ci
  Charset: ucs2
       Id: 139
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 88. row ***************************
Collation: ucs2_lithuanian_ci
  Charset: ucs2
       Id: 140
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 89. row ***************************
Collation: ucs2_slovak_ci
  Charset: ucs2
       Id: 141
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 90. row ***************************
Collation: ucs2_spanish2_ci
  Charset: ucs2
       Id: 142
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 91. row ***************************
Collation: ucs2_roman_ci
  Charset: ucs2
       Id: 143
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 92. row ***************************
Collation: ucs2_persian_ci
  Charset: ucs2
       Id: 144
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 93. row ***************************
Collation: ucs2_esperanto_ci
  Charset: ucs2
       Id: 145
  Default:
 Compiled: Yes
  Sortlen: 8
*************************** 94. row ***************************
Collation: cp866_general_ci
  Charset: cp866
       Id: 36
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 95. row ***************************
Collation: cp866_bin
  Charset: cp866
       Id: 68
  Default:
 Compiled:
  Sortlen: 0
*************************** 96. row ***************************
Collation: keybcs2_general_ci
  Charset: keybcs2
       Id: 37
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 97. row ***************************
Collation: keybcs2_bin
  Charset: keybcs2
       Id: 73
  Default:
 Compiled:
  Sortlen: 0
*************************** 98. row ***************************
Collation: macce_general_ci
  Charset: macce
       Id: 38
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 99. row ***************************
Collation: macce_bin
  Charset: macce
       Id: 43
  Default:
 Compiled:
  Sortlen: 0
*************************** 100. row ***************************
Collation: macroman_general_ci
  Charset: macroman
       Id: 39
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 101. row ***************************
Collation: macroman_bin
  Charset: macroman
       Id: 53
  Default:
 Compiled:
  Sortlen: 0
*************************** 102. row ***************************
Collation: cp852_general_ci
  Charset: cp852
       Id: 40
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 103. row ***************************
Collation: cp852_bin
  Charset: cp852
       Id: 81
  Default:
 Compiled:
  Sortlen: 0
*************************** 104. row ***************************
Collation: latin7_estonian_cs
  Charset: latin7
       Id: 20
  Default:
 Compiled:
  Sortlen: 0
*************************** 105. row ***************************
Collation: latin7_general_ci
  Charset: latin7
       Id: 41
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 106. row ***************************
Collation: latin7_general_cs
  Charset: latin7
       Id: 42
  Default:
 Compiled:
  Sortlen: 0
*************************** 107. row ***************************
Collation: latin7_bin
  Charset: latin7
       Id: 79
  Default:
 Compiled:
  Sortlen: 0
*************************** 108. row ***************************
Collation: cp1251_bulgarian_ci
  Charset: cp1251
       Id: 14
  Default:
 Compiled:
  Sortlen: 0
*************************** 109. row ***************************
Collation: cp1251_ukrainian_ci
  Charset: cp1251
       Id: 23
  Default:
 Compiled:
  Sortlen: 0
*************************** 110. row ***************************
Collation: cp1251_bin
  Charset: cp1251
       Id: 50
  Default:
 Compiled:
  Sortlen: 0
*************************** 111. row ***************************
Collation: cp1251_general_ci
  Charset: cp1251
       Id: 51
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 112. row ***************************
Collation: cp1251_general_cs
  Charset: cp1251
       Id: 52
  Default:
 Compiled:
  Sortlen: 0
*************************** 113. row ***************************
Collation: cp1256_general_ci
  Charset: cp1256
       Id: 57
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 114. row ***************************
Collation: cp1256_bin
  Charset: cp1256
       Id: 67
  Default:
 Compiled:
  Sortlen: 0
*************************** 115. row ***************************
Collation: cp1257_lithuanian_ci
  Charset: cp1257
       Id: 29
  Default:
 Compiled:
  Sortlen: 0
*************************** 116. row ***************************
Collation: cp1257_bin
  Charset: cp1257
       Id: 58
  Default:
 Compiled:
  Sortlen: 0
*************************** 117. row ***************************
Collation: cp1257_general_ci
  Charset: cp1257
       Id: 59
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 118. row ***************************
Collation: binary
  Charset: binary
       Id: 63
  Default: Yes
 Compiled: Yes
  Sortlen: 1
*************************** 119. row ***************************
Collation: geostd8_general_ci
  Charset: geostd8
       Id: 92
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 120. row ***************************
Collation: geostd8_bin
  Charset: geostd8
       Id: 93
  Default:
 Compiled:
  Sortlen: 0
*************************** 121. row ***************************
Collation: cp932_japanese_ci
  Charset: cp932
       Id: 95
  Default: Yes
 Compiled: Yes
  Sortlen: 1
*************************** 122. row ***************************
Collation: cp932_bin
  Charset: cp932
       Id: 96
  Default:
 Compiled: Yes
  Sortlen: 1
*************************** 123. row ***************************
Collation: eucjpms_japanese_ci
  Charset: eucjpms
       Id: 97
  Default: Yes
 Compiled: Yes
  Sortlen: 1
*************************** 124. row ***************************
Collation: eucjpms_bin
  Charset: eucjpms
       Id: 98
  Default:
 Compiled: Yes
  Sortlen: 1
124 rows in set (0.00 sec)

A collation is only really useful in the context of a character set. Since collation naming convention specifies that the supported character set will be part of the name, you can use LIKE to return collations that are related to a particular character set. For example, to see which collations can be applied to 7bit Swedish, use:

mysql> SHOW COLLATION LIKE '%swe7%'\G
*************************** 1. row ***************************
Collation: swe7_swedish_ci
  Charset: swe7
       Id: 10
  Default: Yes
 Compiled:
  Sortlen: 0
*************************** 2. row ***************************
Collation: swe7_bin
  Charset: swe7
       Id: 82
  Default:
 Compiled:
  Sortlen: 0
2 rows in set (0.07 sec)