dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Oct 18, 2006

MySQL and Character Sets - Page 2

By Ian Gilfillan

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)


MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM