导入导出sql脚本文件

业务场景

在复杂系统中，有时候去做数据同步我们需要一次导出导入十几张表甚至更多表的数据，这些数据在不同数据库，也可能分布不同微服务中，三个四个甚至更多，如果使用excel去处理会很复杂，任务复杂度很高，这时候我们就可以使用导出sql脚本文件，然后去其他环境导入sql脚本文件。

使用sql文件导入导出安全吗？

答案是，如果你不做任何安全处理，这样方式是及其不安全的，存在严重的安全隐患，那么我们如何去消除这些隐患呢？这就需要我们对我们导入的sql脚本文件进行完整性校验，即导入和导出的文件内容不允许改变，那么如何做呢？

文件的完整性如何校验？

文件的完整性校验，方式有很多，最常见的摘要算法MD5，假如我们使用MD5算法，我们可以首先对生成的sql原文件进行MD5，然后对MD5值进行AES加密，由于AES秘钥保存在服务端只有我们自己知道，这样即使别人拿到导出的文件而且又知道了我们的幂等性算法的规则，由于其不知道我们私钥，也无法篡改出合规的数据。我们可以把加密后的值作为文件名（加密前使用固定名称，如：export.sql），或者把加密后的值添加到文件的结尾（这时校验的时候需要注意先把追加到文件结尾的幂等值移除，再去做文件完整性校验）。

如何导入导出sql文件？

首先，需要梳理出N多张表的查询sql，如果是覆盖更新还需要梳理出N多张表的删除sql；
遍历查询sql，将每条sql的查询结果转换成insert语句，再组装到一起，得到sql集合；
将删除sql和insert sql 输入到export.sql中，如果是不同数据库的数据，可以使用use XXdb; 去切换数据库；
对生成的export.sql做摘要算法然后对其值进行二次加密；
将二次加密后的值，作为文件名，或者将其追加到文件结尾；
导入时，首先要对sql文件的完整性进行校验；
然后直接执行sql文件，而不是逐条读取再插入。

关键代码如下：

通用查询Mapper

/*** 查询* @param sql* @return*/List<Map<String, Object>> selectBySql(String sql);

<select id="selectBySql" resultType="java.util.Map">${sql}
</select>

将查询sql结果转换成insert集合

/*** 生成插入sql语句 --依据查询sql* @param sql* @param db* @return*/
private List<String> getInsertSqlList(String sql, String db) {String tableName = getTableNameBySql(sql, db);List<Map<String, Object>> list = sqlMapper.selectBySql(sql);List<String> sqlList = new ArrayList<>();if(CollUtil.isEmpty(list)) {return sqlList;}for (Map<String, Object> map : list) {StringBuilder column = new StringBuilder();StringBuilder values = new StringBuilder();int size = map.size();int i = 0;for (Map.Entry<String, Object> entry : map.entrySet()) {if(i == 0) {column.append("( ");column.append(entry.getKey());column.append(", ");values.append("( ");values.append(format(entry.getValue()));values.append(", ");} else if (i < size -1) {column.append(entry.getKey());column.append(", ");values.append(format(entry.getValue()));values.append(", ");} else {column.append(entry.getKey());column.append(")");values.append(format(entry.getValue()));values.append(")");}i++;}String insertsSql = "insert into " + tableName + " " + column + " values " + values + ";";log.info("insertsSql: {}", insertsSql);sqlList.add(insertsSql);}return sqlList;
}

生成sql脚本文件

/*** 生产sql脚本文件* @param appId* @param appTypeId* @param userDomainId* @return*/
private String generateFile(Long id) {List<String> sqlList = new ArrayList<>();// 切换至db1库String db1Sql = "use " + db1 + ";";sqlList.add("\n");sqlList.add("-- 切换到" + db1Sql );sqlList.add(db1Sql);// 获取删除sql（删除quec_end_user库历史数据）sqlList.add("\n");sqlList.add("-- 删除老的数据 id=" + id);List<String> deleteSqlList = getDb1DeleteSqlList(id);sqlList.addAll(deleteSqlList);sqlList.add("\n");sqlList.add("-- 插入新的数据 id=" + id);List<String> selectSql1List = getDbSqlList(id);for (String sql : selectSql1List) {List<String> list = getInsertSqlList(sql, null);if(CollUtil.isNotEmpty(list)) {sqlList.addAll(list);}}// 切换至db2库String db2Sql = "use " + db2Db + ";";sqlList.add("\n");sqlList.add("-- 切换到" + db2Db);sqlList.add(northSql);sqlList.add("\n");sqlList.add("-- 删除老的数据 id=" + id);deleteSqlList = getDb2DeleteSqlList(id);sqlList.addAll(deleteSqlList);sqlList.add("\n");sqlList.add("-- 插入新的数据 id=" + id);List<String> selectSql2List = getDb2SqlList(id);for (String sql : selectSql2List) {List<String> list = getInsertSqlList(sql, db2Db);if(CollUtil.isNotEmpty(list)) {sqlList.addAll(list);}}try {String path = System.getProperty("user.dir") + filePath + "export_" + appId + ".sql";log.info("path: {}", path);export(sqlList, path);return path;} catch (IOException e) {log.error("导出sql文件异常，appId = {}", appId);}return "";
}

private void export(List<String> sqlList, String filePath) throws IOException {// 写入文件FileWriter writer = new FileWriter(filePath);for (String sql: sqlList) {writer.write(sql + "\n");}writer.close();
}

/*** 获取表名* @param sql* @param db* @return*/public static String getTableNameBySql(String sql, String db) {if(StrUtil.isEmpty(sql)) {return null;}sql = sql.toLowerCase();String patternStr = "from\\s+([\\w\\.]*)";Pattern pattern = Pattern.compile(patternStr, Pattern.CASE_INSENSITIVE);Matcher matcher = pattern.matcher(sql);if (matcher.find()) {String name = matcher.group(1);if(StrUtil.isNotEmpty(db) && StrUtil.isNotEmpty(name)) {db = db.toLowerCase();String target = db + ".";name = name.replace(target, "");}return name;}return null;}

/*** XXXXXX --查询sql* @param appId* @return*/
private List<String> getDb1SqlList(Long id) {List<String> sqlList = new ArrayList<>();// termsString terms_sql = "select * from  "  + db1 + ".terms where app_id = %d and is_delete = 0";sqlList.add(String.format(terms_sql, id));// terms_fileString terms_file_sql = "select * from "  + db1 + ".terms_file where tos_id in (select id from "  + db1 + ".terms where id = %d and is_delete = 0)";sqlList.add(String.format(terms_file_sql , id));return sqlList;
}

/*** 字段值格式化* @param obj* @return*/
private Object format(Object obj) {if(obj.getClass().equals(String.class)) {return "'" + obj + "'";}if(obj.getClass().equals(LocalDateTime.class)) {LocalDateTime localDateTime = (LocalDateTime) obj;Date date = Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant());return  "'" + DateUtil.formatDateTime(date) + "'";}return obj;
}

/*** 执行sql脚本* @param path* @throws SQLException*/
private void executeSqlScript(String path) throws SQLException {ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();resourceDatabasePopulator.addScript(new ClassPathResource(path));resourceDatabasePopulator.execute(dataSource);
}