mysql utf8 czech ci_MySQL字符集小结

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL的字符集

前言:

1.字符集和排序规则

说实话我对这两个概念比较模糊，其实可以简单的理解:

字符集(character set)：定义了字符以及字符的编码。

排序规则(collation)：定义了字符的比较规则。

一个字符集对应至少一种排序规则(一般是1对多)

两个不同的字符集不能有相同的排序规则

每个字符集都有默认的排序规则

2.MySQL字符集相关参数:

character_set_client 客户端来源数据使用的字符集，默认值:utf8mb4

character_set_connection 连接层字符集，默认值:utf8mb4

character_set_database 当前选中数据库的默认字符集，默认值:utf8mb4

character_set_results 查询结果字符集，默认值:utf8mb4

character_set_server 默认的内部操作字符集，默认值:utf8mb4

character_set_system 系统元数据(字段名等)字符集，默认值:utf8

这些参数真的多，不过还好MySQL 8.0开始，这些都是默认utf8mb4了(character_set_system除外)。

3.MySQL支持的字符集、排序规则:

MySQl支持的字符集

mysql> SHOW CHARACTER SET;

+----------+---------------------------------+---------------------+--------+

| Charset | Description | Default collation | Maxlen |

+----------+---------------------------------+---------------------+--------+

| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |

| ascii | US ASCII | ascii_general_ci | 1 |

| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

| binary | Binary pseudo charset | binary | 1 |

| cp1250 | Windows Central European | cp1250_general_ci | 1 |

| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

| cp1256 | Windows Arabic | cp1256_general_ci | 1 |

| cp1257 | Windows Baltic | cp1257_general_ci | 1 |

| cp850 | DOS West European | cp850_general_ci | 1 |

| cp852 | DOS Central European | cp852_general_ci | 1 |

| cp866 | DOS Russian | cp866_general_ci | 1 |

| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |

| dec8 | DEC West European | dec8_swedish_ci | 1 |

| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |

| euckr | EUC-KR Korean | euckr_korean_ci | 2 |

| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |

| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |

| greek | ISO 8859-7 Greek | greek_general_ci | 1 |

| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |

| hp8 | HP West European | hp8_english_ci | 1 |

| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |

| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |

| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |

| latin1 | cp1252 West European | latin1_swedish_ci | 1 |

| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |

| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |

| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |

| macce | Mac Central European | macce_general_ci | 1 |

| macroman | Mac West European | macroman_general_ci | 1 |

| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |

| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |

| tis620 | TIS620 Thai | tis620_thai_ci | 1 |

| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |

| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |

| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |

| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |

| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |

+----------+---------------------------------+---------------------+--------+

41 rows in set (0.00 sec)

MySQL utf8mb4支持的排序规则:

mysql> SHOW COLLATION WHERE Charset = 'utf8mb4';

+----------------------------+---------+-----+---------+----------+---------+--------------

| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute

+----------------------------+---------+-----+---------+----------+---------+--------------

| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD

| utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD

| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD

| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD

| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE

| utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 | PAD SPACE

| utf8mb4_cs_0900_ai_ci | utf8mb4 | 266 | | Yes | 0 | NO PAD

| utf8mb4_cs_0900_as_cs | utf8mb4 | 289 | | Yes | 0 | NO PAD

| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | PAD SPACE

| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | PAD SPACE

| utf8mb4_da_0900_ai_ci | utf8mb4 | 267 | | Yes | 0 | NO PAD

| utf8mb4_da_0900_as_cs | utf8mb4 | 290 | | Yes | 0 | NO PAD

| utf8mb4_de_pb_0900_ai_ci | utf8mb4 | 256 | | Yes | 0 | NO PAD

| utf8mb4_de_pb_0900_as_cs | utf8mb4 | 279 | | Yes | 0 | NO PAD

| utf8mb4_eo_0900_ai_ci | utf8mb4 | 273 | | Yes | 0 | NO PAD

| utf8mb4_eo_0900_as_cs | utf8mb4 | 296 | | Yes | 0 | NO PAD

| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | PAD SPACE

| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | PAD SPACE

| utf8mb4_es_0900_ai_ci | utf8mb4 | 263 | | Yes | 0 | NO PAD

| utf8mb4_es_0900_as_cs | utf8mb4 | 286 | | Yes | 0 | NO PAD

| utf8mb4_es_trad_0900_ai_ci | utf8mb4 | 270 | | Yes | 0 | NO PAD

| utf8mb4_es_trad_0900_as_cs | utf8mb4 | 293 | | Yes | 0 | NO PAD

| utf8mb4_et_0900_ai_ci | utf8mb4 | 262 | | Yes | 0 | NO PAD

| utf8mb4_et_0900_as_cs | utf8mb4 | 285 | | Yes | 0 | NO PAD

| utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | PAD SPACE

| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | PAD SPACE

| utf8mb4_hr_0900_ai_ci | utf8mb4 | 275 | | Yes | 0 | NO PAD

| utf8mb4_hr_0900_as_cs | utf8mb4 | 298 | | Yes | 0 | NO PAD

| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | PAD SPACE

| utf8mb4_hu_0900_ai_ci | utf8mb4 | 274 | | Yes | 0 | NO PAD

| utf8mb4_hu_0900_as_cs | utf8mb4 | 297 | | Yes | 0 | NO PAD

| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | PAD SPACE

| utf8mb4_is_0900_ai_ci | utf8mb4 | 257 | | Yes | 0 | NO PAD

| utf8mb4_is_0900_as_cs | utf8mb4 | 280 | | Yes | 0 | NO PAD

| utf8mb4_ja_0900_as_cs | utf8mb4 | 303 | | Yes | 0 | NO PAD

| utf8mb4_ja_0900_as_cs_ks | utf8mb4 | 304 | | Yes | 24 | NO PAD

| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | PAD SPACE

| utf8mb4_la_0900_ai_ci | utf8mb4 | 271 | | Yes | 0 | NO PAD

| utf8mb4_la_0900_as_cs | utf8mb4 | 294 | | Yes | 0 | NO PAD

| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | PAD SPACE

| utf8mb4_lt_0900_ai_ci | utf8mb4 | 268 | | Yes | 0 | NO PAD

| utf8mb4_lt_0900_as_cs | utf8mb4 | 291 | | Yes | 0 | NO PAD

| utf8mb4_lv_0900_ai_ci | utf8mb4 | 258 | | Yes | 0 | NO PAD

| utf8mb4_lv_0900_as_cs | utf8mb4 | 281 | | Yes | 0 | NO PAD

| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | PAD SPACE

| utf8mb4_pl_0900_ai_ci | utf8mb4 | 261 | | Yes | 0 | NO PAD

| utf8mb4_pl_0900_as_cs | utf8mb4 | 284 | | Yes | 0 | NO PAD

| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | PAD SPACE

| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | PAD SPACE

| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | PAD SPACE

| utf8mb4_ro_0900_ai_ci | utf8mb4 | 259 | | Yes | 0 | NO PAD

| utf8mb4_ro_0900_as_cs | utf8mb4 | 282 | | Yes | 0 | NO PAD

| utf8mb4_ru_0900_ai_ci | utf8mb4 | 306 | | Yes | 0 | NO PAD

| utf8mb4_ru_0900_as_cs | utf8mb4 | 307 | | Yes | 0 | NO PAD

| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | PAD SPACE

| utf8mb4_sk_0900_ai_ci | utf8mb4 | 269 | | Yes | 0 | NO PAD

| utf8mb4_sk_0900_as_cs | utf8mb4 | 292 | | Yes | 0 | NO PAD

| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | PAD SPACE

| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | PAD SPACE

| utf8mb4_sl_0900_ai_ci | utf8mb4 | 260 | | Yes | 0 | NO PAD

| utf8mb4_sl_0900_as_cs | utf8mb4 | 283 | | Yes | 0 | NO PAD

| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | PAD SPACE

| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | PAD SPACE

| utf8mb4_sv_0900_ai_ci | utf8mb4 | 264 | | Yes | 0 | NO PAD

| utf8mb4_sv_0900_as_cs | utf8mb4 | 287 | | Yes | 0 | NO PAD

| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | PAD SPACE

| utf8mb4_tr_0900_ai_ci | utf8mb4 | 265 | | Yes | 0 | NO PAD

| utf8mb4_tr_0900_as_cs | utf8mb4 | 288 | | Yes | 0 | NO PAD

| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 | PAD SPACE

| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | PAD SPACE

| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | PAD SPACE

| utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 | PAD SPACE

| utf8mb4_vi_0900_ai_ci | utf8mb4 | 277 | | Yes | 0 | NO PAD

| utf8mb4_vi_0900_as_cs | utf8mb4 | 300 | | Yes | 0 | NO PAD

| utf8mb4_zh_0900_as_cs | utf8mb4 | 308 | | Yes | 0 | NO PAD

+----------------------------+---------+-----+---------+----------+---------+--------------

75 rows in set (0.00 sec)

一.常用的字符集

1.utf8和utf8mb4

MySQL8.0开始，默认字符集修改为了utf8mb4。

MySQL在5.5.3之后增加了utf8mb4的编码，mb4即4-Byte UTF-8 Unicode Encoding，专门用来兼容四字节的unicode。utf8mb4为utf8的超集并兼容utf8，比utf8能表示更多的字符。

如果需要存储4 字节的字符，utf8字符集就会出现问题，例如emoji表情，所以建议选择utf8mb4而非utf8。

当然utf8替换为utf8mb4占的空间更多，如果utf8绝对够用，也可以依旧使用utf8

2.gbk/gb2312/gb18030

GB2312 :中国国家标准简体中文字符集,共收录 6763 个汉字，其中一级汉字 3755 个，二级汉字 3008 个；同时收录了包括拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母在内的 682 个字符。

GBK :共收入21886个汉字和图形符号,GBK 向下与 GB 2312 完全兼容，向上支持 ISO 10646 国际标准，在前者向后者过渡过程中起到的承上启下的作用。

GB18030 :共收录汉字70244个,GB18030 与 GB 312 和 GBK 兼容

大多数情况下，GBK就够用了，如果生僻字比较多，可以改为GB18030

关于utf8和gbk字符集如何选择:

UTF-8：一个汉字 = 3个字节，英文是1个字节

GBK： 一个汉字 = 2个字节，英文是1个字节

这样看，如果是中文的的系统，选择GBK可以比UTF8更节省磁盘空间，性能也就更好。

测试记录:

-- 测试语句

create table t_utf8(en varchar(100),cn varchar(100)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

insert into t_utf8 values ('a','张');

insert into t_utf8 values ('ab','张三');

create table t_gbk(en varchar(100),cn varchar(100)) DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

insert into t_gbk values ('a','张');

insert into t_gbk values ('ab','张三');

select en,cn,char_length(en),char_length(cn),length(en),length(cn) from t_utf8;

select en,cn,char_length(en),char_length(cn),length(en),length(cn) from t_gbk;

测试结果:

mysql> create table t_utf8(en varchar(100),cn varchar(100)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Query OK, 0 rows affected (0.03 sec)

mysql> insert into t_utf8 values ('a','张');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_utf8 values ('ab','张三');

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> create table t_gbk(en varchar(100),cn varchar(100)) DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_gbk values ('a','张');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t_gbk values ('ab','张三');

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> select en,cn,char_length(en),char_length(cn),length(en),length(cn) from t_utf8;

+------+--------+-----------------+-----------------+------------+------------+

| en | cn | char_length(en) | char_length(cn) | length(en) | length(cn) |

+------+--------+-----------------+-----------------+------------+------------+

| a | 张 | 1 | 1 | 1 | 3 |

| ab | 张三 | 2 | 2 | 2 | 6 |

+------+--------+-----------------+-----------------+------------+------------+

2 rows in set (0.00 sec)

mysql> select en,cn,char_length(en),char_length(cn),length(en),length(cn) from t_gbk;

+------+--------+-----------------+-----------------+------------+------------+

| en | cn | char_length(en) | char_length(cn) | length(en) | length(cn) |

+------+--------+-----------------+-----------------+------------+------------+

| a | 张 | 1 | 1 | 1 | 2 |

| ab | 张三 | 2 | 2 | 2 | 4 |

+------+--------+-----------------+-----------------+------------+------------+

2 rows in set (0.00 sec)

mysql>

3.latin

光latin字符集就有latin1、latin2、latin5、latin7四种，当然这4种字符集在中国应用很少，我在这里也就不一一讲解了。

MySQL一直到8.0版本才将字符集由latin1改为utf8mb4。

之前版本如果创建数据库和表的时候，不指定字符集，默认都是latin1字符集。

latin1存中文很容易出现乱码问题，给系统带来灾难性的问题，如果是MySQL 8.0之前版本，创建数据库和表，一定要指定字符集。

测试记录:

-- 创建一个latin字符集的表

create table t_latin(en varchar(100),cn varchar(100)) DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;

-- insert的时候直接报错

insert into t_latin values ('a','程');

insert into t_latin values ('ab','张三');

-- set names latin1 此时可以将中文数据录入表

set names latin1;

insert into t_latin values ('a','张');

insert into t_latin values ('ab','张三');

-- 查询也没有问题，正常显示

select * from t_latin;

-- 退出重登陆后，发现中文乱码了

exit;

mysql -uroot -p

use test;

select * from t_latin;

测试结果:

mysql>

mysql> -- 创建一个latin字符集的表

mysql> create table t_latin(en varchar(100),cn varchar(100)) DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;

Query OK, 0 rows affected (0.02 sec)

mysql> -- insert的时候直接报错

mysql> insert into t_latin values ('a','程');

ERROR 1366 (HY000): Incorrect string value: '\xE7\xA8\x8B' for column 'cn' at row 1

mysql> insert into t_latin values ('ab','张三');

ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'cn' at row 1

mysql>

mysql>

mysql> -- set names latin1 此时可以将中文数据录入表

mysql> set names latin1;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_latin values ('a','张');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_latin values ('ab','张三');

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> -- 查询也没有问题，正常显示

mysql> select * from t_latin;

+------+--------+

| en | cn |

+------+--------+

| a | 张 |

| ab | 张三 |

+------+--------+

2 rows in set (0.00 sec)

mysql>

mysql>

mysql> -- 退出重登陆后，发现中文乱码了

mysql> exit;

Bye

C:\>

C:\>mysql -uroot -p

Enter password: ******

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test

Database changed

mysql> select * from t_latin;

+------+---------------+

| en | cn |

+------+---------------+

| a | å¼ |

| ab | å¼ ä¸‰ |

+------+---------------+

2 rows in set (0.00 sec)

mysql>

解决latin1字符集显示显示乱码的两种方法

mysql>

mysql> select en,cn from t_latin;

+------+---------------+

| en | cn |

+------+---------------+

| a | å¼ |

| ab | å¼ ä¸‰ |

+------+---------------+

2 rows in set (0.00 sec)

mysql> select en,convert(unhex(hex(convert(cn using latin1))) using utf8) as cn from t_latin;

+------+--------+

| en | cn |

+------+--------+

| a | 张 |

| ab | 张三 |

+------+--------+

2 rows in set, 1 warning (0.00 sec)

mysql> set names latin;

ERROR 1115 (42000): Unknown character set: 'latin'

mysql>

mysql> set names latin1;

Query OK, 0 rows affected (0.00 sec)

mysql> select en,cn from t_latin;

+------+--------+

| en | cn |

+------+--------+

| a | 张 |

| ab | 张三 |

+------+--------+

2 rows in set (0.00 sec)

二.常用MySQL的排序规则介绍

utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。

utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会造成不良后果

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

utf8_general_ci校对速度快，但准确度稍差。

utf8_unicode_ci准确度高，但校对速度稍慢。

mysql> show collation where charset = 'utf8';

+--------------------------+---------+-----+---------+----------+---------+---------------+

| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |

+--------------------------+---------+-----+---------+----------+---------+---------------+

| utf8_bin | utf8 | 83 | | Yes | 1 | PAD SPACE |

| utf8_croatian_ci | utf8 | 213 | | Yes | 8 | PAD SPACE |

| utf8_czech_ci | utf8 | 202 | | Yes | 8 | PAD SPACE |

| utf8_danish_ci | utf8 | 203 | | Yes | 8 | PAD SPACE |

| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 | PAD SPACE |

| utf8_estonian_ci | utf8 | 198 | | Yes | 8 | PAD SPACE |

| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | PAD SPACE |

| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 | PAD SPACE |

| utf8_german2_ci | utf8 | 212 | | Yes | 8 | PAD SPACE |

| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 | PAD SPACE |

| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 | PAD SPACE |

| utf8_latvian_ci | utf8 | 194 | | Yes | 8 | PAD SPACE |

| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 | PAD SPACE |

| utf8_persian_ci | utf8 | 208 | | Yes | 8 | PAD SPACE |

| utf8_polish_ci | utf8 | 197 | | Yes | 8 | PAD SPACE |

| utf8_romanian_ci | utf8 | 195 | | Yes | 8 | PAD SPACE |

| utf8_roman_ci | utf8 | 207 | | Yes | 8 | PAD SPACE |

| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 | PAD SPACE |

| utf8_slovak_ci | utf8 | 205 | | Yes | 8 | PAD SPACE |

| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 | PAD SPACE |

| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 | PAD SPACE |

| utf8_spanish_ci | utf8 | 199 | | Yes | 8 | PAD SPACE |

| utf8_swedish_ci | utf8 | 200 | | Yes | 8 | PAD SPACE |

| utf8_tolower_ci | utf8 | 76 | | Yes | 1 | PAD SPACE |

| utf8_turkish_ci | utf8 | 201 | | Yes | 8 | PAD SPACE |

| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 | PAD SPACE |

| utf8_unicode_ci | utf8 | 192 | | Yes | 8 | PAD SPACE |

| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 | PAD SPACE |

+--------------------------+---------+-----+---------+----------+---------+---------------+

28 rows in set (0.01 sec)

mysql> show collation where charset = 'utf8mb4';

+----------------------------+---------+-----+---------+----------+---------+---------------+

| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |

+----------------------------+---------+-----+---------+----------+---------+---------------+

| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |

| utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD |

| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD |

| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD |

| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |

| utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 | PAD SPACE |

| utf8mb4_cs_0900_ai_ci | utf8mb4 | 266 | | Yes | 0 | NO PAD |

| utf8mb4_cs_0900_as_cs | utf8mb4 | 289 | | Yes | 0 | NO PAD |

| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | PAD SPACE |

| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | PAD SPACE |

| utf8mb4_da_0900_ai_ci | utf8mb4 | 267 | | Yes | 0 | NO PAD |

| utf8mb4_da_0900_as_cs | utf8mb4 | 290 | | Yes | 0 | NO PAD |

| utf8mb4_de_pb_0900_ai_ci | utf8mb4 | 256 | | Yes | 0 | NO PAD |

| utf8mb4_de_pb_0900_as_cs | utf8mb4 | 279 | | Yes | 0 | NO PAD |

| utf8mb4_eo_0900_ai_ci | utf8mb4 | 273 | | Yes | 0 | NO PAD |

| utf8mb4_eo_0900_as_cs | utf8mb4 | 296 | | Yes | 0 | NO PAD |

| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | PAD SPACE |

| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | PAD SPACE |

| utf8mb4_es_0900_ai_ci | utf8mb4 | 263 | | Yes | 0 | NO PAD |

| utf8mb4_es_0900_as_cs | utf8mb4 | 286 | | Yes | 0 | NO PAD |

| utf8mb4_es_trad_0900_ai_ci | utf8mb4 | 270 | | Yes | 0 | NO PAD |

| utf8mb4_es_trad_0900_as_cs | utf8mb4 | 293 | | Yes | 0 | NO PAD |

| utf8mb4_et_0900_ai_ci | utf8mb4 | 262 | | Yes | 0 | NO PAD |

| utf8mb4_et_0900_as_cs | utf8mb4 | 285 | | Yes | 0 | NO PAD |

| utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | PAD SPACE |

| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | PAD SPACE |

| utf8mb4_hr_0900_ai_ci | utf8mb4 | 275 | | Yes | 0 | NO PAD |

| utf8mb4_hr_0900_as_cs | utf8mb4 | 298 | | Yes | 0 | NO PAD |

| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | PAD SPACE |

| utf8mb4_hu_0900_ai_ci | utf8mb4 | 274 | | Yes | 0 | NO PAD |

| utf8mb4_hu_0900_as_cs | utf8mb4 | 297 | | Yes | 0 | NO PAD |

| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | PAD SPACE |

| utf8mb4_is_0900_ai_ci | utf8mb4 | 257 | | Yes | 0 | NO PAD |

| utf8mb4_is_0900_as_cs | utf8mb4 | 280 | | Yes | 0 | NO PAD |

| utf8mb4_ja_0900_as_cs | utf8mb4 | 303 | | Yes | 0 | NO PAD |

| utf8mb4_ja_0900_as_cs_ks | utf8mb4 | 304 | | Yes | 24 | NO PAD |

| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | PAD SPACE |

| utf8mb4_la_0900_ai_ci | utf8mb4 | 271 | | Yes | 0 | NO PAD |

| utf8mb4_la_0900_as_cs | utf8mb4 | 294 | | Yes | 0 | NO PAD |

| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | PAD SPACE |

| utf8mb4_lt_0900_ai_ci | utf8mb4 | 268 | | Yes | 0 | NO PAD |

| utf8mb4_lt_0900_as_cs | utf8mb4 | 291 | | Yes | 0 | NO PAD |

| utf8mb4_lv_0900_ai_ci | utf8mb4 | 258 | | Yes | 0 | NO PAD |

| utf8mb4_lv_0900_as_cs | utf8mb4 | 281 | | Yes | 0 | NO PAD |

| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | PAD SPACE |

| utf8mb4_pl_0900_ai_ci | utf8mb4 | 261 | | Yes | 0 | NO PAD |

| utf8mb4_pl_0900_as_cs | utf8mb4 | 284 | | Yes | 0 | NO PAD |

| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | PAD SPACE |

| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | PAD SPACE |

| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | PAD SPACE |

| utf8mb4_ro_0900_ai_ci | utf8mb4 | 259 | | Yes | 0 | NO PAD |

| utf8mb4_ro_0900_as_cs | utf8mb4 | 282 | | Yes | 0 | NO PAD |

| utf8mb4_ru_0900_ai_ci | utf8mb4 | 306 | | Yes | 0 | NO PAD |

| utf8mb4_ru_0900_as_cs | utf8mb4 | 307 | | Yes | 0 | NO PAD |

| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | PAD SPACE |

| utf8mb4_sk_0900_ai_ci | utf8mb4 | 269 | | Yes | 0 | NO PAD |

| utf8mb4_sk_0900_as_cs | utf8mb4 | 292 | | Yes | 0 | NO PAD |

| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | PAD SPACE |

| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | PAD SPACE |

| utf8mb4_sl_0900_ai_ci | utf8mb4 | 260 | | Yes | 0 | NO PAD |

| utf8mb4_sl_0900_as_cs | utf8mb4 | 283 | | Yes | 0 | NO PAD |

| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | PAD SPACE |

| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | PAD SPACE |

| utf8mb4_sv_0900_ai_ci | utf8mb4 | 264 | | Yes | 0 | NO PAD |

| utf8mb4_sv_0900_as_cs | utf8mb4 | 287 | | Yes | 0 | NO PAD |

| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | PAD SPACE |

| utf8mb4_tr_0900_ai_ci | utf8mb4 | 265 | | Yes | 0 | NO PAD |

| utf8mb4_tr_0900_as_cs | utf8mb4 | 288 | | Yes | 0 | NO PAD |

| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 | PAD SPACE |

| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | PAD SPACE |

| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | PAD SPACE |

| utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 | PAD SPACE |

| utf8mb4_vi_0900_ai_ci | utf8mb4 | 277 | | Yes | 0 | NO PAD |

| utf8mb4_vi_0900_as_cs | utf8mb4 | 300 | | Yes | 0 | NO PAD |

| utf8mb4_zh_0900_as_cs | utf8mb4 | 308 | | Yes | 0 | NO PAD |

+----------------------------+---------+-----+---------+----------+---------+---------------+

75 rows in set (0.01 sec)

mysql> show collation where charset = 'gbk';

+----------------+---------+----+---------+----------+---------+---------------+

| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |

+----------------+---------+----+---------+----------+---------+---------------+

| gbk_bin | gbk | 87 | | Yes | 1 | PAD SPACE |

| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 | PAD SPACE |

+----------------+---------+----+---------+----------+---------+---------------+

2 rows in set (0.01 sec)

如果字符集选择utf8，则排序规则选择默认的utf8_general_ci

如果字符集选择utf8mb4，则排序规则选择默认的utf8mb4_0900_ai_ci

如果字符集选择gbk，则排序规则选择默认的gbk_chinese_ci

排序规则大小写敏感测试:

create table t_utf8mb4(en1 varchar(100),en2 varchar(100)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

alter table t_utf8mb4 modify column en2 varchar(100) charset utf8mb4 collate utf8mb4_0900_as_cs ;

insert into t_utf8mb4 values ('a','a');

insert into t_utf8mb4 values ('A','A');

insert into t_utf8mb4 values ('a','A');

insert into t_utf8mb4 values ('A','a');

select * from t_utf8mb4 where en1='a';

select * from t_utf8mb4 where en1='A';

select * from t_utf8mb4 where en2='a';

select * from t_utf8mb4 where en2='A';

测试结果:

mysql>

mysql> create table t_utf8mb4(en1 varchar(100),en2 varchar(100)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Query OK, 0 rows affected (0.03 sec)

mysql> alter table t_utf8mb4 modify column en2 varchar(100) charset utf8mb4 collate utf8mb4_0900_as_cs ;

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>

mysql>

mysql> insert into t_utf8mb4 values ('a','a');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_utf8mb4 values ('A','A');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t_utf8mb4 values ('a','A');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_utf8mb4 values ('A','a');

Query OK, 1 row affected (0.01 sec)

mysql>

mysql>

mysql> select * from t_utf8mb4;

+------+------+

| en1 | en2 |

+------+------+

| a | a |

| A | A |

| a | A |

| A | a |

+------+------+

4 rows in set (0.00 sec)

mysql>

-- 查询不区分大小写的排序规则列，果然全部输出

mysql> select * from t_utf8mb4 where en1='a';

+------+------+

| en1 | en2 |

+------+------+

| a | a |

| A | A |

| a | A |

| A | a |

+------+------+

4 rows in set (0.00 sec)

mysql> select * from t_utf8mb4 where en1='A';

+------+------+

| en1 | en2 |

+------+------+

| a | a |

| A | A |

| a | A |

| A | a |

+------+------+

4 rows in set (0.00 sec)

-- 查询区分大小写的排序规则列，值输出全部是小写或全部是大写的

mysql> select * from t_utf8mb4 where en2='a';

+------+------+

| en1 | en2 |

+------+------+

| a | a |

| A | a |

+------+------+

2 rows in set (0.00 sec)

mysql> select * from t_utf8mb4 where en2='A';

+------+------+

| en1 | en2 |

+------+------+

| A | A |

| a | A |

+------+------+

2 rows in set (0.00 sec)

三.MySQL字符集相关参数

3.1 字符集参数介绍

character_set_client 客户端来源数据使用的字符集，默认值:utf8mb4

character_set_connection 连接层字符集，默认值:utf8mb4

character_set_database 当前选中数据库的默认字符集，默认值:utf8mb4

character_set_results 查询结果字符集，默认值:utf8mb4

character_set_server 默认的内部操作字符集，默认值:utf8mb4

character_set_system 系统元数据(字段名等)字符集，默认值:utf8

也许大家会有疑问，弄一个字符集不好吗，为什么有这6个字符集

我这边来讲讲这6个字符集的使用场景

因为服务器端和客户端，字符集会存在一定的差异。

服务器端总是假设客户端是按照 character_set_client设置的字符来传输数据和SQL语句的。

当服务器收到客户端的SQL语句时，它先将其转换为字符集character_set_connection。它还是用这个设置来决定如何将数据转换成字符串。

当服务器端返回数据或错误信息给客户端时，它会将其转换成character_set_result。

这样就保证了，服务器端和客户端在中转数据的时候，不会出现乱码。

至于character_set_server和character_set_database这两个，一个是系统默认的字符集，一个是创建数据库默认的字符集。

3.2字符集参数修改

备注:character_set_system参数是只读参数，不能修改，其余参数可以通过set命令进行修改

上述5个参数需要保持一致，这样才能避免出现乱码，如果需要修改，可以参考如下:

mysql> set character_set_client = utf8;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set character_set_connection = utf8;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

mysql> set character_set_database = utf8;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql>

mysql> set character_set_results = utf8;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set character_set_server = utf8;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

mysql>

mysql> show variables like 'character_set%';

+--------------------------+----------------------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | E:\mysql\mysql-8.0.19-winx64\share\charsets\ |

+--------------------------+----------------------------------------------+

8 rows in set, 1 warning (0.00 sec)

mysql>

-- 修改

mysql> set character_set_system = utf8;

ERROR 1238 (HY000): Variable 'character_set_system' is a read only variable

四.数据库、表、列的字符集

创建数据库时，如不指定字符集，将根据服务器上的character_set_server设置来设定该数据库的默认字符集。

创建表时，如不指定字符集，将根据数据库的字符集设置指定这个表的字符集。

创建列时，如不指定字符集，将根据表的设置指定列的字符集。

我们来演练一个 数据库、表、列的字符集不相同的例子。

-- 数据库字符集是 latin1

mysql> create database test1 charset latin1;

Query OK, 1 row affected (0.03 sec)

mysql> use test1;

Database changed

-- 表的字符集是 utf8mb4 而列的字符集有 utf8也有gbk

mysql> create table t1(id int,name1 varchar(100) charset gbk,name2 varchar(100) charset utf8) default charset = utf8mb4;

Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> insert into t1 values(1,'张三','张三');

Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;

+------+--------+--------+

| id | name1 | name2 |

+------+--------+--------+

| 1 | 张三 | 张三 |

+------+--------+--------+

1 row in set (0.00 sec)

这样看，数据库、表、列可以有不同的字符集和排序规则。

那么我们如何选择数据库、表、列的字符集和排序规则呢?

最好是先为服务器(数据库)选择一个合理的字符集，然后表和列都遵循这个字符集。

当有特殊需求的时候，可以显式的指定表或列的字符集，让表和列选择自己合适的字符集。

五.字符集和排序规则如何影响查询

1.order by collate时 可能导致无法使用索引进行排序

测试数据:

create table t1(name1 varchar(100) charset utf8mb4 ,name2 varchar(100) charset utf8 collate utf8_bin,name3 varchar(100) charset gbk);

insert into t1 values('张三','张三','张三');

insert into t1 values('李四','李四','李四');

-- 创建索引

create index i_t1_02 on t1(name2);

explain select name2 from t1 order by name2;

explain select name2 from t1 order by name2 collate utf8_general_ci;

执行记录:

mysql> create table t1(name1 varchar(100) charset utf8mb4 ,name2 varchar(100) charset utf8 collate utf8_bin,name3 varchar(100) charset gbk);

Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> insert into t1 values('张三','张三','张三');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values('李四','李四','李四');

Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;

+--------+--------+--------+

| name1 | name2 | name3 |

+--------+--------+--------+

| 张三 | 张三 | 张三 |

| 李四 | 李四 | 李四 |

+--------+--------+--------+

2 rows in set (0.00 sec)

mysql> create index i_t1_02 on t1(name2);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select name2 from t1 order by name2;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

| 1 | SIMPLE | t1 | NULL | index | NULL | i_t1_02 | 303 | NULL | 2 | 100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql>

mysql> explain select name2 from t1 order by name2 collate utf8_general_ci;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+

| 1 | SIMPLE | t1 | NULL | index | NULL | i_t1_02 | 303 | NULL | 2 | 100.00 | Using index; Using filesort |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+

1 row in set, 1 warning (0.00 sec)

mysql>

可以看到 order by name2 collate utf8_general_ci 由于排序规则并不是创建时指定的utf8_bin，最后使用了文件排序 Using filesort