Mysql数据库插入Emoji，字符集设置utf8mb4

字符集和校对规则

字符集(CHARACTER SET) 为字母和符号的集合。 例如 latin1(MySQL默认)， utf8 和 utf8mb4 等等…不同字符集的默认校对规则和1个字符最大支持的字节长度可能不一样。

校对规则(COLLATE) 为规定字符如何比较的指令。 比如当我们 SQL 语句后面需要根据某个字段 order by 时，就会以这个指定的校对规则为准。

相关命令：查询 MySQL 数据库服务器中的所有可用字符集

 

sql

复制代码

SHOW CHARACTER SET;

utf8 和 utf8mb4

我们常说的 utf8 字符集一个字符可以表示1~4字节，然后我们的中文汉字是一个字占3字节。

但是 MySQL 中的 utf8 其实是阉割后的，一个字符只能表示1~3字节，而 utf8mb4 才是我们真正意义上的 utf8。

Tip: MySQL 中的 utf8 是 utf8mb3 的别名，两者一样。

字符集使用级别优先级

“

表字段 > 表 > 数据库(character_set_database) > 服务器(character_set_server)

”

• 如果指定了字符集 character set 和校对规则 collate，就以指定的为准。
• 如果只指定字符集 character set，则使用此字符集及其默认的校对规则 collate。
• 如果既不指定字符集 character set 也不指定校对规则 collate，就使用数据库默认的。
• 数据库的字符集不指定，那在创建的时候就使用服务器级别的。

MySQL 各字符集变量含义

• character_set_client、character_set_connection以及character_set_results是客户端的设置。

  1. character_set_client ：客户端来源数据使用的字符集（告诉转换器客户端发送的字符集）
  2. character_set_connection ：服务器接收到客户端的数据时转换器会将 character_set_client 转为 character_set_connection
  3. character_set_results ：服务器向客户端返回数据时使用的字符集

• character_set_system、character_set_server以及character_set_database是服务器端的设置。

  1. character_set_system ：系统字符集，用于系统元数据（表名、字段名和函数名等）。默认就是 utf8，没必要进行更改

  2. character_set_server ：服务器级别使用的（默认）内部操作字符集；修改后生效需要重启 MySQL 实例

     该参数修改后，仅对开启高权限账号的实例后来创建的数据库有效，对当前数据库无效。

  3. character_set_database ：当前数据库使用的字符集，没有指定就使用 character_set_server 指定的

说实话，信息太多了，很绕…下面画张图来表示 MySQL 中字符集间的转换过程

这里的客户端，就是我们的 Navicat，DOS 命令行界面或者运行的应用程序。其实就是与我们 MySQL 建立连接的一方。

Navicat 作为客户端 DOS 作为客户端

这里会发现，不同的客户端，character_set_client, character_set_connection 以及 character_set_results 是不一样的。

characterEncoding=utf8

为什么 character_set_server 修改后需要重启才能生效，而在数据库 URL 连接串后面加上 ?characterEncoding=utf8 也行？

Tip: 这里的 utf8 指的是 Java 中的，这才是真正意义的 utf8

characterEncoding=utf8：相当于当前客户端（Java程序）的 character_set_client, character_set_connection 和 character_set_results 字符集指定成了 MySQL 中的 utf8mb4。

“

这里相当于执行了一条命令：set names utf8mb4;

它的作用就是修改客户端的三个字符集设置。等价于同时执行了三条语句。

set character_set_client = utf8mb4;

set character_set_connection = utf8mb4;

set character_set_results = utf8mb4;

”

而这个设置，只针对于客户端与 MySQL 服务器的当前连接会话。 如果不写，默认这三个值以 character_set_server 的值为准。（改了 character_set_server 之后再重启，那就一劳永逸了）

而我们线上的数据库恰恰就是 utf8，那么这三个字符集就统一成了 utf8，emoji表情过来的时候，自然就不认识了。这也就是这次字段列设计成了 utf8mb4 却无法插入emoji表情的主要原因。

口说无凭，直接上最权威的官网解释。

事后复盘

1. 建议数据库实例建立起来的时候，先把该改的基本配置都改了。

   避免以后都已经在线上运行了，因为一些问题如果还要重启 MySQL 服务那可太难受了。

   以 Windows 为例，配置文件默认是安装目录下的 my.ini 文件。

   

2. 不同语言对 character-set-server 这项配置的处理方法不一样，至少 Java 依赖于它（当你在 URL 后不指定 characterEncoding 时）。