JayGao

JayGao 的个人博客,分享技术,记录生活,感谢您的支持与关注。

  menu
19 文章
0 浏览
0 当前访客
ღゝ◡╹)ノ❤️

MySQL字符集和排序规则详解 置顶!

MySQL字符集和排序规则详解

概念

  • 字符集的内容包含:字符集(character set)和排序规则(collation rule);

  • 每种字符集可对应一到多个排序规则,每种排序规则对应一种字符集;

  • 字符集是一套字符与一套编码的映射集合,像这样:

    字符编码
    A0
    B1
  • 排序规则是字符集内用来比较每个字符的一套规则,也就是字符的排序方式
    比如要比较字符 A 和 B 的大小,最简单直观的方法就是对比他们对应的编码。显然编码 0 < 1,这种规则下 A < B。那么类似这样的规则集合就是排序规则。单字节字符编码如此,多字节的编码排序也以此类推。

排序规则后缀说明:

  1. _ci : 不区分大小写,Case-insensitive的缩写;
  2. _cs : 区分大小写,Case-sensitive的缩写;
  3. _ai : 不区分重音,Accent-insensitive的缩写;
  4. _as : 区分重音,Accent-sensitive的缩写;
  5. _bin : 二进制;

NO PAD vs PAD SPACE

含义:表示排序时是否需要比较字符后面的空格

  • NO PAD(处理)

如果字符后面有空格,那就把空格当作一个字符处理。也就是在对比的时候不会忽视空格的存在。

  • PAD SPACE(忽略)

表示如果字符后面有空格,可以忽略空格来比较。也就是空格可有可无。

查询已有的字符集

执行mysql命令:SHOW CHARSET ,查看当前mysql支持的所有字符集;如下表所示:

CharsetDescriptionDefault collationMaxlen
armscii8ARMSCII-8 Armenianarmscii8_general_ci1
asciiUS ASCIIascii_general_ci1
big5Big5 Traditional Chinesebig5_chinese_ci2
binaryBinary pseudo charsetbinary1
cp1250Windows Central Europeancp1250_general_ci1
cp1251Windows Cyrilliccp1251_general_ci1
cp1256Windows Arabiccp1256_general_ci1
cp1257Windows Balticcp1257_general_ci1
cp850DOS West Europeancp850_general_ci1
cp852DOS Central Europeancp852_general_ci1
cp866DOS Russiancp866_general_ci1
cp932SJIS for Windows Japanesecp932_japanese_ci2
8-DecDEC West Europeandec8_swedish_ci1
eucjpmsUJIS for Windows Japaneseeucjpms_japanese_ci3
euckrEUC-KR Koreaneuckr_korean_ci2
gb18030China National Standard GB18030gb18030_chinese_ci4
gb2312GB2312 Simplified Chinesegb2312_chinese_ci2
gbkGBK Simplified Chinesegbk_chinese_ci2
geostd8GEOSTD8 Georgiangeostd8_general_ci1
greekISO 8859-7 Greekgreek_general_ci1
hebrewISO 8859-8 Hebrewhebrew_general_ci1
hp8HP West Europeanhp8_english_ci1
keybcs2DOS Kamenicky Czech-Slovakkeybcs2_general_ci1
koi8rKOI8-R Relcom Russiankoi8r_general_ci1
koi8uKOI8-U Ukrainiankoi8u_general_ci1
latin1cp1252 West Europeanlatin1_swedish_ci1
latin2ISO 8859-2 Central Europeanlatin2_general_ci1
latin5ISO 8859-9 Turkishlatin5_turkish_ci1
latin7ISO 8859-13 Balticlatin7_general_ci1
macceMac Central Europeanmacce_general_ci1
macromanMac West Europeanmacroman_general_ci1
sjisShift-JIS Japanesesjis_japanese_ci2
swe77bit Swedishswe7_swedish_ci1
tis620TIS620 Thaitis620_thai_ci1
ucs2UCS-2 Unicodeucs2_general_ci2
ujisEUC-JP Japaneseujis_japanese_ci3
utf16UTF-16 Unicodeutf16_general_ci4
utf16leUTF-16LE Unicodeutf16le_general_ci4
utf32UTF-32 Unicodeutf32_general_ci4
utf8UTF-8 Unicodeutf8_general_ci3
utf8mb4UTF-8 Unicodeutf8mb4_0900_ai_ci4

mysql8推荐是utf8mb4,所以看到最后一行:字符集(Chartset)utf8mb4使用的事UTF-8 Unicode编码,它的默认排序规则(collation)是utf8mb4_0900_ai_ci,该字符集占用最大字节为4个。

查询指定字符集的排序规则

这里我们只关心utf8mb4,查询utf8mb4的所有排序规则,执行mysql命令:SHOW COLLATION LIKE 'utf8mb4%';结果如下表格:

CollationCharsetIdDefaultCompiledSortlenPad_attribute
utf8mb4_0900_ai_ciutf8mb4255YesYes0NO PAD
utf8mb4_0900_as_ciutf8mb4305 Yes0NO PAD
utf8mb4_0900_as_csutf8mb4278 Yes0NO PAD
utf8mb4_0900_binutf8mb4309 Yes1NO PAD
utf8mb4_binutf8mb446 Yes1PAD SPACE
utf8mb4_croatian_ciutf8mb4245 Yes8PAD SPACE
utf8mb4_cs_0900_ai_ciutf8mb4266 Yes0NO PAD
utf8mb4_cs_0900_as_csutf8mb4289 Yes0NO PAD
utf8mb4_czech_ciutf8mb4234 Yes8PAD SPACE
utf8mb4_danish_ciutf8mb4235 Yes8PAD SPACE
utf8mb4_da_0900_ai_ciutf8mb4267 Yes0NO PAD
utf8mb4_da_0900_as_csutf8mb4290 Yes0NO PAD
utf8mb4_de_pb_0900_ai_ciutf8mb4256 Yes0NO PAD
utf8mb4_de_pb_0900_as_csutf8mb4279 Yes0NO PAD
utf8mb4_eo_0900_ai_ciutf8mb4273 Yes0NO PAD
utf8mb4_eo_0900_as_csutf8mb4296 Yes0NO PAD
utf8mb4_esperanto_ciutf8mb4241 Yes8PAD SPACE
utf8mb4_estonian_ciutf8mb4230 Yes8PAD SPACE
utf8mb4_es_0900_ai_ciutf8mb4263 Yes0NO PAD
utf8mb4_es_0900_as_csutf8mb4286 Yes0NO PAD
utf8mb4_es_trad_0900_ai_ciutf8mb4270 Yes0NO PAD
utf8mb4_es_trad_0900_as_csutf8mb4293 Yes0NO PAD
utf8mb4_et_0900_ai_ciutf8mb4262 Yes0NO PAD
utf8mb4_et_0900_as_csutf8mb4285 Yes0NO PAD
utf8mb4_general_ciutf8mb445 Yes1PAD SPACE
utf8mb4_german2_ciutf8mb4244 Yes8PAD SPACE
utf8mb4_hr_0900_ai_ciutf8mb4275 Yes0NO PAD
utf8mb4_hr_0900_as_csutf8mb4298 Yes0NO PAD
utf8mb4_hungarian_ciutf8mb4242 Yes8PAD SPACE
utf8mb4_hu_0900_ai_ciutf8mb4274 Yes0NO PAD
utf8mb4_hu_0900_as_csutf8mb4297 Yes0NO PAD
utf8mb4_icelandic_ciutf8mb4225 Yes8PAD SPACE
utf8mb4_is_0900_ai_ciutf8mb4257 Yes0NO PAD
utf8mb4_is_0900_as_csutf8mb4280 Yes0NO PAD
utf8mb4_ja_0900_as_csutf8mb4303 Yes0NO PAD
utf8mb4_ja_0900_as_cs_ksutf8mb4304 Yes24NO PAD
utf8mb4_latvian_ciutf8mb4226 Yes8PAD SPACE
utf8mb4_la_0900_ai_ciutf8mb4271 Yes0NO PAD
utf8mb4_la_0900_as_csutf8mb4294 Yes0NO PAD
utf8mb4_lithuanian_ciutf8mb4236 Yes8PAD SPACE
utf8mb4_lt_0900_ai_ciutf8mb4268 Yes0NO PAD
utf8mb4_lt_0900_as_csutf8mb4291 Yes0NO PAD
utf8mb4_lv_0900_ai_ciutf8mb4258 Yes0NO PAD
utf8mb4_lv_0900_as_csutf8mb4281 Yes0NO PAD
utf8mb4_persian_ciutf8mb4240 Yes8PAD SPACE
utf8mb4_pl_0900_ai_ciutf8mb4261 Yes0NO PAD
utf8mb4_pl_0900_as_csutf8mb4284 Yes0NO PAD
utf8mb4_polish_ciutf8mb4229 Yes8PAD SPACE
utf8mb4_romanian_ciutf8mb4227 Yes8PAD SPACE
utf8mb4_roman_ciutf8mb4239 Yes8PAD SPACE
utf8mb4_ro_0900_ai_ciutf8mb4259 Yes0NO PAD
utf8mb4_ro_0900_as_csutf8mb4282 Yes0NO PAD
utf8mb4_ru_0900_ai_ciutf8mb4306 Yes0NO PAD
utf8mb4_ru_0900_as_csutf8mb4307 Yes0NO PAD
utf8mb4_sinhala_ciutf8mb4243 Yes8PAD SPACE
utf8mb4_sk_0900_ai_ciutf8mb4269 Yes0NO PAD
utf8mb4_sk_0900_as_csutf8mb4292 Yes0NO PAD
utf8mb4_slovak_ciutf8mb4237 Yes8PAD SPACE
utf8mb4_slovenian_ciutf8mb4228 Yes8PAD SPACE
utf8mb4_sl_0900_ai_ciutf8mb4260 Yes0NO PAD
utf8mb4_sl_0900_as_csutf8mb4283 Yes0NO PAD
utf8mb4_spanish2_ciutf8mb4238 Yes8PAD SPACE
utf8mb4_spanish_ciutf8mb4231 Yes8PAD SPACE
utf8mb4_sv_0900_ai_ciutf8mb4264 Yes0NO PAD
utf8mb4_sv_0900_as_csutf8mb4287 Yes0NO PAD
utf8mb4_swedish_ciutf8mb4232 Yes8PAD SPACE
utf8mb4_tr_0900_ai_ciutf8mb4265 Yes0NO PAD
utf8mb4_tr_0900_as_csutf8mb4288 Yes0NO PAD
utf8mb4_turkish_ciutf8mb4233 Yes8PAD SPACE
utf8mb4_unicode_520_ciutf8mb4246 Yes8PAD SPACE
utf8mb4_unicode_ciutf8mb4224 Yes8PAD SPACE
utf8mb4_vietnamese_ciutf8mb4247 Yes8PAD SPACE
utf8mb4_vi_0900_ai_ciutf8mb4277 Yes0NO PAD
utf8mb4_vi_0900_as_csutf8mb4300 Yes0NO PAD
utf8mb4_zh_0900_as_csutf8mb4308 Yes0NO PAD

其中以下排序规则为通用的:

排序规则
utf8mb4_0900_ai_ci
utf8mb4_0900_as_ci
utf8mb4_0900_as_cs
utf8mb4_0900_bin
utf8mb4_bin
utf8mb4_general_ci
utf8mb4_unicode_520_ci
utf8mb4_unicode_ci

下面这个格式代表地区或国家语言支持,这里只关注中文中国

utf8mb4_zh_0900_as_cs

其他没有例举出来的,表示其他国家或地区的排序规则,不关注。

字符排序含义区别

MySQL 8.0 .1默认的是 utf8mb4_0900_ai_ci,属于 utf8mb4_unicode_ci 中的一种,具体含义如下:

  1. uft8mb4 表示用 UTF-8 编码方案,每个字符最多占 4 个字节。
  2. 0900 指的是 Unicode 校对算法版本。(Unicode 归类算法是用于比较符合 Unicode 标准要求的两个 Unicode 字符串的方法)。
  3. ai 指的是口音不敏感。也就是说,排序时 e,è,é,ê 和 ë 之间没有区别。
  4. ci 表示不区分大小写。也就是说,排序时 p 和 P 之间没有区别。
  5. utf8mb4 已成为默认字符集,在 MySQL 8.0.1 及更高版本中将 utf8mb4_0900_ai_ci 作为默认排序规则。以前,utf8mb4_general_ci 是默认排序规则。由于 utf8mb4_0900_ai_ci 排序规则现在是默认排序规则,因此默认情况下新表格可以存储基本多语言平面之外的字符。现在可以默认存储表情符号。如果需要重音灵敏度和区分大小写,则可以使用 utf8mb4_0900_as_cs 代替。
  6. utf8mb4_general_ci(general_ci 则是在 unicode_ci 排序上做了一定优化,排序效率提高了一点(十分之一左右),只支持单字符对比排序。)在StackOverflow中推荐是使用 unicode_ci,因为general_ci的效率提升有限。

例如:

在德语中 ß 书写为 ss
unicode_ci 只有 ß = ss,单个 s 和 ß 是不同的。
general_ci 做了优化,只支持单字符比较,但是又需要 对 ß 进行排序,所以结果是:ß = s

  • utf8mb4_unicode_ci(遵守的Unicode组织的4.0.0归类算法排序)
  • utf8mb4_unicode_520_ci(遵守的Unicode组织的 5.2.0归类算法排,不区分大小写)
  • utf8mb4_0900_ai_ci(遵守的Unicode组织的 9.0.0归类算法排,不区分重音,不区分大小写,mysql8的默认排序规则)
  • utf8mb4_0900_as_ci(遵守的Unicode组织的 9.0.0归类算法排,区分重音,不区分大小写)
  • utf8mb4_0900_as_cs(遵守的Unicode组织的 9.0.0归类算法排,区分重音,区分大小写)
    utf8mb4_0900_bin(遵守的Unicode组织的 9.0.0归类算法排,对于二进制字符集,字母大小写和重音等效的概念不适用,对于存储为二进制字符串的单字节字符,字符和字节边界是相同的,因此在比较中,字母大小写和重音符号的差异是显著的。也就是说,排序规则区分大小写和重音)
  • utf8mb4_zh_0900_as_cs(遵守的Unicode组织的 9.0.0归类算法排,区分重音,区分大小写,支持中国中文)
  • utf8mb4_bin:将字符串每个字符用二进制数据编译存储,区分大小写,而且可以存二进制的内容。
  • utf8mb4_0900_bin和utf8mb4_bin去区别:归类算法排版本不同,前者会处理字符串结尾的空格,后者会忽略字符串结尾的空格。这两个二进制排序规则具有相同的排序顺序,但它们的pad属性和排序权重特征不同。请参见排序板属性和字符排序权重

官网文档:

For the binary character set, the concepts of lettercase and accent equivalence do not apply:

  • For single-byte characters stored as binary strings, character and byte boundaries are the same, so lettercase and accent differences are significant in comparisons. That is, the binary collation is case-sensitive and accent-sensitive.

**译文:**对于存储为二进制字符串的单字节字符,字符和字节边界是相同的,因此字母大小写和重音差异在比较中很重要。也就是说,二进制排序规则区分大小写和重音。

对于存储为二进制字符串的多字节字符,字符和字节边界不同。字符边界丢失,因此依赖于它们的比较没有意义。

总结

1、通用业务场景使用utf8mb4和排序规则utf8mb4_0900_ai_ci,不需要区分大小写,不需要区分重音;

2、不区分大小写区分重音使用:utf8mb4_0900_as_ci;

3、不区分大小写也不区分重音可以使用以下三种,三种区别可以对比以上排序规则:

utf8mb4_0900_as_cs
utf8mb4_0900_bin
utf8mb4_bin

4、下面这三个版本的排序归类算法版本较低,不推荐使用

utf8mb4_general_ci
utf8mb4_unicode_520_ci
utf8mb4_unicode_ci

5、如果业务场景种使用的中文很多,推荐使用utf8mb4_zh_0900_as_cs,优点:包含所有目前支持的中文,占用空间较小。

详情参阅mysql官网:MySQL :: MySQL 8.0 Reference Manual :: 10.10.1 Unicode Character Sets


标题:MySQL字符集和排序规则详解
作者:JayGao
地址:https://www.xiaofang520.com/articles/2022/04/21/1650516606373.html
版权:版权JayGao所有,如需转载,请注明出处
捐赠: