记一次H2数据迁移到mysql的过程

目录

• 1 思路
• 2 导出h2数据为.sql文件
• 3 小结

1 思路

H2数据迁移到mysql，网上查找了一圈，没有现成的工具。
有几种方法，不过都可能出现各种bug，需要调试：

1. H2导出为csv文件然后导入mysql.
2. H2导出为sql文件然后导入mysql的过程
3. 使用转换工具软件，比如Spectral Core(https://www.fullconvert.com/howto/h2-to-mysql 没试过) ，比如IDEA。
   在使用第二种方法迁移数据成功后，我尝试了idea，我觉得非常好用，只是测试了导出数据到.sql文件，比使用h2内置方法导出的.sql文件人性化多了，稍微修改就能兼容和导入到mysql。当然我也没尝试完整的导出导入，因为已经迁移完成了，没必要了。
   方法：选中要导出的表，右击，选择Dump Data to File(s) -- SQL Inserts，参考如下截图：
   

下一次我会使用IDEA来进行不同数据库之间的数据迁移，原因如下：

1. 兼容10多种数据库。idea不仅可以连接mysql和h2，还能连接oracle等10多种数据库。
2. 导出的sql文件中的sql语法比较通用，和其他数据库兼容性好。
3. 一次可以导出一张表、多张表、所有表。
4. 可以导出为一个文件和可以导出为多个文件，一张表导出成一个文件。
5. 导出时可以添加建表语句，也可以不加，如果被导入数据的数据库中已存在表结构，则不需要建表语句。

我选择了第二种方法。这种方法遇到了很多坑，原因有多种，比如h2的导出sql文件完全是为了用于h2的导入，h2和mysql语法不同，需要转换，又比如hibernate执行原生的insert语句有bug。下面慢慢来说。

注：mysql中创建数据库的语法：create database halo default character set utf8mb4 default collate utf8mb4_unicode_ci;

2 导出h2数据为.sql文件

java -cp $H2DRIVERS org.h2.tools.Script -url jdbc:h2:/root/.halo/db/halo -user root -password root -script halo_h2_db_exp_original20200613.sql
# 或者如下的语法
java -cp ./h2-1.4.200.jar org.h2.tools.Script -url jdbc:h2:/root/.halo/db/halo -user root -password root -script halo_h2_db_exp_original20200613.sql

# 本地url的格式：jdbc:h2:/root/.halo/db/halo
# 远程url的格式：jdbc:h2:tcp://192.168.6.16:8082/root/.halo/db/halo

1. 坑1：STRINGDECODE
   导出sql文件中，所有的中文都是\u0011这种unicode字符，h2打算在导入数据时，使用内置函数STRINGDECODE解码，比如：
   INSERT INTO SYSTEM_LOB_STREAM VALUES(278, 0, STRINGDECODE('<h1 id=\"\\u0011\\u2390\\u0013aaabbbccc\">****</h1>')
   解决方法：写java程序将unicode字符全部转换成中文。然后用正则表达式将STRINGDECODE全部去掉。

IDEA中的操作：
STRINGDECODE\('(.*?)'\), NULL\); 替换为 '$1', NULL);
GEDIT和notepadqq中的操作：
STRINGDECODE\('(.*?)'\), 替换为 '\1',

2. 坑2：建表的sql语法不同。
   解决方法：
   （1）在建表语句最后添加默认字符集和默认校验方式：ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
   另外，AUTO_INCREMENT=40，设置自增长到哪个数字了。这个数字还要到sql文件的序列中去找：

-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_07030136_5506_425A_9226_CC6A332680DA" START WITH 39 BELONGS_TO_TABLE;
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_85A72195_448B_47C7_91E3_8EDCBF47C0B5" START WITH 38 BELONGS_TO_TABLE;
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_28935B3E_1674_4941_9D8B_E107ADBFE655" START WITH 2 BELONGS_TO_TABLE; 
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_8A63B720_7E60_4B97_B457_77B9D788B782" START WITH 100 BELONGS_TO_TABLE;  
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_BA607316_C0CA_4AC9_93B7_4EDD398DCE5E" START WITH 242 BELONGS_TO_TABLE;
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_C77F783A_7DED_4E69_9C04_901BA9342C28" START WITH 9 BELONGS_TO_TABLE; 
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_69675EBF_E100_4418_A967_F88A376A4AEF" START WITH 5775 BELONGS_TO_TABLE;           
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_95A9EFBB_A2DE_4C87_8D19_A805E62C0173" START WITH 1 BELONGS_TO_TABLE; 
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_7137CFFF_AD51_432D_AD29_C22596D7D471" START WITH 73 BELONGS_TO_TABLE;
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_56A6F5AE_2FC4_437F_A7BD_3B12F355782E" START WITH 20 BELONGS_TO_TABLE;
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_3FA77B52_1B71_4B7C_B75F_7CD707111A1C" START WITH 73 BELONGS_TO_TABLE;
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_5749AACE_18C3_42AE_A57F_F776A4CEB70B" START WITH 35 BELONGS_TO_TABLE;
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_7C9D7387_4BD6_4031_ACB0_EBF15B600A93" START WITH 6666 BELONGS_TO_TABLE;          
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_7437AF47_F79E_47AE_B97A_73C915A54E73" START WITH 1155 BELONGS_TO_TABLE;           
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_558AED66_5DC3_46BF_8E83_C8FF7AF1F02F" START WITH 1257 BELONGS_TO_TABLE;          
-- CREATE SEQUENCE `halo`."SYSTEM_SEQUENCE_42A96F72_A81E_4741_AF00_3EA740EB24E7" START WITH 39 BELONGS_TO_TABLE;

（2）修改建表语句中的字段类型为mysql语法的。
（3）每张表都有一个主键字段ID，原来是设置自增长的，使用序列来实现，mysql中没有序列，但是也可设置自增长，所以这么修改：ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT
（4）H2的字段是用双引号"包裹起来的，而mysql是用飘号`，需要用正则来替换。

eg.   `$1` VARCHAR(200) NOT NULL,
	"([A-Z0-9]+_?[A-Z0-9]*?)" VARCHAR\(  替换为   `\1` VARCHAR( 或者  `$1` VARCHAR(，notpad++使用 \1，idea使用  $1 
	"([A-Z0-9]+_?[A-Z0-9]*?)" BIGINT\(  替换为    `$1` BIGINT(
	"([A-Z0-9]+_?[A-Z0-9]*?)" TINYINT\(  替换为   `$1` TINYINT(

3. java代码报错：Caused by: org.hibernate.QueryException: Space is not allowed after parameter prefix ':'
   原因：参数前缀:(即引用占位符)的前后都不允许有空格。（其实不是这个原因）
   举例如下：

# error
...AND apply.startTime >=: startTime1 AND apply.endTime <=: endTime1   
# correct
...AND apply.startTime >=:startTime1 AND apply.endTime <=:endTime1   

解决方法：拼接时给concat参数加上英文单引号。

4. java代码报错：java.lang.IllegalArgumentException: Illegal group reference
   String test = "abc";
   test = test.replaceAll("abc", "1111111111$$");
   //以上语句会报异常java.lang.IllegalArgumentException: Illegal group reference，解决方法为改写以下形式：
   test = test.replaceAll("abc", java.util.regex.Matcher.quoteReplacement("dXNlcjM1NDk2NQ$$"));

5. java代码报错：data too long for column FORMAT_CONTENT``
   解决方法：将FORMAT_CONTENT字段类型由text（最长4K）改为mediumtext（最长16M）

6. java代码报错：java.lang.IllegalArgumentException: org.hibernate.QueryException: Unmatched braces(大括号) for alias path
   原因：我使用的原生sql语句，拼接后直接执行。此时，因为insert语句中有{括号所以会报错，解决方案就是去掉{或者加上}，使{}不出现或成对出现。
   总之，当保存的字段内容有其他特殊符号，或者比较复杂的内容时，容易出现该错误，容易使程序解析不了SQL语句。其实也就是因为insert into语句完全是用手动拼字符串而成的SQL语句。
   解决方法：

   1. 所有{后添加}。
      eg. id=549的posts记录是这么处理的。
   2. 改为字段内容提取出来成为参数，使用query.setParameter(1,strParam)来设置参数。这样也是防止了SQL注入。

7. java代码报错：IllegalArgumentException: Could not locate ordinal(顺序的) parameter [1], expecting one of []
   原因：list中没有值可以被调用。这个不讲了，很简单。

全是坑，不用hibernate了；最终使用了java原生的jdbc方法来执行insert语句，非常香！（原来使用query = em.createNativeQuery(sql)生成Query对象，再执行query.executeUpdate()）

3 小结

1. 下次使用IDEA来迁移试试。
2. 迁移之前，保存草稿时，halo系统经常报错“网络异常”，迁移之后，现在不报错了。