MySQL中的字符集

我们在开发过程中可能会碰到MySQL字符集的问题,有时候发现存入某个字符,数据库存储不对。有时候发现,存储某个字符正确,但是获取到的又是乱码。这种情况下一个一个去网上搜索,实在是浪费时间。这里总结一下MySQL中字符集的规则以及配置方法,下次遇到问题时,不妨从这里总结的入手。
首先,先了解两个概念。

字符集和校对规则

MySQL包含字符集(character)和校对规则(collation)两个概念。

  • 字符集用来定义MySQL存储字符串的方式
  • 校对规则用来定义比较字符串的方式

字符集和校对规则一般是一对多的关系

校对规则命名约定

以其相关的字符集名开始,通常包括一个语言名,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元,即比较是基于字符编码的值而与language无关)结束。
如常见的utf8mb4字符集,它默认的校对规则utf8mb4_general_ci表示比较字符串是对大小写不敏感。

字符集级别及设置

mysql中字符集在服务端的存储,有4个级别的控制,分别为:服务器级别数据库级别表级别列级别
另外要想正确获取,还要注意客户端与服务器交互层次上的字符集配置。
这里来逐一看一下如何配置以及配置如何生效的吧!

服务服务器字符集和校对规则

  • 在MySQL服务启动的时候确定
  • 查看方法
show variables like 'character_set_server';
  • 配置方法

配置方法有多种
1.在MySQL配置文件中修改,如my.cnf中:

[mysqld]
character-set-server=utf8mb4

2.在启动选项中指定

mysqld --character-set-server=utf8mb4

3.编译时指定

cmake . -DDEFAULT_CHARSET=utf8mb4

数据库字符集和校对规则

  • 在创建数据库的时候指定,也可以在创建完数据库后通过“alter database”命令进行修改。
  • 查看方法
show variables like 'character_set_database';
show variables like 'collation_database';
  • 设置字符集的规则
  1. 如果指定了字符集和校对规则,则使用指定的字符集和校对规则
  2. 如果指定了字符集,没有指定校对规则,则使用指定字符集的默认校对规则
  3. 如果指定了校对规则,但未指定字符集,则字符集使用与该校对规则关联的字符集
  4. 如果没有指定字符集和校对规则,则使用服务器字符集和校对规则作为数据库的字符集和校对规则

表字符集和校对规则

  • 在创建表的时候指定,可以通过"alter table"命令进行修改
  • 查看方法
show create table t1;
  • 设置字符集规则
  1. 如果指定了字符集和校对规则,使用指定的字符集和校对规则
  2. 如果指定了字符集,没有指定校对规则,则使用指定字符集的默认校对规则
  3. 如果指定了校对规则,但未指定字符集,则字符集使用与该校对规则关联的字符集
  4. 如果没有指定字符集和校对规则,则使用数据库字符集和校对规则作为表的字符集和校对规则

列字符集和校对规则

  • 可以在创建表时指定,或者在修改表时调整
  • 如果在创建表的时候没有特别指定字符集和校对规则,则默认使用表的字符集和校对规则

连接字符集和校对规则

上面四种,确定的是数据保存的字符集和校对规则。对于实际的应用来说,还存在客户端和服务器之间交互的字符集和校对规则的设置。

  • 配置方法

1.在连接时配置,且每次连接时都需要配置
使用

set names ***;

这条命令等价于配置了以下三个参数选项
character_set_client、character_set_connection和character_set_results,
分别代表客户端、连接和返回结果的字符集

2.在MySQL配置文件中修改

[mysql]
default-character-set = utf8mb4

请注意,这里的级别控制可能在你的业务程序代码中被手动指定了,所以有时候服务器那边都存储正确了,但是拿不到正确的值,就要检查你的代码喽。

MySQL字符集修改方法

不能通过直接修改数据库或表字符集来设置,因为旧数据不会被更新
正确的步骤应该如下:

步骤

使用mysqldump进行修正

  1. 导出表结构
  2. 手工修改表创建sql中标结构定义的字符集为新的字符集
  3. 确保记录不再更新后,导出所有记录
  4. 打开数据sql,修改set names *
  5. 使用新的字符集创建新的数据库
  6. 创建表,执行表创建sql
  7. 导入数据,执行数据sql

以下是修改的一个例子
MySQL字符集转换.png

参考文档:
[1]唐汉明.深入浅出MySQL数据库开发、优化与管理维护[M].人民邮电出版社:北京,2014:156.