devbox
爱喝兽奶帝天荒
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL 知识点分享一：utf8 字符集和排序规则_utf8mb40900aici与utf8mb4bin区别

作者：爱喝兽奶帝天荒 | 2024-07-19 14:25:34

赞

踩

utf8mb40900aici与utf8mb4bin区别

我们经常能在数据库中看到这些: utf8mb4 和 utf8, utf8mb4_unicode_ci, utf8mb4_general_ci, utf8mb4_bin 分别代表什么意思呢?

其实他们表示的是字符集 和 排序规则

字符集：就是用来定义字符在数据库中的编码的集合。 排序规则：用来定义比较字符串的方式。 字符集和排序规则是一对多的关系

一 MySQL 支持多个 Unicode 字符集

utf8mb4: Unicode 字符集的 UTF-8 编码, 每个字符使用 1-4 个字节, mb4 即 most bytes 4

utf8mb3: Unicode 字符集的 UTF-8 编码, 每个字符使用 1-3 个字节. MySQL 8.0 中不推荐使用此字符集, 应改用utf8mb4

ucs2: Unicode 字符集的 UCS-2 编码, 每个字符使用 2 个字节. MySQL 8.0.28 中已弃用, 您应该希望在将来的版本中删除对该字符集的支持

utf16: Unicode字符集的 UTF-16 编码, 每个字符使用 2-4 字节. 与 ucs2 类似, 但具有补充字符的扩展名

utf16le: Unicode 字符集的 UTF-16LE 编码. 像 utf16, 但小端序而不是大端序

utf32: Unicode 字符集的 UTF-32 编码, 每个字符使用 4 个字节

utf8: 是utf8mb3 的别名, 节省空间但不能表达全部的UTF-8 (比如emoji表情). MySQL 的 utf8 实际上不是真正的 UTF-8. 在 MySQL 8.0 中, 此别名已弃用, 改用utf8mb4, utf8 有望在未来的版本中成为 utf8mb4 的别名. 从 MySQL 8.0.28 开始, 在信息模式表的列和 SQL SHOW 语句的输出中, utf8mb3也显示在utf8的位置.

utf8mb4 utf16 utf16le 和 utf32 支持BMP(Basic Multilingual Plane: 基本多语言平面)字符和位于BMP之外的补充字符. utf8mb3 和 ucs2 仅支持 BMP 字符

utf8mb4 是 utf8mb3 的超集

推荐

为了避免 utf8 含义的歧义, 请考虑为字符集引用显式指定 utf8mb4

二 排序规则

大多数Unicode字符集都有一个通用排序规则(由名称中的_general表示, 或由缺少语言说明符表示), 一个二进制排序规则(名称中的_bin表示)和几个特定于语言的排序规则(通过语言说明符指示).

例如, 对于utf8mb4, utf8mb4_general_ci 和 utf8mb 4_bin 是其通用和二进制排序规则, 而 utf8mb4_unicode_ci 是其特定语言的排序规则之一

大多数字符集都有一个二进制排序规则, utf8mb4 是一个例外, 它有两个: utf8mb4_bin 和 utf8mb4_0900_bin(从MySQL 8.0.17起) 这两个二进制排序规则具有相同的排序顺序, 但通过其 pad 属性和排序权重特性进行区分

utf8mb4_general_ci 和 utf8mb4_unicode_ci 对比

从准确性比较:

utf8mb4_unicode_ci 是基于标准的 Unicode 来排序和比较, 能够在各种语言之间精确排序

utf8mb4_general_ci 没有实现 Unicode 排序规则, 在遇到某些特殊语言或者字符集, 排序结果可能不一致. 但在绝大多数情况下, 这些特殊字符的顺序并不需要那么精确

从性能比较:

utf8mb4_general_ci 在比较和排序的时候更快

utf8mb4_unicode_ci 在特殊情况下, Unicode 排序规则为了能够处理特殊字符的情况, 实现了略微复杂的排序算法. 但是在绝大多数情况不会发生此类复杂比较

utf8mb4_0900_ai_ci 和 utf8mb4_bin

utf8mb4_0900_ai_ci: 0900代表Unicode 9.0的规范, ai表示 accent insensitivity(不区分音调), 而ci表示case insensitivity(不区分大小写) 而 as 区分音调 cs 表示区分大小写

utf8mb4_bin: 将字符串每个字符用二进制数据编译存储, 区分大小写, 而且可以存二进制的内容

8.0之后默认不再像之前版本一样是utf8mb4_general_ci 而是统一更新成了utf8mb4_0900_ai_ci

推荐

utf8mb4_0900_ai_ci(默认值) 或 utf8mb4_bin

三 字符集和排序规则怎么看

首先查看支持的字符集和排序规则

MySQL不同版本支持的字符集有所不同，可以使用show charset 和 show collation 查看

  1. mysql> show charset;
  2. +----------+---------------------------------+---------------------+--------+
  3. | Charset  | Description                     | Default collation   | Maxlen |
  4. +----------+---------------------------------+---------------------+--------+
  5. | armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
  6. | ascii    | US ASCII                        | ascii_general_ci    |      1 |
  7. | big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
  8. | binary   | Binary pseudo charset           | binary              |      1 |
  9. | cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
  10. | cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
  11. | cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
  12. | cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
  13. | cp850    | DOS West European               | cp850_general_ci    |      1 |
  14. | cp852    | DOS Central European            | cp852_general_ci    |      1 |
  15. | cp866    | DOS Russian                     | cp866_general_ci    |      1 |
  16. | cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
  17. | dec8     | DEC West European               | dec8_swedish_ci     |      1 |
  18. | eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
  19. | euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
  20. | gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
  21. | gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
  22. | gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
  23. | geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
  24. | greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
  25. | hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
  26. | hp8      | HP West European                | hp8_english_ci      |      1 |
  27. | keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
  28. | koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
  29. | koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
  30. | latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
  31. | latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
  32. | latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
  33. | latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
  34. | macce    | Mac Central European            | macce_general_ci    |      1 |
  35. | macroman | Mac West European               | macroman_general_ci |      1 |
  36. | sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
  37. | swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
  38. | tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
  39. | ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
  40. | ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
  41. | utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
  42. | utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
  43. | utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
  44. | utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
  45. | utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
  46. +----------+---------------------------------+---------------------+--------+
  47. 41 rows in set (0.01 sec)
  48.  
  49. mysql> show collation;
  50. +----------------------------+----------+-----+---------+----------+---------+---------------+
  51. | Collation                  | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
  52. +----------------------------+----------+-----+---------+----------+---------+---------------+
  53. | armscii8_bin               | armscii8 |  64 |         | Yes      |       1 | PAD SPACE     |
  54. | armscii8_general_ci        | armscii8 |  32 | Yes     | Yes      |       1 | PAD SPACE     |
  55. | ascii_bin                  | ascii    |  65 |         | Yes      |       1 | PAD SPACE     |
  56. | ascii_general_ci           | ascii    |  11 | Yes     | Yes      |       1 | PAD SPACE     |
  57. | big5_bin                   | big5     |  84 |         | Yes      |       1 | PAD SPACE     |
  58. | big5_chinese_ci            | big5     |   1 | Yes     | Yes      |       1 | PAD SPACE     |
  59. | binary                     | binary   |  63 | Yes     | Yes      |       1 | NO PAD        |
  60. | cp1250_bin                 | cp1250   |  66 |         | Yes      |       1 | PAD SPACE     |
  61. | cp1250_croatian_ci         | cp1250   |  44 |         | Yes      |       1 | PAD SPACE     |
  62. | cp1250_czech_cs            | cp1250   |  34 |         | Yes      |       2 | PAD SPACE     |
  63. | cp1250_general_ci          | cp1250   |  26 | Yes     | Yes      |       1 | PAD SPACE     |
  64. | cp1250_polish_ci           | cp1250   |  99 |         | Yes      |       1 | PAD SPACE     |
  65. | cp1251_bin                 | cp1251   |  50 |         | Yes      |       1 | PAD SPACE     |
  66. | cp1251_bulgarian_ci        | cp1251   |  14 |         | Yes      |       1 | PAD SPACE     |
  67. | cp1251_general_ci          | cp1251   |  51 | Yes     | Yes      |       1 | PAD SPACE     |
  68. | cp1251_general_cs          | cp1251   |  52 |         | Yes      |       1 | PAD SPACE     |
  69. | cp1251_ukrainian_ci        | cp1251   |  23 |         | Yes      |       1 | PAD SPACE     |
  70. | cp1256_bin                 | cp1256   |  67 |         | Yes      |       1 | PAD SPACE     |
  71. | cp1256_general_ci          | cp1256   |  57 | Yes     | Yes      |       1 | PAD SPACE     |
  72. | cp1257_bin                 | cp1257   |  58 |         | Yes      |       1 | PAD SPACE     |
  73. | cp1257_general_ci          | cp1257   |  59 | Yes     | Yes      |       1 | PAD SPACE     |
  74. | cp1257_lithuanian_ci       | cp1257   |  29 |         | Yes      |       1 | PAD SPACE     |
  75. | cp850_bin                  | cp850    |  80 |         | Yes      |       1 | PAD SPACE     |
  76. | cp850_general_ci           | cp850    |   4 | Yes     | Yes      |       1 | PAD SPACE     |
  77. | cp852_bin                  | cp852    |  81 |         | Yes      |       1 | PAD SPACE     |
  78. | cp852_general_ci           | cp852    |  40 | Yes     | Yes      |       1 | PAD SPACE     |
  79. | cp866_bin                  | cp866    |  68 |         | Yes      |       1 | PAD SPACE     |
  80. | cp866_general_ci           | cp866    |  36 | Yes     | Yes      |       1 | PAD SPACE     |
  81. | cp932_bin                  | cp932    |  96 |         | Yes      |       1 | PAD SPACE     |
  82. | cp932_japanese_ci          | cp932    |  95 | Yes     | Yes      |       1 | PAD SPACE     |
  83. | dec8_bin                   | dec8     |  69 |         | Yes      |       1 | PAD SPACE     |
  84. | dec8_swedish_ci            | dec8     |   3 | Yes     | Yes      |       1 | PAD SPACE     |
  85. | eucjpms_bin                | eucjpms  |  98 |         | Yes      |       1 | PAD SPACE     |
  86. | eucjpms_japanese_ci        | eucjpms  |  97 | Yes     | Yes      |       1 | PAD SPACE     |
  87. | euckr_bin                  | euckr    |  85 |         | Yes      |       1 | PAD SPACE     |
  88. | euckr_korean_ci            | euckr    |  19 | Yes     | Yes      |       1 | PAD SPACE     |
  89. | gb18030_bin                | gb18030  | 249 |         | Yes      |       1 | PAD SPACE     |
  90. | gb18030_chinese_ci         | gb18030  | 248 | Yes     | Yes      |       2 | PAD SPACE     |
  91. | gb18030_unicode_520_ci     | gb18030  | 250 |         | Yes      |       8 | PAD SPACE     |
  92. | gb2312_bin                 | gb2312   |  86 |         | Yes      |       1 | PAD SPACE     |
  93. | gb2312_chinese_ci          | gb2312   |  24 | Yes     | Yes      |       1 | PAD SPACE     |
  94. | gbk_bin                    | gbk      |  87 |         | Yes      |       1 | PAD SPACE     |
  95. | gbk_chinese_ci             | gbk      |  28 | Yes     | Yes      |       1 | PAD SPACE     |
  96. | geostd8_bin                | geostd8  |  93 |         | Yes      |       1 | PAD SPACE     |
  97. | geostd8_general_ci         | geostd8  |  92 | Yes     | Yes      |       1 | PAD SPACE     |
  98. | greek_bin                  | greek    |  70 |         | Yes      |       1 | PAD SPACE     |
  99. | greek_general_ci           | greek    |  25 | Yes     | Yes      |       1 | PAD SPACE     |
  100. | hebrew_bin                 | hebrew   |  71 |         | Yes      |       1 | PAD SPACE     |
  101. | hebrew_general_ci          | hebrew   |  16 | Yes     | Yes      |       1 | PAD SPACE     |
  102. | hp8_bin                    | hp8      |  72 |         | Yes      |       1 | PAD SPACE     |
  103. | hp8_english_ci             | hp8      |   6 | Yes     | Yes      |       1 | PAD SPACE     |
  104. | keybcs2_bin                | keybcs2  |  73 |         | Yes      |       1 | PAD SPACE     |
  105. | keybcs2_general_ci         | keybcs2  |  37 | Yes     | Yes      |       1 | PAD SPACE     |
  106. | koi8r_bin                  | koi8r    |  74 |         | Yes      |       1 | PAD SPACE     |
  107. | koi8r_general_ci           | koi8r    |   7 | Yes     | Yes      |       1 | PAD SPACE     |
  108. | koi8u_bin                  | koi8u    |  75 |         | Yes      |       1 | PAD SPACE     |
  109. | koi8u_general_ci           | koi8u    |  22 | Yes     | Yes      |       1 | PAD SPACE     |
  110. | latin1_bin                 | latin1   |  47 |         | Yes      |       1 | PAD SPACE     |
  111. | latin1_danish_ci           | latin1   |  15 |         | Yes      |       1 | PAD SPACE     |
  112. | latin1_general_ci          | latin1   |  48 |         | Yes      |       1 | PAD SPACE     |
  113. | latin1_general_cs          | latin1   |  49 |         | Yes      |       1 | PAD SPACE     |
  114. | latin1_german1_ci          | latin1   |   5 |         | Yes      |       1 | PAD SPACE     |
  115. | latin1_german2_ci          | latin1   |  31 |         | Yes      |       2 | PAD SPACE     |
  116. | latin1_spanish_ci          | latin1   |  94 |         | Yes      |       1 | PAD SPACE     |
  117. | latin1_swedish_ci          | latin1   |   8 | Yes     | Yes      |       1 | PAD SPACE     |
  118. | latin2_bin                 | latin2   |  77 |         | Yes      |       1 | PAD SPACE     |
  119. | latin2_croatian_ci         | latin2   |  27 |         | Yes      |       1 | PAD SPACE     |
  120. | latin2_czech_cs            | latin2   |   2 |         | Yes      |       4 | PAD SPACE     |
  121. | latin2_general_ci          | latin2   |   9 | Yes     | Yes      |       1 | PAD SPACE     |
  122. | latin2_hungarian_ci        | latin2   |  21 |         | Yes      |       1 | PAD SPACE     |
  123. | latin5_bin                 | latin5   |  78 |         | Yes      |       1 | PAD SPACE     |
  124. | latin5_turkish_ci          | latin5   |  30 | Yes     | Yes      |       1 | PAD SPACE     |
  125. | latin7_bin                 | latin7   |  79 |         | Yes      |       1 | PAD SPACE     |
  126. | latin7_estonian_cs         | latin7   |  20 |         | Yes      |       1 | PAD SPACE     |
  127. | latin7_general_ci          | latin7   |  41 | Yes     | Yes      |       1 | PAD SPACE     |
  128. | latin7_general_cs          | latin7   |  42 |         | Yes      |       1 | PAD SPACE     |
  129. | macce_bin                  | macce    |  43 |         | Yes      |       1 | PAD SPACE     |
  130. | macce_general_ci           | macce    |  38 | Yes     | Yes      |       1 | PAD SPACE     |
  131. | macroman_bin               | macroman |  53 |         | Yes      |       1 | PAD SPACE     |
  132. | macroman_general_ci        | macroman |  39 | Yes     | Yes      |       1 | PAD SPACE     |
  133. | sjis_bin                   | sjis     |  88 |         | Yes      |       1 | PAD SPACE     |
  134. | sjis_japanese_ci           | sjis     |  13 | Yes     | Yes      |       1 | PAD SPACE     |
  135. | swe7_bin                   | swe7     |  82 |         | Yes      |       1 | PAD SPACE     |
  136. | swe7_swedish_ci            | swe7     |  10 | Yes     | Yes      |       1 | PAD SPACE     |
  137. | tis620_bin                 | tis620   |  89 |         | Yes      |       1 | PAD SPACE     |
  138. | tis620_thai_ci             | tis620   |  18 | Yes     | Yes      |       4 | PAD SPACE     |
  139. | ucs2_bin                   | ucs2     |  90 |         | Yes      |       1 | PAD SPACE     |
  140. | ucs2_croatian_ci           | ucs2     | 149 |         | Yes      |       8 | PAD SPACE     |
  141. | ucs2_czech_ci              | ucs2     | 138 |         | Yes      |       8 | PAD SPACE     |
  142. | ucs2_danish_ci             | ucs2     | 139 |         | Yes      |       8 | PAD SPACE     |
  143. | ucs2_esperanto_ci          | ucs2     | 145 |         | Yes      |       8 | PAD SPACE     |
  144. | ucs2_estonian_ci           | ucs2     | 134 |         | Yes      |       8 | PAD SPACE     |
  145. | ucs2_general_ci            | ucs2     |  35 | Yes     | Yes      |       1 | PAD SPACE     |
  146. | ucs2_general_mysql500_ci   | ucs2     | 159 |         | Yes      |       1 | PAD SPACE     |
  147. | ucs2_german2_ci            | ucs2     | 148 |         | Yes      |       8 | PAD SPACE     |
  148. | ucs2_hungarian_ci          | ucs2     | 146 |         | Yes      |       8 | PAD SPACE     |
  149. | ucs2_icelandic_ci          | ucs2     | 129 |         | Yes      |       8 | PAD SPACE     |
  150. | ucs2_latvian_ci            | ucs2     | 130 |         | Yes      |       8 | PAD SPACE     |
  151. | ucs2_lithuanian_ci         | ucs2     | 140 |         | Yes      |       8 | PAD SPACE     |
  152. | ucs2_persian_ci            | ucs2     | 144 |         | Yes      |       8 | PAD SPACE     |
  153. | ucs2_polish_ci             | ucs2     | 133 |         | Yes      |       8 | PAD SPACE     |
  154. | ucs2_romanian_ci           | ucs2     | 131 |         | Yes      |       8 | PAD SPACE     |
  155. | ucs2_roman_ci              | ucs2     | 143 |         | Yes      |       8 | PAD SPACE     |
  156. | ucs2_sinhala_ci            | ucs2     | 147 |         | Yes      |       8 | PAD SPACE     |
  157. | ucs2_slovak_ci             | ucs2     | 141 |         | Yes      |       8 | PAD SPACE     |
  158. | ucs2_slovenian_ci          | ucs2     | 132 |         | Yes      |       8 | PAD SPACE     |
  159. | ucs2_spanish2_ci           | ucs2     | 142 |         | Yes      |       8 | PAD SPACE     |
  160. | ucs2_spanish_ci            | ucs2     | 135 |         | Yes      |       8 | PAD SPACE     |
  161. | ucs2_swedish_ci            | ucs2     | 136 |         | Yes      |       8 | PAD SPACE     |
  162. | ucs2_turkish_ci            | ucs2     | 137 |         | Yes      |       8 | PAD SPACE     |
  163. | ucs2_unicode_520_ci        | ucs2     | 150 |         | Yes      |       8 | PAD SPACE     |
  164. | ucs2_unicode_ci            | ucs2     | 128 |         | Yes      |       8 | PAD SPACE     |
  165. | ucs2_vietnamese_ci         | ucs2     | 151 |         | Yes      |       8 | PAD SPACE     |
  166. | ujis_bin                   | ujis     |  91 |         | Yes      |       1 | PAD SPACE     |
  167. | ujis_japanese_ci           | ujis     |  12 | Yes     | Yes      |       1 | PAD SPACE     |
  168. | utf16le_bin                | utf16le  |  62 |         | Yes      |       1 | PAD SPACE     |
  169. | utf16le_general_ci         | utf16le  |  56 | Yes     | Yes      |       1 | PAD SPACE     |
  170. | utf16_bin                  | utf16    |  55 |         | Yes      |       1 | PAD SPACE     |
  171. | utf16_croatian_ci          | utf16    | 122 |         | Yes      |       8 | PAD SPACE     |
  172. | utf16_czech_ci             | utf16    | 111 |         | Yes      |       8 | PAD SPACE     |
  173. | utf16_danish_ci            | utf16    | 112 |         | Yes      |       8 | PAD SPACE     |
  174. | utf16_esperanto_ci         | utf16    | 118 |         | Yes      |       8 | PAD SPACE     |
  175. | utf16_estonian_ci          | utf16    | 107 |         | Yes      |       8 | PAD SPACE     |
  176. | utf16_general_ci           | utf16    |  54 | Yes     | Yes      |       1 | PAD SPACE     |
  177. | utf16_german2_ci           | utf16    | 121 |         | Yes      |       8 | PAD SPACE     |
  178. | utf16_hungarian_ci         | utf16    | 119 |         | Yes      |       8 | PAD SPACE     |
  179. | utf16_icelandic_ci         | utf16    | 102 |         | Yes      |       8 | PAD SPACE     |
  180. | utf16_latvian_ci           | utf16    | 103 |         | Yes      |       8 | PAD SPACE     |
  181. | utf16_lithuanian_ci        | utf16    | 113 |         | Yes      |       8 | PAD SPACE     |
  182. | utf16_persian_ci           | utf16    | 117 |         | Yes      |       8 | PAD SPACE     |
  183. | utf16_polish_ci            | utf16    | 106 |         | Yes      |       8 | PAD SPACE     |
  184. | utf16_romanian_ci          | utf16    | 104 |         | Yes      |       8 | PAD SPACE     |
  185. | utf16_roman_ci             | utf16    | 116 |         | Yes      |       8 | PAD SPACE     |
  186. | utf16_sinhala_ci           | utf16    | 120 |         | Yes      |       8 | PAD SPACE     |
  187. | utf16_slovak_ci            | utf16    | 114 |         | Yes      |       8 | PAD SPACE     |
  188. | utf16_slovenian_ci         | utf16    | 105 |         | Yes      |       8 | PAD SPACE     |
  189. | utf16_spanish2_ci          | utf16    | 115 |         | Yes      |       8 | PAD SPACE     |
  190. | utf16_spanish_ci           | utf16    | 108 |         | Yes      |       8 | PAD SPACE     |
  191. | utf16_swedish_ci           | utf16    | 109 |         | Yes      |       8 | PAD SPACE     |
  192. | utf16_turkish_ci           | utf16    | 110 |         | Yes      |       8 | PAD SPACE     |
  193. | utf16_unicode_520_ci       | utf16    | 123 |         | Yes      |       8 | PAD SPACE     |
  194. | utf16_unicode_ci           | utf16    | 101 |         | Yes      |       8 | PAD SPACE     |
  195. | utf16_vietnamese_ci        | utf16    | 124 |         | Yes      |       8 | PAD SPACE     |
  196. | utf32_bin                  | utf32    |  61 |         | Yes      |       1 | PAD SPACE     |
  197. | utf32_croatian_ci          | utf32    | 181 |         | Yes      |       8 | PAD SPACE     |
  198. | utf32_czech_ci             | utf32    | 170 |         | Yes      |       8 | PAD SPACE     |
  199. | utf32_danish_ci            | utf32    | 171 |         | Yes      |       8 | PAD SPACE     |
  200. | utf32_esperanto_ci         | utf32    | 177 |         | Yes      |       8 | PAD SPACE     |
  201. | utf32_estonian_ci          | utf32    | 166 |         | Yes      |       8 | PAD SPACE     |
  202. | utf32_general_ci           | utf32    |  60 | Yes     | Yes      |       1 | PAD SPACE     |
  203. | utf32_german2_ci           | utf32    | 180 |         | Yes      |       8 | PAD SPACE     |
  204. | utf32_hungarian_ci         | utf32    | 178 |         | Yes      |       8 | PAD SPACE     |
  205. | utf32_icelandic_ci         | utf32    | 161 |         | Yes      |       8 | PAD SPACE     |
  206. | utf32_latvian_ci           | utf32    | 162 |         | Yes      |       8 | PAD SPACE     |
  207. | utf32_lithuanian_ci        | utf32    | 172 |         | Yes      |       8 | PAD SPACE     |
  208. | utf32_persian_ci           | utf32    | 176 |         | Yes      |       8 | PAD SPACE     |
  209. | utf32_polish_ci            | utf32    | 165 |         | Yes      |       8 | PAD SPACE     |
  210. | utf32_romanian_ci          | utf32    | 163 |         | Yes      |       8 | PAD SPACE     |
  211. | utf32_roman_ci             | utf32    | 175 |         | Yes      |       8 | PAD SPACE     |
  212. | utf32_sinhala_ci           | utf32    | 179 |         | Yes      |       8 | PAD SPACE     |
  213. | utf32_slovak_ci            | utf32    | 173 |         | Yes      |       8 | PAD SPACE     |
  214. | utf32_slovenian_ci         | utf32    | 164 |         | Yes      |       8 | PAD SPACE     |
  215. | utf32_spanish2_ci          | utf32    | 174 |         | Yes      |       8 | PAD SPACE     |
  216. | utf32_spanish_ci           | utf32    | 167 |         | Yes      |       8 | PAD SPACE     |
  217. | utf32_swedish_ci           | utf32    | 168 |         | Yes      |       8 | PAD SPACE     |
  218. | utf32_turkish_ci           | utf32    | 169 |         | Yes      |       8 | PAD SPACE     |
  219. | utf32_unicode_520_ci       | utf32    | 182 |         | Yes      |       8 | PAD SPACE     |
  220. | utf32_unicode_ci           | utf32    | 160 |         | Yes      |       8 | PAD SPACE     |
  221. | utf32_vietnamese_ci        | utf32    | 183 |         | Yes      |       8 | PAD SPACE     |
  222. | utf8mb4_0900_ai_ci         | utf8mb4  | 255 | Yes     | Yes      |       0 | NO PAD        |
  223. | utf8mb4_0900_as_ci         | utf8mb4  | 305 |         | Yes      |       0 | NO PAD        |
  224. | utf8mb4_0900_as_cs         | utf8mb4  | 278 |         | Yes      |       0 | NO PAD        |
  225. | utf8mb4_0900_bin           | utf8mb4  | 309 |         | Yes      |       1 | NO PAD        |
  226. | utf8mb4_bin                | utf8mb4  |  46 |         | Yes      |       1 | PAD SPACE     |
  227. | utf8mb4_croatian_ci        | utf8mb4  | 245 |         | Yes      |       8 | PAD SPACE     |
  228. | utf8mb4_cs_0900_ai_ci      | utf8mb4  | 266 |         | Yes      |       0 | NO PAD        |
  229. | utf8mb4_cs_0900_as_cs      | utf8mb4  | 289 |         | Yes      |       0 | NO PAD        |
  230. | utf8mb4_czech_ci           | utf8mb4  | 234 |         | Yes      |       8 | PAD SPACE     |
  231. | utf8mb4_danish_ci          | utf8mb4  | 235 |         | Yes      |       8 | PAD SPACE     |
  232. | utf8mb4_da_0900_ai_ci      | utf8mb4  | 267 |         | Yes      |       0 | NO PAD        |
  233. | utf8mb4_da_0900_as_cs      | utf8mb4  | 290 |         | Yes      |       0 | NO PAD        |
  234. | utf8mb4_de_pb_0900_ai_ci   | utf8mb4  | 256 |         | Yes      |       0 | NO PAD        |
  235. | utf8mb4_de_pb_0900_as_cs   | utf8mb4  | 279 |         | Yes      |       0 | NO PAD        |
  236. | utf8mb4_eo_0900_ai_ci      | utf8mb4  | 273 |         | Yes      |       0 | NO PAD        |
  237. | utf8mb4_eo_0900_as_cs      | utf8mb4  | 296 |         | Yes      |       0 | NO PAD        |
  238. | utf8mb4_esperanto_ci       | utf8mb4  | 241 |         | Yes      |       8 | PAD SPACE     |
  239. | utf8mb4_estonian_ci        | utf8mb4  | 230 |         | Yes      |       8 | PAD SPACE     |
  240. | utf8mb4_es_0900_ai_ci      | utf8mb4  | 263 |         | Yes      |       0 | NO PAD        |
  241. | utf8mb4_es_0900_as_cs      | utf8mb4  | 286 |         | Yes      |       0 | NO PAD        |
  242. | utf8mb4_es_trad_0900_ai_ci | utf8mb4  | 270 |         | Yes      |       0 | NO PAD        |
  243. | utf8mb4_es_trad_0900_as_cs | utf8mb4  | 293 |         | Yes      |       0 | NO PAD        |
  244. | utf8mb4_et_0900_ai_ci      | utf8mb4  | 262 |         | Yes      |       0 | NO PAD        |
  245. | utf8mb4_et_0900_as_cs      | utf8mb4  | 285 |         | Yes      |       0 | NO PAD        |
  246. | utf8mb4_general_ci         | utf8mb4  |  45 |         | Yes      |       1 | PAD SPACE     |
  247. | utf8mb4_german2_ci         | utf8mb4  | 244 |         | Yes      |       8 | PAD SPACE     |
  248. | utf8mb4_hr_0900_ai_ci      | utf8mb4  | 275 |         | Yes      |       0 | NO PAD        |
  249. | utf8mb4_hr_0900_as_cs      | utf8mb4  | 298 |         | Yes      |       0 | NO PAD        |
  250. | utf8mb4_hungarian_ci       | utf8mb4  | 242 |         | Yes      |       8 | PAD SPACE     |
  251. | utf8mb4_hu_0900_ai_ci      | utf8mb4  | 274 |         | Yes      |       0 | NO PAD        |
  252. | utf8mb4_hu_0900_as_cs      | utf8mb4  | 297 |         | Yes      |       0 | NO PAD        |
  253. | utf8mb4_icelandic_ci       | utf8mb4  | 225 |         | Yes      |       8 | PAD SPACE     |
  254. | utf8mb4_is_0900_ai_ci      | utf8mb4  | 257 |         | Yes      |       0 | NO PAD        |
  255. | utf8mb4_is_0900_as_cs      | utf8mb4  | 280 |         | Yes      |       0 | NO PAD        |
  256. | utf8mb4_ja_0900_as_cs      | utf8mb4  | 303 |         | Yes      |       0 | NO PAD        |
  257. | utf8mb4_ja_0900_as_cs_ks   | utf8mb4  | 304 |         | Yes      |      24 | NO PAD        |
  258. | utf8mb4_latvian_ci         | utf8mb4  | 226 |         | Yes      |       8 | PAD SPACE     |
  259. | utf8mb4_la_0900_ai_ci      | utf8mb4  | 271 |         | Yes      |       0 | NO PAD        |
  260. | utf8mb4_la_0900_as_cs      | utf8mb4  | 294 |         | Yes      |       0 | NO PAD        |
  261. | utf8mb4_lithuanian_ci      | utf8mb4  | 236 |         | Yes      |       8 | PAD SPACE     |
  262. | utf8mb4_lt_0900_ai_ci      | utf8mb4  | 268 |         | Yes      |       0 | NO PAD        |
  263. | utf8mb4_lt_0900_as_cs      | utf8mb4  | 291 |         | Yes      |       0 | NO PAD        |
  264. | utf8mb4_lv_0900_ai_ci      | utf8mb4  | 258 |         | Yes      |       0 | NO PAD        |
  265. | utf8mb4_lv_0900_as_cs      | utf8mb4  | 281 |         | Yes      |       0 | NO PAD        |
  266. | utf8mb4_persian_ci         | utf8mb4  | 240 |         | Yes      |       8 | PAD SPACE     |
  267. | utf8mb4_pl_0900_ai_ci      | utf8mb4  | 261 |         | Yes      |       0 | NO PAD        |
  268. | utf8mb4_pl_0900_as_cs      | utf8mb4  | 284 |         | Yes      |       0 | NO PAD        |
  269. | utf8mb4_polish_ci          | utf8mb4  | 229 |         | Yes      |       8 | PAD SPACE     |
  270. | utf8mb4_romanian_ci        | utf8mb4  | 227 |         | Yes      |       8 | PAD SPACE     |
  271. | utf8mb4_roman_ci           | utf8mb4  | 239 |         | Yes      |       8 | PAD SPACE     |
  272. | utf8mb4_ro_0900_ai_ci      | utf8mb4  | 259 |         | Yes      |       0 | NO PAD        |
  273. | utf8mb4_ro_0900_as_cs      | utf8mb4  | 282 |         | Yes      |       0 | NO PAD        |
  274. | utf8mb4_ru_0900_ai_ci      | utf8mb4  | 306 |         | Yes      |       0 | NO PAD        |
  275. | utf8mb4_ru_0900_as_cs      | utf8mb4  | 307 |         | Yes      |       0 | NO PAD        |
  276. | utf8mb4_sinhala_ci         | utf8mb4  | 243 |         | Yes      |       8 | PAD SPACE     |
  277. | utf8mb4_sk_0900_ai_ci      | utf8mb4  | 269 |         | Yes      |       0 | NO PAD        |
  278. | utf8mb4_sk_0900_as_cs      | utf8mb4  | 292 |         | Yes      |       0 | NO PAD        |
  279. | utf8mb4_slovak_ci          | utf8mb4  | 237 |         | Yes      |       8 | PAD SPACE     |
  280. | utf8mb4_slovenian_ci       | utf8mb4  | 228 |         | Yes      |       8 | PAD SPACE     |
  281. | utf8mb4_sl_0900_ai_ci      | utf8mb4  | 260 |         | Yes      |       0 | NO PAD        |
  282. | utf8mb4_sl_0900_as_cs      | utf8mb4  | 283 |         | Yes      |       0 | NO PAD        |
  283. | utf8mb4_spanish2_ci        | utf8mb4  | 238 |         | Yes      |       8 | PAD SPACE     |
  284. | utf8mb4_spanish_ci         | utf8mb4  | 231 |         | Yes      |       8 | PAD SPACE     |
  285. | utf8mb4_sv_0900_ai_ci      | utf8mb4  | 264 |         | Yes      |       0 | NO PAD        |
  286. | utf8mb4_sv_0900_as_cs      | utf8mb4  | 287 |         | Yes      |       0 | NO PAD        |
  287. | utf8mb4_swedish_ci         | utf8mb4  | 232 |         | Yes      |       8 | PAD SPACE     |
  288. | utf8mb4_tr_0900_ai_ci      | utf8mb4  | 265 |         | Yes      |       0 | NO PAD        |
  289. | utf8mb4_tr_0900_as_cs      | utf8mb4  | 288 |         | Yes      |       0 | NO PAD        |
  290. | utf8mb4_turkish_ci         | utf8mb4  | 233 |         | Yes      |       8 | PAD SPACE     |
  291. | utf8mb4_unicode_520_ci     | utf8mb4  | 246 |         | Yes      |       8 | PAD SPACE     |
  292. | utf8mb4_unicode_ci         | utf8mb4  | 224 |         | Yes      |       8 | PAD SPACE     |
  293. | utf8mb4_vietnamese_ci      | utf8mb4  | 247 |         | Yes      |       8 | PAD SPACE     |
  294. | utf8mb4_vi_0900_ai_ci      | utf8mb4  | 277 |         | Yes      |       0 | NO PAD        |
  295. | utf8mb4_vi_0900_as_cs      | utf8mb4  | 300 |         | Yes      |       0 | NO PAD        |
  296. | utf8mb4_zh_0900_as_cs      | utf8mb4  | 308 |         | Yes      |       0 | NO PAD        |
  297. | utf8_bin                   | utf8     |  83 |         | Yes      |       1 | PAD SPACE     |
  298. | utf8_croatian_ci           | utf8     | 213 |         | Yes      |       8 | PAD SPACE     |
  299. | utf8_czech_ci              | utf8     | 202 |         | Yes      |       8 | PAD SPACE     |
  300. | utf8_danish_ci             | utf8     | 203 |         | Yes      |       8 | PAD SPACE     |
  301. | utf8_esperanto_ci          | utf8     | 209 |         | Yes      |       8 | PAD SPACE     |
  302. | utf8_estonian_ci           | utf8     | 198 |         | Yes      |       8 | PAD SPACE     |
  303. | utf8_general_ci            | utf8     |  33 | Yes     | Yes      |       1 | PAD SPACE     |
  304. | utf8_general_mysql500_ci   | utf8     | 223 |         | Yes      |       1 | PAD SPACE     |
  305. | utf8_german2_ci            | utf8     | 212 |         | Yes      |       8 | PAD SPACE     |
  306. | utf8_hungarian_ci          | utf8     | 210 |         | Yes      |       8 | PAD SPACE     |
  307. | utf8_icelandic_ci          | utf8     | 193 |         | Yes      |       8 | PAD SPACE     |
  308. | utf8_latvian_ci            | utf8     | 194 |         | Yes      |       8 | PAD SPACE     |
  309. | utf8_lithuanian_ci         | utf8     | 204 |         | Yes      |       8 | PAD SPACE     |
  310. | utf8_persian_ci            | utf8     | 208 |         | Yes      |       8 | PAD SPACE     |
  311. | utf8_polish_ci             | utf8     | 197 |         | Yes      |       8 | PAD SPACE     |
  312. | utf8_romanian_ci           | utf8     | 195 |         | Yes      |       8 | PAD SPACE     |
  313. | utf8_roman_ci              | utf8     | 207 |         | Yes      |       8 | PAD SPACE     |
  314. | utf8_sinhala_ci            | utf8     | 211 |         | Yes      |       8 | PAD SPACE     |
  315. | utf8_slovak_ci             | utf8     | 205 |         | Yes      |       8 | PAD SPACE     |
  316. | utf8_slovenian_ci          | utf8     | 196 |         | Yes      |       8 | PAD SPACE     |
  317. | utf8_spanish2_ci           | utf8     | 206 |         | Yes      |       8 | PAD SPACE     |
  318. | utf8_spanish_ci            | utf8     | 199 |         | Yes      |       8 | PAD SPACE     |
  319. | utf8_swedish_ci            | utf8     | 200 |         | Yes      |       8 | PAD SPACE     |
  320. | utf8_tolower_ci            | utf8     |  76 |         | Yes      |       1 | PAD SPACE     |
  321. | utf8_turkish_ci            | utf8     | 201 |         | Yes      |       8 | PAD SPACE     |
  322. | utf8_unicode_520_ci        | utf8     | 214 |         | Yes      |       8 | PAD SPACE     |
  323. | utf8_unicode_ci            | utf8     | 192 |         | Yes      |       8 | PAD SPACE     |
  324. | utf8_vietnamese_ci         | utf8     | 215 |         | Yes      |       8 | PAD SPACE     |
  325. +----------------------------+----------+-----+---------+----------+---------+---------------+
  326. 272 rows in set (0.03 sec)

查看服务器当前字符集和排序规则

可以使用 show variables like '%character%' 和 show variables like '%collation%' 查看

character_set_client 表示客户端使用的字符集

character_set_connection 表示连接层字符集

character_set_results 表示查询结果字符集

character_set_server 表示服务器字符集

  1. mysql> show variables like '%character%';
  2. +--------------------------+--------------------------------+
  3. | Variable_name            | Value                          |
  4. +--------------------------+--------------------------------+
  5. | character_set_client     | utf8mb4                        || character_set_connection | utf8mb4                        |
  6. | character_set_database   | utf8mb4                        || character_set_filesystem | binary                         |
  7. | character_set_results    | utf8mb4                        || character_set_server     | utf8mb4                        |
  8. | character_set_system     | utf8                           || character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
  9. +--------------------------+--------------------------------+
  10. 8 rows in set (0.01 sec)
  11.  
  12. mysql> show variables like '%collation%';
  13. +-------------------------------+--------------------+
  14. | Variable_name                 | Value              |
  15. +-------------------------------+--------------------+
  16. | collation_connection          | utf8mb4_0900_ai_ci |
  17. | collation_database            | utf8mb4_0900_ai_ci || collation_server              | utf8mb4_0900_ai_ci |
  18. | default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
  19. +-------------------------------+--------------------+
  20. 4 rows in set (0.01 sec)

查看数据库， 表，字段的字符集

  1. mysql> use TEST;
  2. Database changed
  3. mysql> show variables like 'character_set_database';
  4. +------------------------+---------+
  5. | Variable_name          | Value   |
  6. +------------------------+---------+
  7. | character_set_database | utf8mb4 |
  8. +------------------------+---------+
  9. 1 row in set (0.01 sec)
  10.  
  11. mysql> show table status from TEST like 'T1';
  12. +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
  13. | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation   | Checksum | Create_options | Comment |
  14. +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
  15. | T1   | InnoDB |10| Dynamic    |0|              0 |16384|               0 |0|         0 | NULL           | 2023-03-22 09:10:17 | NULL        | NULL       | utf8mb4_bin | NULL     ||         |
  16. +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
  17. 1 row in set (0.01 sec)
  18.  
  19. mysql> show full columns from TEST.T1;
  20. +-------+--------------+-------------+------+-----+---------+-------+---------------------------------+---------+
  21. | Field | Type         | Collation   | Null | Key | Default | Extra | Privileges                      | Comment |
  22. +-------+--------------+-------------+------+-----+---------+-------+---------------------------------+---------+
  23. | C1    | varchar(255) | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references |         |
  24. +-------+--------------+-------------+------+-----+---------+-------+---------------------------------+---------+
  25. 1 row in set (0.01 sec)
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/爱喝兽奶帝天荒/article/detail/851964
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号