MYSQL-追根朔源 你的表情（🚀🚀🚀utf8mb4）插入对了吗？
问题背景
线上异常监控发现项目中有sql异常，查看发现是表情插入异常
项目环境
线上MYSQL 版本：
polarDB：5.6.16-log
测试MYSQL 版本：
5.7.25-28-log
5.7.25-28-log
druid:
1.1.10
druid-spring-boot-starter:
1.1.10
mysql-connector-java:
5.1.42
问题处理
- 下意识直接想到数据库没有用utf8mb4 类型，直接查看数据库表结构发现数据库表确实是utf8mb4
CREATE TABLE `t_course_resource` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程关联的资源id',
`c_course_uid` char(32) NOT NULL DEFAULT '' CO',
`c_uid` varchar(256) NOT NULL DEFAULT '' COMMENT '资源id',
`c_name` varchar(64) NOT NULL DEFAULT '' COMMENT '资源名',
`c_description` varchar(128) NOT NULL DEFAULT '' COMMENT '资源描述',
`c_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`c_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
`c_is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '记录是否被删除',
PRIMARY KEY (`id`),
KEY `idx_course_uid` (`c_course_uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4220171 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='课程资源'
- 为了验证数据库端没有问题，直接提交流水线，走数据库更新一条sql，提交表情字符串，发现是可以正确执行的。
上述两步验证可以暂时排除数据库端的问题。
-
测试环境和开发环境尝试用代码提交带表情的字符串，发现是可以的，对比配置中心和代码除了数据库地址完全一样。（线上是polarDB，测试是自建）
-
查看阿里云数据库文档，看到有这么一句话，发现不建议配置characterEncoding，然后瞎猜算命去掉配置，测试了下，发现还是不行。
-
询问DBA，DBA建议尝试用 connectionInitSqls = set names utf8mb4 配置测试下，没有研究过这个配置，也不清楚有什么用，但是DBA大佬都建议了，就尝试了下，确实可以，吹一句DBA大佬真香。
sql 验证
-
set name utf8mb4 作用分析和验证：
-
测试表：
CREATE TABLE `t_test_encoding` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -
1. 连接数据数据库 ，执行 show global variables like 'character%'; 查看全局的字符集配置
-
2. 执行 show variables like 'character%'; 查看当前会话的字符集配置 执行插入表情操作 INSERT INTO `t_demo` ( `name`) VALUES ('测试表情🆚🏷❤️😌');发现报错。
-
3. 执行 set names utf8mb4; 设置当前会话的字符集 4. 再次执行 show variables like 'character%'; 查看当前会话的字符集配置 再次执行插入表情操作 INSERT INTO `t_demo` ( `name`) VALUES ('测试表情🆚🏷❤️😌'); 插入OK
-
5. 执行 show global variables like 'character%'; 查看全局的字符集配置
总结：
1. 是否能够插入表情，不仅仅要求相关的字段是utf8mb4编码格式，utf8mb4仅仅只是前提
2. 除了要求字段的格式是utf8mb4，还要求当前的session会话连接的编码同时是utf8mb4
3. set name utf8mb4 只对当前会话有效，不影响其他连接和全局的配置
原因分析
-
问题到这就结束了吗？当然没有，身为一个有追求的猴子，怎么可能这样就完了，肯定要研究透这个问题，现在还有以下两点想不明白的。
- connectionInitSqls 是用来干嘛的，为什么配置一个这个就可以了？
- 为什么测试环境和开发环境可以，线上不行？（程序员界历史难题，为什么我本地是可以，上服务器就挂 [流泪] [流泪] [流泪]）
-
接下来我们就看看这两个问题的分析：
-
问题1：
-
connectionInitSqls 是druid 数据库特有的配置（查看官方文档说明该参数是在初始化连接时执行的sql），那这样就很好理解了，当我们执行set name utf8mb4之后就在当前session 中一直使用utf8mb4的编码格式
-
接下来分析 druid 数据源数据库连接初始化， connectionInitSqls 执行分析。
-
druid 数据源初始化连接：（获取配置的连接初始化执行的sql，并依次执行），也就意味着我们如果配置了utf8mb4 ，那么数据库连接初始化的时候就会执行set names utf8mb4。
-
我们在配置中心配置下connectionInitSqls
spring.datasource.druid.connection-init-sqls = set names utf8mb4 -
我们先启动项目，抓取初始化连接时和MYSQL 服务端通信的数据包看下
-
-
重点重点重点（通过抓包我们发现）
-
- 数据源和mysql的初始化连接过程并不只包含3次握手，其中还有很多次的 query-response --- sql 操作
-
- 在完成一次连接初始化之后，我们发现执行了两次字符集设置 SET NAMES utf8 和 set names utf8mb4。其中第二次 set names utf8mb4才是我们通过设置 connection-init-sqls，这次的设置也是当前会话最终生效的字符集设置，其中第一次怎么来的，先卖个关子，等会再来分析（这个问题其实就和测试开发环境为什么可以有关）
-
-
接下来我们看下druid 数据源层是如何设置这个字符集的，通过druid-spring-boot-starter 的自动配置源码, 我们debug 很容易就发现 connectionInitSqls 的执行逻辑
@Configuration @ConditionalOnClass(DruidDataSource.class) @AutoConfigureBefore(DataSourceAutoConfiguration.class) @EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class}) @Import({DruidSpringAopConfiguration.class, DruidStatViewServletConfiguration.class, DruidWebStatFilterConfiguration.class, DruidFilterConfiguration.class}) public class DruidDataSourceAutoConfigure { private static final Logger LOGGER = LoggerFactory.getLogger(DruidDataSourceAutoConfigure.class); @Bean(initMethod = "init") @ConditionalOnMissingBean public DataSource dataSource() { LOGGER.info("Init DruidDataSource"); return new DruidDataSourceWrapper(); } }
-
我们 debug断点到 Collection initSqls = getConnectionInitSqls();，再次启动项目，抓包发现 只有一次 SET NAMES utf8mb4。当我们放行这次断点，就会发现，set names utf8mb4，到此为止就很明了了， druid 数据源在初始化连接之后，还会进行配置的initSql初始化，在这次初始化里，用户设置了set names utf8mb4 之后，就会指定了最终的字符集。
-
-
-
问题2：
-
为什么测试环境和开发环境可以呢？（细心的同学可能发现，我们在解析问题1的时候已经提到了，测试环境的问题可能和第一次的set names 有关）接下来我们测试下。
-
将数据库连接改到开发环境, 重启项目，取消connectionInitSqls，debug 断点到 Collection initSqls = getConnectionInitSqls()，抓包。
-
发现只有一次SET NAMES，而且第一次就是 utf8mb4，这就意味着，即使我们不通过 druid 的 connectionInitSqls，那这次的session会话连接，也是OK的。
-
到这里，很多同学就可能觉得开发环境的第一次 SET NAMES utf8mb4 应该是合开发环境的全局字符集配置有关，我们连接开发环境执行下sql 验证下, 确实是这样的。
show global variables like 'character%';
-
那我们现在知道了，第一次的字符集设置和mysql服务端的全局配置有关，👌，到这就完了吗？，没有我们继续
-
我们知道设置当前会话的字符集设置是客户端主动设置的，那么客户端有什么理由这样设置呢？，我们看源码发现druid没有做这样的操作，在druid设置之前，这个动作已经发生了，那我们猜应该是在mysql-connector-java里设置的，接下来看源码验证。
-
我们debug的路径是这样的（省略了查找最终代码的过程），给大家展示一个debug的路径，大家可以根据这个路径去查找
-
我们发现是否SET NAMES utf8mb4 取决于下面的条件, 我们来分析下这两个条件：
boolean utf8mb4Supported = versionMeetsMinimum(5, 5, 2); boolean useutf8mb4 = utf8mb4Supported && (CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex)); /** * Does the version of the MySQL server we are connected to meet the given * minimums? * * @param major * @param minor * @param subminor */ boolean versionMeetsMinimum(int major, int minor, int subminor) { if (getServerMajorVersion() >= major) { if (getServerMajorVersion() == major) { if (getServerMinorVersion() >= minor) { if (getServerMinorVersion() == minor) { return (getServerSubMinorVersion() >= subminor); } // newer than major.minor return true; } // older than major.minor return false; } // newer than major return true; } return false; }
-
-
分析条件
第一个条件 MySQL server version是否比 5.5.2 大 第二个条件 CharsetMapping.UTF8MB4_INDEXES 是否包含 this.io.serverCharsetIndex 只有两个条件同时满足才可以设置 utf8mb4，否则就是utf8 第一个很好理解，而且我们MYSQL SERVER的版本也满足 主要是看下第二个条件 先看下 UTF8MB4_INDEXES 放的是什么？
private static final String MYSQL_CHARSET_NAME_utf8mb4 = "utf8mb4"; Collation[] collation = new Collation[MAP_SIZE]; collation[1] = new Collation(1, "big5_chinese_ci", 1, MYSQL_CHARSET_NAME_big5); ........ collation[45] = new Collation(45, "utf8mb4_general_ci", 1, MYSQL_CHARSET_NAME_utf8mb4);-
我这边摘取了部分源码，详细的可以看 com.mysql.jdbc.CharsetMapping 类
-
通过上述代码，我们发现 UTF8MB4_INDEXES， 存放的其实是 45一个整数值
-
再回过头看这个条件，我们只需要知道 this.io.serverCharsetIndex 这个值是多少就可以了
CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex)
-
再次跟进代码 com.mysql.jdbc.MysqlIO#doHandshake （省略其他代码）
/** * Initialize communications with the MySQL server. Handles logging on, and * handling initial connection errors. * 初始化与MySQL服务器的通信。处理登录和初始连接错误。 * @param user * @param password * @param database * * @throws SQLException * @throws CommunicationsException */ void doHandshake(String user, String password, String database) throws SQLException { .... .... if ((versionMeetsMinimum(4, 1, 1) || ((this.protocolVersion > 9) && (this.serverCapabilities & CLIENT_PROTOCOL_41) != 0))) { /* New protocol with 16 bytes to describe server characteristics */ // read character set (1 byte) this.serverCharsetIndex = buf.readByte() & 0xff; // read status flags (2 bytes) this.serverStatus = buf.readInt(); checkTransactionState(0); } }-
发现这个属性是在和服务端初始化连接时设置的，再次抓包握手代码，发现3次握手完毕后，服务端有一个相应，回复了 Server Language: utf8mb4 COLLATE utf8mb4_general_ci (45) 服务器端的字符集设置
45 是怎么来的呢，其实是我们数据库 information_schema.COLLATIONS 表的id字段
-
现在就很明了了，初始化连接时， mysql-connector-java 会获取当前数据库的mysql 全局字符配置，然后根据MYSQL SERVER的版本和MYSQL SERVER 的字符集配置来决定使用哪个字符集进行设置 SET NAMES
-
总结：
-
是否能够插入表情，不仅取决于字段的编码格式，还有当前session会话的编码格式
-
设置utf8mb4
-
设置全局的 字符集设置 show global variables like 'character%'; -DBA可能不会帮你搞，在线上跑了这么久，你让我改这个？
-
设置当前会话的字符集设置 druid connectionInitSqls
-
如果没有这个配置，可以在连接池初始化之后手动执行一次 set names utf8mb4
-
升级mysql-connector-java 到 5.1.47，
5.1.47版本的characterEncoding参数设置为UTF8/UTF-8的时候, 会直接映射到 utf8mb4, 不像低版本那样还需要依赖数据库返回的编码, 也不用重启数据库即可生效 dev.mysql.com/doc/relnote…
-
转载自:https://juejin.cn/post/6844904178536415245