字符集和校验集是对字符串类型的数据的存储方式和比较方式,MySQL 可以使用它们来组织字符串。

字符集

字符集是一套符号和编码的规则,字符串都必须有相应的字符集。
查看数据库支持的字符集:mysql> SHOW CHARACTER SET;
其中,常见的字符集有 gb2312、gbk、gb18030、utf8、utf8mb4 等。
查看数据库当前的字符集:mysql> SHOW [SESSION | GLOBAL] VARIABLES LIKE 'character_set_server';

校验集

校验集是这套符号和编码的校验规则,定义着字符的排序规则,字符串之间比较的规则,用于验证大小写、不同重音等是否一致。
查看数据库支持的校验集:mysql> SHOW COLLATION;
其中,

  • xxx_bin:将字符串中的每一个字符用二进制数据存储,区分大小写
  • xxx_ci:case insensitive,不区分大小写,即大小写不敏感
  • xxx_cs:case sensitive,区分大小写,即大小写敏感

查看数据库当前的校验集:mysql> SHOW [SESSION | GLOBAL] VARIABLES LIKE 'collation_server';

字符集和校验集的设置

字符集的影响

下面是一个查询请求与响应的过程:

1
2
3
Client -> Server -> DB -> Table -> Column -> Result -> +
| |
+ <---------------------------------------------- <-+

在此过程中,只有当所有环节的字符集都一致(或兼容)时,客户端得到的结果才会正常显示。
反之,不管哪个环节的字符集出现差异,就有可能出现乱码。
对于 DBA 来说,一般要关注 Server 和 DB 层面的字符集或者校验集。

操作系统

查看系统当前的字符集:

  • CentOS 6.x:locale
  • CentOS 7.x:localectl status

查看系统支持的字符集:

  • CentOS 6.x:locale -a
  • CentOS 7.x:localectl list-locales

更改系统的字符集:

  • CentOS 6.x:echo 'LANG="en_US.UTF-8"' > /etc/sysconfig/i18n
  • CentOS 7.x:localectl set-locale 'LANG=en_US.UTF-8'

数据库

  • 在配置文件中指定字符集和校验集,实例上所有的库或者表在创建时不再需要明确指定字符集和校验集

    1
    2
    3
    [mysqld]
    character_set_server = utf8
    collation_server = utf8_unicode_ci
  • 也可以在创建库时指定字符集和校验集,其下的表在创建时不再需要明确指定字符集和校验集

    1
    2
    3
    CREATE DATABASE `db_name` 
    DEFAULT CHARACTER SET = utf8
    DEFAULT COLLATE = utf8_unicode_ci
  • 在创建表时也能指定字符集和校验集,甚至可以精确到列

    1
    2
    3
    CREATE TABLE `tb_name` (
    ...
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

客户端

客户端或者应用程序在连接上MySQL后,可以对会话级别的字符集和校验做设置:
SET NAMES utf8 COLLATE utf8_unicode_ci

确认一致性

1
2
3
4
5
6
7
8
9
10
11
mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
+--------------------------+-------+