背景:
世界之大,什么都有可能发生,尤其是写代码这种事。正在写公司的小程序项目,想让同事用微信登录后测一下修改的功能,哪晓得修复的bug还没测呢,新的BUG的就被同事测出来了,我当时恨不得吃了她。问题是这样的,同事登录小程序授权登录小程序报错,是因为同事修改了她的微信昵称,是带有emoji表情的昵称,该死的昵称入不了库~于是,我决定先解决这个BUG吧,哈哈哈~
问题原因分析:
utf-8编码可能是2个字节,3个字节,4个字节,但是MySQL的utf8编码其实并不是真的utf8编码,MySQL的utf8编码只支持3字节的数据,而移动端的表情数据是4个字节的字符。如果直接采用utf-8编码的数据库中插入表情数据,Java程序中就会报SQL异常,比如:
java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x92\x94’ for column ‘name’ at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)
解决方案:
将数据库的编码,表的编码,字段的编码改成utf8mb4编码。utf8mb4编码是utf8的超集合,兼容utf8。
1、MySQL的版本
utf8mb4 的最低 MySQL 版本支持版本为 5.5.3+,若不是,请升级到较新版本。
2、MySQL驱动
5.1.34 可用,最低不能低于 5.1.13。
3、MySQL配置文件修改
my.cnf一般在etc/mysql/my.cnf位置。找到后请在以下三部分里添加如下内容:
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
我的文件如下:
[mysqld]
port=3306
# 设置mysql的安装目录,如果目录中带空格则必须加引号
basedir=D:\devlope\mysql5.6.22\mysql
# 设置mysql数据库的数据的存放目录,文件夹名必须是data
datadir=D:\devlope\mysql5.6.22\mysql\data
#设置数据库引擎为INNODB
default-storage-engine=INNODB
# 设置mysql服务器编码
#character_set_server=utf8
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
# 设置mysql服务器字符集
#collation-server=utf8_general_ci
[client]
#default-character-set = utf8
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[WinMySQLadmin]
Server=DD:\devlope\mysql5.6.22\mysql\bin\mysqladmin.exe
4、重启数据库,检查变量
SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
但是按照预期的结果应该是下面这样的,
但是必须保证一下几项是utf8mb4.
(*)这里说明一下,虽然我的数据库编码检测有出入,但是亲测后是有效的,能够插入emoji,具体的原因有待考证,希望广大读者各抒己见。
更改数据库编码:
ALTER DATABASE 你的数据库名 CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`;
更改表的编码:
ALTER TABLE `TABLE_NAME` CONVERT TO CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`;
更改表对应字段的编码:
ALTER TABLE `TABLE_NAME`MODIFY COLUMN `COLUMN_NAME` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
5、数据库连接配置
jdbc.url=jdbc:mysql://127.0.0.1:3306/cxbl?useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE
其中,autoReconnect=true 是必须加上的。
数据源连接池的xml配置文件中,需要加上字符集的设置:
<bean parent="parentDataSource" id="DataSource88">
<property name="connectionInitSqls">
<list>
<value>set names utf8mb4</value>
</list>
</property>
</bean>
这样数据库中就能正常添加带emoji的昵称了,库中的显示是用?:
说明:
从 MySQL 5.5.3 开始,MySQL 支持一种 utf8mb4 的字符集,这个字符集能够支持 4 字节的 UTF8 编码的字符。 utf8mb4 字符集能够完美地向下兼容 utf8 字符串。在数据存储方面,当一个普通中文字符存入数据库时仍然占用 3 个字节,在存入一个 Unified Emoji 表情的时候,它会自动占用 4 个字节。所以在输入输出时都不会存在乱码的问题了。
要使用 MySQL 的这个特性,首先需要把 MySQL 升级到 5.5.3 以上的版本。
其次,需要修改数据结构中的字符集为 utf8mb4 ,如 utf8mb4_general_ci 。由于 utf8mb4 是 utf8 的超集,从 utf8 升级到 utf8mb4 不会有任何问题,直接升级即可;如果从别的字符集如 gb2312 或者 gbk 转化而来,一定要先备份数据库。
然后,修改 MySQL 的配置文件 /etc/my.cnf,修改连接默认字符集为 utf8mb4 ,如果是自己写的 PHP 脚本,也可以在连接数据库以后首先执行一句 SQL: SET NAMES utf8mb4;。这时候,PHP 应该就可以正常保存 Emoji 到数据库了。
这种方式可能带来的问题:
存储:在数据表中,对于变长的字段(如VARCHAR2,TEXT),utf8mb4最大可存储的字符可能少于utf8系列的collation;在索引中,对于文本类型的字段,utf8mb4可索引的字符少于utf8系列的collations。如InnoDB的索引最多使用767字节。如果使用utf8mb4,每一个字符都会预留4字节做索引,而utf8则预留3字节。故此前者是191个字符,后者是255个字符。。
性能:由于以上原因,加上字符集大,utf8mb4的性能可能比utf8系列的collations低,可以参考stackoverfolow上的一个测试结果:http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci,差异不是特别大。
运维:如果一个大的环境内,如果其他的数据库都是utf8模式,把其中某个库设置为utf8mb4模式,在后续交接运维可能会造成问题,遗留下坑。
上下游:数据库支持unicode的emoji存储,上下游不一定支持。比如mysql客户端驱动(低版本的jdbc就不行)可能不支持utf8mb4,或者DDL的中间件不支持utf8mb4。web端处理utf8mb4字符展示,这些都有可能影响emoji的存储活着展示。
从上面的信息,从数据库层面如果不是特别看重存储,性能,运维并能解决上下游的问题,数据库是完全可以支持emoji的,但是有个新问题没有解决,emoji在iOS上展示OK,andriod设备如何展示emoji表情?
参考:
https://www.cnblogs.com/xujanus/p/5189358.html
https://www.jianshu.com/p/f7d7609de6b0
个人能力有限,如果本文存在错误,希望大家批评指正。