MySQL转H2

项目场景：

在使用内嵌数据库写测试用例时，遇到的MySQL导出的 .sql 文件，无法正常写入到H2内嵌数据库问题

代码示例：

    @Test
    public void h2(){
        try {
            System.out.println(convert("D:\\idea\\projects\\新能源\\app_alarm_mileage_statistic_single.sql"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }




    public static String convert(String filePath) throws IOException {
        String[] rawSQL = new String(Files.readAllBytes(Paths.get(filePath))).split("\\n");
        StringBuilder builder = new StringBuilder();

        for(String line : rawSQL) {
            if(line.contains("CHARACTER SET utf8 COLLATE utf8_general_ci")) {
                line = line.replaceAll("CHARACTER SET utf8 COLLATE utf8_general_ci", "");
            } else if(line.contains("INDEX")) {
                continue;
            } else if(line.contains("IF NOT EXISTS")) {
                line = line.replaceAll("IF NOT EXISTS", "");
            } else if(line.contains("--")) {
                continue;
            } else if(line.contains("ENGINE")) {
                line = line.replaceAll("\\).*ENGINE.*(?=)", ");");
            }else if (line.contains("USING BTREE")){
                line = line.replaceAll("USING BTREE*","");
            }

            line = line.replace("`", "");
            builder.append(line).append("\n");
        }
        return builder.toString();
    }

处理前的sql：

-- ----------------------------
-- Table structure for app_alarm_year_statistic_single
-- ----------------------------
DROP TABLE IF EXISTS  app_alarm_year_statistic_single ;
CREATE TABLE  app_alarm_year_statistic_single   (
   id  bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '序号',
   vin  varchar(17) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'vin码',
   oem  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '整车厂',
   brand  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '品牌',
   car_type  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '车型',
   st_date  char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '数据日期（yyyyMMdd）',
   create_datetime  datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
   update_datetime  datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY ( id ) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 37 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

处理后的sql：

-- ----------------------------
-- Table structure for app_alarm_year_statistic_single
-- ----------------------------
DROP TABLE IF EXISTS app_alarm_year_statistic_single;
CREATE TABLE app_alarm_year_statistic_single
(
    id                           bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '序号',
    vin                          varchar(17) NOT NULL COMMENT 'vin码',
    oem                          varchar(20) NULL DEFAULT NULL COMMENT '整车厂',
    brand                        varchar(20) NULL DEFAULT NULL COMMENT '品牌',
    car_type                     varchar(20) NULL DEFAULT NULL COMMENT '车型',
    st_date                      char(8) NULL DEFAULT NULL COMMENT '数据日期（yyyyMMdd）',
    create_datetime              datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    update_datetime              datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
    PRIMARY KEY (id)
);

数据插入语句一样需要执行此代码处理。

代码比较简单, 注释里面有说明, 不一定能处理所有情况, 但是我这里是可以了