广州外贸公司网站建设百度收录的网站
由于上一版发现数据库【MySQL】不支持DML事务回滚，该迭代主要是去兼容这种问题。
数据表新增一个completed字段，用来表示当前版本下同步成功的个数。
数据表
CREATE TABLE `auto_sql_version` (`id` int NOT NULL AUTO_INCREMENT,`version` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '版本号',`created` datetime NULL DEFAULT NULL COMMENT '插入时间',`completed` int NULL DEFAULT NULL COMMENT '完成同步的sql脚本数',PRIMARY KEY (`id`) USING BTREE,UNIQUE INDEX `uniqueKey_version`(`version` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '数据版本' ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;run方法/主方法逻辑
新增了一个判断，对于查到的版本号，需要额外判断completed字段是否为null，非null说明有无成功执行的sql，应该继续执行。
@Overridepublic void run(ApplicationArguments args) throws Exception {if (!databaseAutoFillSwitch) {log.info("database auto fill switch is false,skip auto fill");return;}String basePath = "/dbVersion/MySQL.sql";InputStream inputStream = this.getClass().getResourceAsStream(basePath);String sqlScript = IoUtil.readUtf8(inputStream);if (null == inputStream) {log.info("inputStream is null");return;}inputStream.close();List<String> versionList = new ArrayList<>();String[] lines = sqlScript.split("\n");for (String line : lines) {if (line.toLowerCase().contains(PREFIX)) {versionList.add(line.substring(line.lastIndexOf("-") + 1).trim().toLowerCase());}}int left = 0, right = versionList.size() - 1;// 通过二分查找，最终得到的left,表示不在库中的最小版本号，如果left == list.size() 则还需要去查询库中是否真正存在while (left <= right) {int mid = left + (right - left) / 2;VersionVo versionVo = autoSqlFillDao.selectVersionAndCompleted(versionList.get(mid));if (versionVo == null || versionVo.getVersion() == null || versionVo.getCompleted() != null) {// 当前版本号为完成同步right = mid - 1;} else {// 当前版本号已完成同步left = mid + 1;}}if (left == versionList.size()) {log.info("no new sqlVersion found,finished auto sql fill");return;}//proxy = (AutoSqlFillImpl) AopContext.currentProxy();String result = "";// 现在开始，从left指针开始遍历所有的sql脚本while (left < versionList.size()) {// 得到版本号整串String latestVersion = versionList.get(left);// 写入数据库的版本号前缀【过滤掉无效字符，统一版本号】String version = latestVersion.substring(latestVersion.lastIndexOf("-") + 1).trim().toLowerCase();// 获取版本号在sql脚本中的位置int index = sqlScript.indexOf(latestVersion);if (index == -1) {log.info("current version exception:{}", version);LogUtil.info(version, "current version exception");return;}index += latestVersion.length();String nextVersion = "";if (left + 1 < versionList.size()) {nextVersion = versionList.get(left + 1);int nextIndex = sqlScript.indexOf(nextVersion);if (nextIndex != -1) {result = sqlScript.substring(index, nextIndex).trim();executeSqlScript(result, version);} else {log.info("should have next sqlVersion,but next version not found:{}", nextVersion);LogUtil.info(version, "should have next sqlVersion,but next version not found");}} else {// 没有下一个版本，提取剩余部分result = sqlScript.substring(index).trim();executeSqlScript(result, version);}left++;}log.info("auto deploying sql finished...");}service逻辑
public void executeSqlScript(String sqlScript, String version) {//查到的最小版本号，可能由于DDL语句执行失败，导致版本未同步完全，需要独立判断//这里加if else分支，保证只有第一次查库，后面循环不需要重复查库了if (!first) {VersionVo versionVo1 = autoSqlFillDao.selectVersionAndCompleted(version);//如果当前版本号存在未成功同步的表结构，则接着执行完if (Objects.nonNull(versionVo1) && versionVo1.getVersion() != null && versionVo1.getCompleted() != null) {int completed = versionVo1.getCompleted();readBySql(sqlScript, version, completed);}first = true;}else{readBySql(sqlScript, version, 0);}// 如果所有 SQL 语句都成功执行，插入版本记录AutoSqlVersionEntity entity = new AutoSqlVersionEntity();entity.setVersion(version);entity.setCreated(new Date());autoSqlFillDao.saveOrUpdateByVersion(entity);}具体的数据库逻辑
对于有执行失败的版本号，记录对应的completed字段。
/*** 遍历sql并执行* @param sqlScript* @param version* @param completed*/public void readBySql(String sqlScript, String version, Integer completed) {String[] resultList = sqlScript.split(";");for (int i = completed; i < resultList.length; i++) {String line = resultList[i];if (!line.toLowerCase().contains("drop") && !line.toLowerCase().contains("delete") && line.length() > 10 && !line.contains("--")) {// 开始执行插入操作try {autoSqlFillDao.updateSql(line.trim());log.info("version:{}, start sql script:{}", version, line.trim());LogUtil.info("version, sql script:", version, line.trim());completed++;} catch (Exception e) {AutoSqlVersionEntity entity = new AutoSqlVersionEntity();entity.setVersion(version);entity.setCreated(new Date());entity.setCompleted(completed);autoSqlFillDao.saveOrUpdateByVersion(entity);log.info("version:{}, sql执行异常:{}", version, line.trim());LogUtil.info("sql执行异常", line.trim());StringBuilder stringBuilder = new StringBuilder("### 接口异常\n");stringBuilder.append("- **服务环境**: " + envValue + "\n");stringBuilder.append("- **异常信息**:" + e + " \n");if (!"dev".equals(envValue)) {RobotClient.pushMarkDown("自动化执行sql脚本异常，请注意脚本格式是否正确", stringBuilder.toString());}throw new RuntimeException("sql auto exception:" + line.trim());}}}}