MySQL中存储emoji字符详解以Nodejs环境为例

一、故障现象

1. 报错提示

D:\api\node_modules\mysql\lib\protocol\Parser.js:79
		throw err; // Rethrow non-MySQL errors
Error: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value: '\xF0\x9F\x94\xA5' for column 'alert' at row 1
	at Query.Sequence._packetToError...

2. 报错原因

① 编码位数：
MySQL 的 utf8 编码的一个字符最多 3 个字节，但是一个 emoji 表情为 4 个字节，所以 utf8 不支持存储 emoji 表情。但是 utf8 的超集 utf8mb4 一个字符最多能有4字节，所以能支持 emoji 表情的存储。下面介绍了关于如何修改 mysql 数据库的编码格式变为 utf8mb4 的具体方法。
② utf8与utf8mb4说明：

• UTF- 8：Unicode Transformation Format-8bit，允许含BOM，但通常不含BOM。是用以解决国际上字符的一种多字节编码，它对英文使用8位（即一个字节），中文使用24为（三个字节）来编码。UTF-8包含全世界所有国家需要用到的字符，是国际编码，通用性强。UTF-8编码的文字可以在各国支持UTF8字符集的浏览器上显示。如果是UTF8编码，则在外国人的英文IE上也能显示中文，他们无需下载IE的中文语言支持包。

• UTF8MB4：MySQL在5.5.3之后增加了utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。

二、解决步骤

根据报错原因，我们需要更换字符集即可 utf8 --> utf8mb4，具体步骤如下

1. 修改 MySQL 的配置文件

• Linux 下为 /etc/mysql路径下的 my.cnf 文件
• Windows 下为 mysql/bin/my.ini文件

[mysqld]
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

2. 修改 database/table 和 column 的字符集

进入 MySQL 中，按下述所示进行命令的执行：

① 修改 database 的字符集：

ALTER DATABASE 数据库名 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;

示例：

ALTER DATABASE xxxdb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;

② 指明数据库：
示例：

use xxxdb;

③ 修改 table 的字符集：

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

示例：

ALTER TABLE user_comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

④ 修改 column 的字符集：

ALTER TABLE 表名 CHANGE 字段名 字段名 该字段原来的数据类型 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

示例：

ALTER TABLE user_comments CHANGE content content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

⑤ 上述修改完毕，exit退出mysql

3. 重启mysql

① 停止msql的运行

通过 /etc/init.d/mysql 执行 stop 命令

② 启动mysql

通过 /etc/init.d/mysql 执行 start 命令

三、修改项目数据库连接配置

本文中项目为 Express 项目，代码如下：

var dateUtils = require('./dateUtils.js');
var jpushUtils = require('./jpushUtils.js');
var UUID = require('uuid');
/**
 * 数据库配置
 * @type {exports|module.exports}
 */
var mysql = require('mysql');
var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'avic',
    charset: 'UTF8MB4_BIN'	//若不设置，默认为 UTF8_GENERAL_CI
});

四、效果演示

使用 Navicat 连接数据库发现已经存储上 emoji 字符，字库原因这里是方框代替了，总之是存上了，使用的时候也没问题