这个问题源自苹果客户端emoji表情，插入乱码引发的
2012年曾经在项目上遇到类似的问题，戳这里
但是这两年在上一个公司这个问题也一直争论不下，争论点还是在实例是否需要重启的问题上
这两天正巧业务在测试环境需要修改，抓住这个机会，请应用运维协助测试了下
环境 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
语言：Java
应用服务：Tomcat
驱动：mysql-connector-java-5.1.37
URL：database.jdbcUrl=jdbc:mysql://192.168.1.1:3306/test?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE
数据库：Percona MySQL 5.5.30
+--------------------------+-----------------------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------------------+
| character_set_ client | utf8 |
| character_set_ connection | utf8 |
| character_set_ database | utf8 |
| character_set_ filesystem | binary |
| character_set_ results | utf8 |
| character_set_ server | utf8 |
| character_set_system | utf8 |
+--------------------------+-----------------------------------------------------+
官方说明
MySQL Connector/J Change log
我们先来说一下
正确的操作方法和步骤 1.修改mysql实例配置文件
1
2
3
4
vi my.cnf
将 character_set _server=utf8
修改为 character_set _server=utf8mb4
2.重启mysql，查看字符集
1
2
3
4
5
6
7
8
9
10
11
12
13
show variables like 'character%';
+--------------------------+-----------------------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
+--------------------------+-----------------------------------------------------+
3.打开general_log
1
2
3
4
5
6
7
set global general_log=on;
+--------------------------+ -----------------------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------------------+
| general_log | ON |
+--------------------------+-----------------------------------------------------+
3.应用配置不变
推荐使用
1
2
3
4
这里配置了characterEncoding=utf8
而MySQL配置已修改，连接建立后字符集会被MySQL配置覆盖，使用utf8mb4
database.jdbcUrl=jdbc:mysql://192.168 .1.1 :3306 /test?useUnicode=true &characterEncoding=utf8&autoReconnect=true &rewriteBatchedStatements=TRUE
不推荐
1
2
3
4
5
这里去掉了characterEncoding选项
如果程序中在QUERY语句前包含SET NAMES UTF8MB4; 可以这样做
否则，插入失败
database.jdbcUrl=jdbc:mysql://192.168 .1.1 :3306 /test?useUnicode=true &autoReconnect=true &rewriteBatchedStatements=TRUE
4.重启应用，建立重连
5.查看general_log
6.应用验证，插入成功
感兴趣的朋友可以继续往下看
测试场景 下面是我们进行了排列组合各种配置的修改测试，其目的是想找到在不重启DB的情况下，如何完成修改的方法。结论是我们失败了，哈哈
开始测试
所有测试，建议DBA打开general_log
1
2
3
4
5
6
7
set global general_log=on;
+--------------------------+ -----------------------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------------------+
| general_log | ON |
+--------------------------+-----------------------------------------------------+
先来纠正下之前的例子
这个参数对于新创建的数据库来说，新建表默认为database字符集，而对于已存在的数据库是没用的，修改这个参数没有意义。
我们有同学说曾经测试过，无需重启DB，想想就开心，测试就此开始。。。
DB必须做的修改
按照官方文档，我们应该修改character_set_server
1
2
3
4
5
6
7
8
9
10
11
12
13
set global character_set_server=utf8mb4;
+--------------------------+-----------------------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
+--------------------------+-----------------------------------------------------+
将需要用到utf8mb4字符集的表结构修改1
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
开发会说，我只修改特定字段就行了
在这里，DBA不强制修改表，但是对于维护来说，将表字符集修改比较方便管理
应用配置修改 1
2
3
database.jdbcUrl=jdbc:mysql://192.168 .1.1 :3306 /test?useUnicode=true &characterEncoding=utf8mb4&autoReconnect=true &rewriteBatchedStatements=TRUE
应用重启失败
1
2
3
4
5
傻傻按照官方文档修改，说实话没看懂文档"..." 的意思，望高手解答
database.jdbcUrl=jdbc:mysql://192.168 .1.1 :3306 /test?useUnicode=true &characterEncoding=...&autoReconnect=true &rewriteBatchedStatements=TRUE
应用重启失败
1
2
3
database.jdbcUrl=jdbc:mysql://192.168 .1.1 :3306 /test?useUnicode=true &characterEncoding=.&autoReconnect=true &rewriteBatchedStatements=TRUE
插入失败
1
2
3
database.jdbcUrl=jdbc:mysql://192.168 .1.1 :3306 /test?useUnicode=true &autoReconnect=true &rewriteBatchedStatements=TRUE
插入失败
1
2
3
database.jdbcUrl=jdbc:mysql://192.168 .1.1 :3306 /test?useUnicode=true &characterEncoding=utf8&autoReconnect=true &rewriteBatchedStatements=TRUE
插入失败
结论：character_set_server这个参数在线修改是无效的，虽然show出来是ok的，但是应用插入依然报错，DBA只能重启实例
有同学说，之前测试是可以的，我们的结论是驱动的问题，有的驱动会使用character_set_server参数，有的驱动会使用character_set_database或者database的字符集（那么， 对于使用database的，需要增加ALTER DATABASE db1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;）。这也合理的解释了为什么有的case不重启DB，也成功
