任何计算机存储数据,都需要字符集,因为计算机存储的数据其实都是二进制编码,将一个个字符,映射到对应的二进制编码的这个映射就是字符编码(字符集)。这些字符如何排序呢?决定字符排序的规则就是排序规则。
查看内置字符集与比较规则
通过show charset;
命令,可以查看所有的字符集。 以下仅展示了我们常用的字符集:
+----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | +----------+---------------------------------+---------------------+--------+
ascii
:共收录128个字符,包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符,所以可以使用1个字节来进行编码latin1
:共收录256个字符,是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母),也可以使用1个字节来进行编码。gb2312
: 收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个,其他文字符号682个,兼容ASCII字符集。这是一个变长字符集,如果该字符在ascii
字符集中,则采用1字节编码,否则采用两字节。gbk
: GBK是在gb2312
基础上扩容后的标准。收录了所有的中文字符。同样的,这是一个变长字符集,如果该字符在ascii
字符集中,则采用1字节编码,否则采用两字节。utf8
和utf8mb4
: 收录地球上能想到的所有字符,而且还在不断扩充。这种字符集兼容ASCII字符集,采用变长编码方式,编码一个字符需要使用1~4个字节。MySQL
为了节省空间,其中的utf8
是标准 UTF8 阉割后的,只有1~3字节编码的字符集,基本包含了所有常用的字符。如果还要使用 enoji 表情,那么需要使用utf8mb4
,这个是完整的 UTF8 字符集。utf16
: 不同于utf8
,utf16
用两个字节或者四个字节编码字符,可以理解为utf8
的不节省空间的一种形式utf32
: 固定用四个字节编码字符,可以理解为utf8
的不节省空间的一种形式
通过查看information_schema.character_sets
表,也可以看到所有的字符集:
mysql> select * from information_schema.character_sets where character_set_name = "utf8"; +--------------------+----------------------+---------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+---------------+--------+ | utf8 | utf8_general_ci | UTF-8 Unicode | 3 | +--------------------+----------------------+---------------+--------+ 1 row in set (0.06 sec)
通过show collation;
命令,可以查看所有的字符集,我们这里来查看utf8mb4
的排序规则:
mysql> show collation like 'utf8mb4%'; +------------------------+---------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +------------------------+---------+-----+---------+----------+---------+ | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | | utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | | utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | | utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | | utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | | utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | | utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | | utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | | utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 | | utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | | utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | | utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | | utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | | utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | | utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | | utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | | utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | | utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | | utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | | utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 | | utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | | utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 | +------------------------+---------+-----+---------+----------+---------+ 26 rows in set (0.13 sec)
同样的,通过查询information_schema.collations
也可以:
mysql> select * from information_schema.collations where character_set_name = "utf8mb4"; +------------------------+--------------------+-----+------------+-------------+---------+ | COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | +------------------------+--------------------+-----+------------+-------------+---------+ | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | | utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | | utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | | utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | | utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | | utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | | utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | | utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | | utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 | | utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | | utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | | utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | | utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | | utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | | utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | | utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | | utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | | utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | | utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | | utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 | | utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | | utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 | +------------------------+--------------------+-----+------------+-------------+---------+ 26 rows in set (0.11 sec)
- 每个字符集都有一个默认的排序规则:IS_DEFAULT 为 Yes。
- 比较规则名称以与其关联的字符集的名称开头,可以用通过这个开头查询所有的字符集,也可以查询
information_schema.collations
精确指定字符集 - 字符集后面跟着的是语言编码,因为
utf8mb4
包含了所有字符,不同国家的文字语言排序肯定不一样。 - 最后末尾的
ci
代表case insensitive
,大小写不敏感,所有可能的后缀如下所示:
- ai: accent insensitive 不区分重音
- as: accent sensitive 区分重音
- ci: case insensitive 不区分大小写
- cs: case sensitive 区分大小写
- bin: binary 以二进制方式比较
应用字符集与比较规则
字符集与比较规则配置有四个级别:
- MySQL实例级别
- 库级别
- 表级别
- 字段级别 指定的级别粒度越小,则以粒度越小的字符集还有比较规则优先。例如指定MySQL实例级别字符集是
utf8mb4
,指定某个表字符集是latin1
,那么这个表的所有字段如果不指定的话,编码就是latin1
由于字符集和比较规则是互相有联系的,如果我们只修改了字符集,比较规则也会跟着变化,如果只修改了比较规则,字符集也会跟着变化,具体规则如下:
- 只修改字符集,则比较规则将变为修改后的字符集默认的比较规则。
- 只修改比较规则,则字符集将变为修改后的比较规则对应的字符集。
实例级别
通过两个系统变量来指定实例级别的字符集与排序规则。
配置文件:
[server] character_set_server=utf8mb4 collation_server=utf8mb4_general_ci
启动之后,可以查看并修改这两个变量。
mysql> show variables like 'character_set_server'; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | character_set_server | utf8mb4 | +----------------------+---------+ 1 row in set (0.06 sec) mysql> show variables like 'collation_server'; +------------------+--------------------+ | Variable_name | Value | +------------------+--------------------+ | collation_server | utf8mb4_general_ci | +------------------+--------------------+ 1 row in set (0.05 sec) mysql> set character_set_server = 'utf8mb4'; Query OK, 0 rows affected (0.00 sec) mysql> set collation_server = 'utf8mb4_general_ci'; Query OK, 0 rows affected (0.00 sec)