数据库迁移数据转义问题：db2迁移到mysql

数据库迁移，db2迁移到mysql，运维使用的是MTK工具，具体操作不太清楚，但是出现了转义字符问题，就是在db2数据库中的数据为"\n"两个字符，导入到mysql数据库，数据变为"\n"一个字符（转为为了换行符），MTK工具能否设置不太清楚，但是可以通过曲线策略实现数据正常，步骤如下：

1. mysql的sql_mode增加配置NO_BACKSLASH_ESCAPES（禁止转义）；
2. MTK执行数据迁移，db2迁移到mysql；
3. mysql的sql_mode删除配置NO_BACKSLASH_ESCAPES（禁止转义）；

1.转义字符【\】问题

DBeaver客户端不同数据库【mysql、db2】相同流程结果分析：

• mysql【sql_mode不设置NO_BACKSLASH_ESCAPES（默认不设置）】：

1.DBeaver客户端执行插入语句：

INSERT INTO test(ID,NAME1,NAME2) VALUES(1, 'a\\rb\\nc','a\rb\nc\d');
1

2.DBeaver客户端执行查询语句：

SELECT * FROM test WHERE ID = 1;
1

3.DBeaver客户端查看返回结果：

结果解析：
NAME1：“a\rb\nc”，字符长度为7，\r、\n各占两个字符；
NAME2："a’\r’b’\n’cd"字符长度为6，\r转义为回车符，\n转义为换行符，\d无法转义则自动去除掉转义字符\；
mysql默认会对转义字符进行解析，会将\r、\n进行转义为单个字符，并自动去除无法进行转义的转义字符\；

4.DBeaver客户端导出数据，导出的sql如下【各个客户端导出的数据时处理回车换行符逻辑不一样】：

-- DBeaver导出sql如下：
INSERT INTO test(ID,NAME1,NAME2) VALUES(1, 'a\\rb\\nc','a
b
cd');

-- Navicat导出sql如下：
INSERT INTO test(ID,NAME1,NAME2) VALUES(1, 'a\\rb\\nc','a\rb\ncd');
1
2
3
4
5
6
7

• mysql【sql_mode设置NO_BACKSLASH_ESCAPES】：
  1.Dbeaver客户端设置sql_mode为NO_BACKSLASH_ESCAPES：

-- 查询sql_mode配置
SELECT @@sql_mode;
-- 修改会话级别的sql_mode
SET SESSION sql_mode = '...,NO_BACKSLASH_ESCAPES';
-- 修改全局级别的sql_mode
SET GLOBAL sql_mode = '...,NO_BACKSLASH_ESCAPES';
1
2
3
4
5
6

2.DBeaver客户端执行插入语句：

INSERT INTO test(ID,NAME1,NAME2) VALUES(1, 'a\\rb\\nc','a\rb\nc\d');
1

2.DBeaver客户端执行查询语句：

SELECT * FROM test WHERE ID = 1;
1

3.DBeaver客户端查看返回结果：

结果解析：
NAME1：“a\\rb\\nc”，字符长度为9，不会对转义字符\进行任何转义，原样存储；
NAME2："a\rb\nc\d"字符长度为9，不会对转义字符\进行任何转义，原样存储；
mysql默认会对转义字符进行解析，会将\r、\n进行转义为单个字符，并自动去除无法进行转义的转义字符\；

4.DBeaver客户端导出数据，导出的sql如下【各个客户端导出的数据时处理回车换行符逻辑不一样】：

-- DBeaver导出sql如下，在当前sql_mode：NO_BACKSLASH_ESCAPES，模式下再次执行sql会导致数据与源数据不一致，就是每次导出数据再次执行数据都会发生改变：
INSERT INTO test(ID,NAME1,NAME2) VALUES(1, 'a\\\\rb\\\\nc','a\\rb\\nc\\d');
1
2

• db2：

1.DBeaver客户端执行插入语句：

INSERT INTO test(ID,NAME1,NAME2) VALUES(1, 'a\\rb\\nc','a\rb\nc\d');
1

2.DBeaver客户端执行查询语句：

SELECT * FROM test WHERE ID = 1;
1

3.查看返回结果：

结果解析：
NAME1：“a\rb\nc”，字符长度为9；
NAME2："a\rb\nc\d"字符长度为9；
db2不会对转义字符进行解析，全部按照单个字符存入数据库；

4.使用DBeaver工具导出数据，导出的sql如下【各个客户端导出的数据时处理回车换行符逻辑不一样】：

-- DBeaver导出sql如下：
INSERT INTO test(ID,NAME1,NAME2) VALUES(1, 'a\\rb\\nc','a\rb\nc\d');
1
2

java程序层面不同数据库【mysql、db2】相同流程结果分析：

1.java程序执行插入语句：

entity.setId(1);
entity.setName1("a\\rb\\nc");
entity.setName2("a\rb\nc"); // java使用转义字符\后边必须跟随能够转义的特殊字符
entityDao.insert(entity);
1
2
3
4

2.java程序执行查询语句：

entity.setId(1);
Entity entity = entityDao.query(entity);
1
2

3.查看返回结果：

结果解析：
NAME1：“a\rb\nc”，字符长度为7，\r、\n各占两个字符；
NAME2："a’\r’b’\n’c"字符长度为5，\r转义为回车符，\n转义为换行符；
mysql【sql_mode不设置NO_BACKSLASH_ESCAPES（默认不设置）】、db2数据库存储的数据完全与java程序中的数据一一对应

---

参考链接：

1. SQL服务器模式：https://www.mysqlzh.com/doc/40/167.html
2. SQL服务器模式官网：https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html