mybatis转义反斜杠_MyBatis踩坑之SQLProvider转义字符被删除问题

目录

踩坑背景

项目架构：Spring Boot + MyBatis + MySQL。

使用MyBatis作为ORM框架，jdbc驱动使用的是mariadb-java-client。

org.mariadb.jdbc

mariadb-java-client

2.3.0

为了不使用xml形式的配置文件，MyBatis使用接口映射器，并使用映射器注解方式编写SQL语句。

@Mapper

public interface TestDAO {

@Select("select * from test where id = #{id}")

public Test getById(@Param("id") long id);

}

问题描述

在批量添加记录时通过SQLProvider动态拼装SQL，具体代码示例如下所示：

@Repository

@Mapper

public interface TestDAO {

// 使用SQLProvider拼装SQL实现批量插入

@InsertProvider(type = TestProvider.class, method = "addTestBatch")

public Integer addTestBatch(@Param("tests") List tests);

}

public class TestProvider {

public String addTestBatch(List tests) {

StringBuffer buffer = new StringBuffer().append("insert into scene(id,name,data,thumbnail,comments,ctime,mtime) values");

int size = tests.size();

for(int i = 0; i < size; i++) {

Test test = tests.get(i);

buffer.append("(")

.append(test.getId()).append(",")

.append("'").append(test.getName()).append("'").append(",")

.append("'").append(test.getData()).append("'").append(",")

.append("'").append(test.getThumbnail()).append("'").append(",")

.append("'").append(test.getComments()).append("'").append(",")

.append("now()").append(",")

.append("now()")

.append(")");

if(i < (size - 1)) {

buffer.append(",");

}

}

return buffer.toString();

}

}

Test对象的data属性值为json字符串，其中带有MySQL转意字符“”，使用上述方式添加记录时会导致test对象的data属性值中的字符“”被删除掉。

具体来说，假设Test对象的data属性值为：{"value":"{"x":277,"y":29}"}，插入MySQL之后变成了：{"value":"{"x":277,"y":29}"}。

显然，Test对象的data属性值插入MySQL之后其中的字符“”被删除了，这将导致该属性再次从MySQL中查询出来之后无法使用！

原因追踪

一开始我以为是MyBatis的原因导致的，因为使用如下方式插入单调记录是没有问题的：

@Repository

@Mapper

public interface TestDAO {

// 插入单条记录

@Insert("insert into test(id,name,data,thumbnail,comments,ctime,mtime) values(#{id},#{name},#{data},#{thumbnail},#{comments},now(),now())")

public Integer add(Scene scene);

}

通过程序日志可以看到2种方式使用的SQL语句不一样！

通过SQLProvider拼装SQL的方式在日志中看到发送给MySQL的语句为：

而通过@Insert注解方式定义SQL在日志中看到发送给MySQL的语句为：

显然，二者的区别在于：前者使用PreparedStatement时参数列表为空，实际上列值已经在SQL语句中了，本质上并没有使用PreparedStatement。

排查到这里，心里基本有点眉目了，该问题大概率不是MyBatis的锅！

于是我直接把第一种方式的SQL语句通过MySQL客户端执行，果然插入MySQL之后其中的字符“”被删除了！！！

也就是说，这其实是MySQL本身的原因导致的，最终通过查阅MySQL官方文档得以确认：

上述这段话的大概意思就是说，MySQL在默认情况下(SQL模式不是“NO_BACKSLASH_ESCAPES”)会将插入字段中的字符“”删除掉。

解决方案

既然找到的问题的根源，那就不难解决了。

实际上，有2种解决办法：

方法一

修改MySQL配置，让MySQL的SQL模式运行在“NO_BACKSLASH_ESCAPES”模式下。

默认情况下，MySQL的SQL模式不包含“NO_BACKSLASH_ESCAPES”。修改配置文件“/etc/my.cnf”，重启MySQL即可。

$ sudo vim /etc/my.cnf

$ [mysqld]

sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES

修改之后重启MySQL，再次查看SQL模式：

修改MySQL的SQL模式为“NO_BACKSLASH_ESCAPES”之后，再次插入带有字符“”的内容就不再会被删除了。

方法二

虽然方法一可以解决问题，但是未免太过于兴师动众，而且对于线上运行的实例通常不能做重启操作。另一个解决办法就是通过在JDBC客户端解决，只要确保在客户端使用PreparedStatement预处理语句即可解决该问题。原因是在PreparedStatement预处理语句中会对转义字符做处理，如下我们通过追踪“mariadb-java-client”的源码来确认一下。

显然，在PreparedStatement预处理语句中会对转义字符做特别处理，具体来讲：当查询的字段中包含'，"，，NUL时，会在这些字符前面再加一个转义字符，所以最终发送给MySQL服务器的SQL语句中这些字符对应就变成了'，"，\，NUL，如果此时MySQL的SQL模式不是”NO_BACKSLASH_ESCAPES“时，会删除其中的转义字符，这样就可以使得插入到数据库中的这些特殊字符还原为自身了。

到这里我们再来回看方法一的解决方式并不优雅而且笨重，甚至会带来诸多限制。一旦使用了方法一的解决方案，那么就不能在客户端使用预处理语句PreparedStatement了，否则将会导致最终插入到MySQL中的特殊字符多带一个转义字符”“，将会带来新的问题。

再次回到实际开发中的场景，当使用MyBatis作为ORM框架时，只使用接口映射器的情况下，该如何配置SQL语句才能实现批量插入呢？

实际上，MySQL的映射器注解支持xml风格的动态SQL配置，如下所示：

@Insert({

"

"insert into test(id,name,data,thumbnail,comments,ctime,mtime) values",

"",

"(#{test.id},#{test.name},#{test.data},#{test.thumbnail},#{test.comments},now(),now())",

"",

""

})

public Integer addTestBatch(@Param("tests") List tests);

使用这种方式的SQL配置，也会使用PreparedStatement预处理方式对特殊字符进行处理，所以可以解决问题。