springboot 整合mysql实现版本管理通用最新解决方案

一、前言

1.1 单独执行初始化sql

1）好处：sql文件单独维护，与项目代码分开，可以避免文件管理上的麻烦； 2）缺点：实施时需要较多的人力干预，如果sql与工程代码是多人维护，升级中遇到问题，需要多方协调人力参与排查问题，而且这种方式难以做到自动化。

1.2 程序自动执行

二、数据库版本升级管理问题

• V1版本，初始化数据表，给部分表创建索引，同时初始化部分数据；
• V1版本的业务开发完成并上线；
• V2版本新的需求来了，需要继续追加新表，之前的某个表需要补充字段，同时某个表需要批量修改数据以适配新的业务；
• V3...

三、spring 框架sql自动管理机制

3.1 jdbcTemplate 方式

3.1.1 创建数据库

create database bank;

3.1.2 创建 springboot 工程

3.1.3 初始化sql脚本

-- 初始化建表sql
use bank;
-- tb_user表建表
CREATE TABLE `tb_user` (
 `id` int(12) NOT NULL,
 `user_name` varchar(32) DEFAULT NULL,
 `password` varchar(64) DEFAULT NULL,
 `phone` varchar(32) DEFAULT NULL,
 `address` varchar(64) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- tb_role表建表
CREATE TABLE `tb_role` (
 `id` int(12) NOT NULL,
 `role_name` varchar(32) DEFAULT NULL,
 `role_type` varchar(64) DEFAULT NULL,
 `code` varchar(32) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- tb_role 表增加索引
alter tb_role add index idx_code(code);

3.1.4 核心配置类

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
/**
 * 成功执行
 */
@Component
@Slf4j
public class DatabaseInitializer implements InitializingBean {
 @Value("classpath:db/init.sql")
 private Resource dataScript;
 @Bean
 public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
 final DataSourceInitializer initializer = new DataSourceInitializer();
 // 设置数据源
 initializer.setDataSource(dataSource);
 initializer.setDatabasePopulator(databasePopulator());
 return initializer;
 }
 private DatabasePopulator databasePopulator() {
 final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
 populator.addScripts(dataScript);
 log.info("sql脚本初始化完成");
 return populator;
 }
 @Override
 public void afterPropertiesSet() throws Exception {
 }
}

3.1.5 执行sql初始化

3.2 配置文件方式

spring:
 datasource:
 type: com.alibaba.druid.pool.DruidDataSource
 url: jdbc:mysql://IP:3306/bank?createDatabaseIfNotExist=true&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8
 driver-class-name: com.mysql.jdbc.Driver
 username: root
 password: root
 #初始化sql建表相关配置属性 ========================
 schema: classpath:db/init.sql #初始化sql语句，DDL相关
 data: classpath:db/data.sql # 追加数据，添加字段，索引等
 separator: ";" # sql语句中的结束分隔符，通常为;
 continue-on-error: false #执行错误时是否继续
 initialization-mode: always #是否每次启动工程时都执行
 platform: mysql #使用哪种数据库
 sqlScriptEncoding: utf-8 #编码

四、Flyway 实现数据库版本自动管理

4.1 Flyway 简介

Flyway是独立于数据库的应用、管理并跟踪数据库变更的数据库版本管理工具。通俗来讲，Flyway就像Git管理代码那样，管理不同版数据库本sql脚本，从而做到数据库同步升级。

4.2 Flyway 执行流程与原理

• 按照Flyway 的配置要求，完成相关的配置信息；
• 运行项目，会在默认的数据库下（没有指定的话就用在项目的数据库）下创建Flyway自带的数据表，用于存储Flyway运行中的数据，比如记录了本次升级的版本号，时间等；
• 对工程文件目录进行扫描，主要是需要初始化加载或运行的sql文件路径，然后Flyway执行数据迁移时将基于sql脚本版本号进行排序，按顺序执行，这个由Flyway内部的机制保障；
• 初次执行完sql脚本之后，将会在Flyway运行时数据表中记录关键的信息，比如本次升级的版本号，下次项目继续启动时将会对比sql文件以确定是否需要再次执行sql升级；

4.3 Flyway sql脚本命名规范

V  为版本迁移， U  为回滚迁移， R  为可重复迁移。在日常开发中，我们使用  V  前缀，表示版本迁移。绝大多数情况下，我们只会使用  V  前缀。

每一个迁移脚本，都需要一个对应一个唯一的版本号。而脚本的执行顺序，按照版本号的顺序。一般情况下，每次发生了sql脚本的变更，我们使用数字自增即可。

两个  _  ，即  __  ，约定的一种配置，建议按这种规范执行。

在下面的示例中，我们使用 init，或update，一般见名知义即可。

.sql ，可配置，不过一般可以不用配置，默认即为.sql。

4.3.1 sql命名参考示例

V20230810__init.sql V20230810__data.sql V20230810.01_init.sql V20230810.02_data.sql

固定大写（必须大写，小写不执行），约定规范；

20230810是日期，后面用.01代表序号，如果前面日期相同，但是后面的序号不一样，.02比.01版本高，.03比.02版本高，Flyway 在执行sql脚本时是严格按照版本号的顺序进行；

Flyway 比较两个 SQL 文件的先后顺序时采用 采用左对齐原则, 缺位用 0 代替 。

• 1.0.1.1 比 1.0.1 版本高；
• 1.0.10 比 1.0.9.4 版本高；
• 1.0.10 和 1.0.010 版本号一样，每个版本号部分的前导 0 会被忽略；

4.3.2 sql命名规范补充说明

当与V开头的sql文件在一起执行时，V开头的SQL执行优先级要比R开头的SQL优先级高

4.4 SpringBoot集成Flyway

1、V1版本，初始化建表； 2、V2版本，给表初始化一些数据，补充索引、字段等； 3、V3版本，依次类推..

4.4.1 引入基础依赖

 <dependency>
 <groupId>org.flywaydb</groupId>
 <artifactId>flyway-core</artifactId>
 <version>6.0.7</version>
 </dependency>
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-web</artifactId>
 <version>2.3.2.RELEASE</version>
 </dependency>

4.4.2 配置Flyway参数

spring:
 datasource:
 type: com.alibaba.druid.pool.DruidDataSource
 url: jdbc:mysql://IP:3306/bank
 driver-class-name: com.mysql.jdbc.Driver
 username: root
 password: root
 flyway:
 enabled: true
 encoding: UTF-8
 cleanDisabled: true
 ## 迁移sql脚本文件存放路径，默认db/migration
 locations: classpath:db/migration
 ## 迁移sql脚本文件名称的前缀，默认V
 sqlMigrationPrefix: V
 ## 迁移sql脚本文件名称的分隔符，默认2个下划线__
 sqlMigrationSeparator: __
 # 迁移sql脚本文件名称的后缀
 sqlMigrationSuffixes: .sql
 validateOnMigrate: true
 # 设置为true，当迁移发现数据库非空且存在没有元数据的表时，自动执行基准迁移，新建schema_version表
 baselineOnMigrate: true

• locations，即升级sql的文件位置；
• sqlMigrationSuffixes，执行sql文件的后缀名；
• 数据库连接信息必不可少，因为Flyway升级过程中需要初始化自己的表；

4.4.3 准备升级sql脚本

-- 初始化建表sql
use bank;
-- tb_user表建表
CREATE TABLE `tb_user` (
 `id` int(12) NOT NULL,
 `user_name` varchar(32) DEFAULT NULL,
 `password` varchar(64) DEFAULT NULL,
 `phone` varchar(32) DEFAULT NULL,
 `address` varchar(64) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- tb_role表建表
CREATE TABLE `tb_role` (
 `id` int(12) NOT NULL,
 `role_name` varchar(32) DEFAULT NULL,
 `role_type` varchar(64) DEFAULT NULL,
 `code` varchar(32) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 初始化建表sql
use bank;
-- 给tb_user表添加一个索引
alter table tb_user add index idx_name(user_name);
-- 给tb_user表追加新字段
alter table tb_user add column woker varchar(32) default null;

4.4.4 测试效果一：执行sql初始化建表

4.4.5 测试效果二：执行sql创建索引

4.5 使用经验与问题总结

• V开头的sql脚本，执行后不可修改，修改会导致启动项目时Flyway检测不通过，导致项目启动失败，一旦有sql变更，建议增加版本号使用新的sql文件；
• 开发或测试阶段需修改已执行脚本，可通过删除相关flyway_schema_history表元数据后，重启项目；
• 脚本命名不规范，导致脚本未被执行，比如未使用V开头命名文件，可参阅官网或资料认真了解命名规范；
• 脚本文件名版本和描述之间没有使用双下滑线‘__’分割导致执行失败；
• 新增脚本，版本号没有大于已有版本号，导致执行失败；
• 各sql语句要以分号‘;’结尾，忘记用分号会导致sql执行失败，语法格式牢记于心；
• sql语句不满足可重复执行，新环境执行sql导致失败；

五、Liquibase 实现数据库版本自动管理

5.1 Liquibase简介

5.2 Liquibase 优势

• 可以用上下文控制sql在何时何地如何执行；
• 根据配置文件自动生成sql语句用于预览；
• 配置文件支持SQL、XML、JSON 或者 YAML；
• 可重复执行迁移，可回滚，且支持插件化配置拓展；
• 兼容14中主流数据库如oracle，mysql，pg等，支持平滑迁移；
• 版本控制按序执行，支持schmea的变更；
• 与springboot整合简单，上手简单；
• 支持schema方式的多租户(multi-tenant)；

5.3 SpringBoot集成Liquibase

5.3.1 导入依赖

 <dependency>
 <groupId>org.liquibase</groupId>
 <artifactId>liquibase-core</artifactId>
 <version>3.6.3</version>
 </dependency>

5.3.2 新建master.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
 <changeSet id="v1.0.0-1" author="zcy" context="main">
 <sqlFile path="classpath:/sql/v1.0.0/1.init.sql"/>
 <sqlFile path="classpath:/sql/v1.0.0/2.init-data.sql"/>
 </changeSet>
</databaseChangeLog>

5.3.3 创建sql文件

5.3.4 自定义Liquibase配置类

import liquibase.integration.spring.SpringLiquibase;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.DefaultResourceLoader;
import javax.annotation.Resource;
import javax.sql.DataSource;
@Slf4j
@Configuration
public class LiquibaseConfig {
 //指定liquibase版本
 @Value("${dc.version:main}")
 private String contexts;
 //是否开启liquibase
 @Value("${dc.liquibase.enable:true}")
 private Boolean enable;
 //liquibase用到的两张表，记录与管理升级sql相关
 private static final String DATABASE_CHANGE_LOG_TABLE = "lqb_changelog_demo";
 private static final String DATABASE_CHANGE_LOG_LOCK_TABLE = "lqb_lock_demo";
 @Resource
 private DataSource dataSource;
 /**
 * liquibase bean配置声明
 */
 @Bean
 public SpringLiquibase liquibase() {
 SpringLiquibase liquibase = new SpringLiquibase();
 // Liquibase xml 文件路径
 liquibase.setChangeLog("classpath:sql/master.xml");
 liquibase.setDataSource(dataSource);
 if (StringUtils.isNotBlank(contexts)) {
 liquibase.setContexts(contexts);
 }
 liquibase.setShouldRun(enable);
 liquibase.setResourceLoader(new DefaultResourceLoader());
 // 覆盖Liquibase changelog表名
 liquibase.setDatabaseChangeLogTable(DATABASE_CHANGE_LOG_TABLE);
 liquibase.setDatabaseChangeLogLockTable(DATABASE_CHANGE_LOG_LOCK_TABLE);
 return liquibase;
 }
}

5.3.5 效果测试一

5.3.6 效果测试二

-- 初始化建表sql
use bank;
-- tb_depart表建表
CREATE TABLE `tb_depart` (
 `id` int(12) NOT NULL,
 `dept_name` varchar(32) DEFAULT NULL,
 `dept_type` varchar(64) DEFAULT NULL,
 `code` varchar(32) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 关于使用Liquibase进行版本管理和命名规范的问题，限于篇幅原因就不再详细展开了，有兴趣的同学可参考相关资料和官方文档，网上的资料很丰富。

5.4 Flyway与Liquibase的对比

不过从入门到使用，Liquibase 并未看到比 Flyway 带来多大优势，反倒 Flyway 基于“约定大于配置”的思想，在使用上更加便捷，也更容易实现标准化。

六、自研数据库版本管理SDK工具

比如像SAAS这样的平台应用，可能存在非常多的微服务模块，如果每个应用都引第三方组件，一方面技术栈难以统一，另一方面不同的应用开发人员存在不同的学习成本，相反，如果自研一套SDK工具，只需要发布一下SDK，其他应用按照SDK的规范统一接入使用即可。

6.1 实现思路

• 统一技术栈，建议SDK中尽可能少的依赖第三方包；
• 商讨并制定sql文件的格式规范，下文会给出一个模板规范；
• SDK对外暴露的信息尽可能少，方便其他应用引用，减少对接和学习成本；

--version 1.0.0,build 2023-08-11 ,新建用户表
CREATE TABLE `tb_user` (
 `id` int(12) NOT NULL,
 `user_name` varchar(32) DEFAULT NULL,
 `password` varchar(64) DEFAULT NULL,
 `phone` varchar(32) DEFAULT NULL,
 `address` varchar(64) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表示每次升级涉及到的sql的版本号，至于是1.0.0或1.0等，可以根据团队习惯来定，需要注意的是，每次发生了sql版本的变更，程序在启动之后会执行sql的变更

即本次升级sql的时间，也可以理解为一个标识位，即发生sql变更的时间，方便后续出现问题时进行sql的回滚或问题追溯

6.2 核心代码

 
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Pattern;
/**
 * 操作sysversion表相关
 */
public class SysVersionUtils {
 public static void main(String[] args){
 boolean sysVersionTableExist = SysVersionConfig.isSysVersionTableExist();
 if(!sysVersionTableExist){
 //如果没有创建表，执行表的创建
 SysVersionConfig.checkAndCreateSysVersion();
 }
 //到这一步，说明已经创建完成了，接下来开始解析sql文件进行脚本的初始化
 String sqlFilePath= "classpath:sql/table_init.sql";
 parseAndStoreVersionInfo(sqlFilePath);
 }
 public static void parseAndStoreVersionInfo(String sqlFilePat) {
 File databaseFile = null;
 try {
 databaseFile = ResourceUtils.getFile(sqlFilePat);
 } catch (FileNotFoundException e) {
 System.out.println("获取或解析sql文件失败");
 e.printStackTrace();
 return;
 }
 List<String> readLines = null;
 try {
 readLines = FileUtils.readLines(databaseFile, "UTF-8");
 } catch (IOException e) {
 e.printStackTrace();
 System.out.println("解析sql文件失败");
 return;
 }
 if (CollectionUtils.isEmpty(readLines)) {
 return;
 }
 //如果解析数据时发现不合理的数据行，是直接抛出异常不再执行？还是忽略？可以自行扩展，这里暂时跳过，记录日志
 List<String> versionInfoList = new LinkedList<>();
 for (String readLine : readLines) {
 if (!isValidVersionTagLine(readLine)) {
 continue;
 }
 versionInfoList.add(readLine);
 }
 //检查数据库中sys_version数据是否存在
 Connection connection = JdbcUtils.getConnection();
 VersionRecord dbVersionRecord = getDbVersionRecord(connection);
 if (dbVersionRecord == null) {
 System.out.println("初次执行sql变更");
 String upgradeSql = getSqlTextFromLines(readLines);
 System.out.println("to execute sql : " + upgradeSql);
 //解析得到最后一行的version数据
 String lastVersionLine = ((LinkedList<String>) versionInfoList).getLast();
 TemLineRecord temLineRecord = ParseUtils.getFromLine(lastVersionLine);
 System.out.println("本次变更的sql版本号信息" + temLineRecord.getVersionNum() + ":" + temLineRecord.getBuildDate());
 //TODO 执行sql建表相关的创建
 initSysVersion(connection,"app",temLineRecord.getVersionNum(),temLineRecord.getBuildDate());
 //执行sql升级
 doUpgradeSql(connection,upgradeSql);
 System.out.println("完成sql的初始化执行");
 return;
 }
 /**
 * FIXME 1、如果sys_version已经有数据了，说明之前做过升级了，只需要解析最新的版本变更;
 * FIXME 2、可以重复执行，如果没有版本变更，直接忽略，不用解析
 */
 //拿到最后一行的version信息，下面使用
 String lastVersionLine = ((LinkedList<String>) versionInfoList).getLast();
 TemLineRecord lineRecord = ParseUtils.getFromLine(lastVersionLine);
 if(lineRecord.getBuildDate().equals(dbVersionRecord.getBuildDate()) &&
 lineRecord.getVersionNum().equals(dbVersionRecord.getVersionNum())){
 System.out.println("已经是最新版本号，无需升级");
 return;
 }
 //从哪一行开始的数据才是本次需要执行的sql呢？那就是：数据库中的两个字段和 --version 以及时间正好能够对的上的那一行开始
 getTargetSqlLines(readLines, versionInfoList, dbVersionRecord);
 String sqlText = getSqlTextFromLines(readLines);
 System.out.println("待执行的sql ：" + sqlText);
 //执行本次sql的更新
 doUpgradeSql(connection,sqlText);
 //升级版本号
 updateSysVersion(lineRecord,dbVersionRecord.getId());
 }
 private static void getTargetSqlLines(List<String> readLines, List<String> versionInfoList, VersionRecord dbVersionRecord) {
 String targetLine = null;
 int lineNum = 0;
 for (int i = 0; i < versionInfoList.size() - 1; i++) {
 if (versionInfoList.get(i).contains(dbVersionRecord.getVersionNum()) && versionInfoList.get(i).contains(dbVersionRecord.getBuildDate())) {
 lineNum = i;
 break;
 }
 }
 targetLine = versionInfoList.get(lineNum + 1);
 int targetNum = 0;
 for (int i = 0; i < readLines.size(); i++) {
 if (readLines.get(i).equalsIgnoreCase(targetLine)) {
 //找到这一行的行号
 targetNum = i;
 break;
 }
 }
 //接下来移除targetNum之前的所有元素
 readLines.subList(0, targetNum+1).clear();
 }
 /**
 * 将读取到的数据行组装成sql
 * @param allLines
 * @return
 */
 public static String getSqlTextFromLines(List<String> allLines){
 if(CollectionUtils.isEmpty(allLines)){
 System.out.println("数据行为空");
 return null;
 }
 StringBuilder stringBuilder = new StringBuilder();
 for(String text : allLines){
 if(text.startsWith("--")){
 continue;
 }else if(StringUtils.isEmpty(text)){
 continue;
 }
 stringBuilder.append(text);
 stringBuilder.append("\n");
 }
 return stringBuilder.toString();
 }
 /**
 * 更新版本号
 * @param lineRecord
 * @param id
 */
 public static void updateSysVersion(TemLineRecord lineRecord,String id){
 Connection connection = JdbcUtils.getConnection();
 PreparedStatement ps=null;
 String sql="UPDATE sys_version SET BUILD_DATE=?, VERSION_NUM= ? WHERE id=?";
 try {
 ps=connection.prepareStatement(sql);
 ps.setString(1, lineRecord.getBuildDate());
 ps.setString(2, lineRecord.getVersionNum());
 ps.setString(3, id);
 ps.execute();
 } catch (SQLException e) {
 e.printStackTrace();
 }finally {
 try {
 connection.close();
 ps.close();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }
 }
 /**
 * 初次新增版本号
 * @param connection
 * @param id
 * @param versionNum
 * @param dateStr
 */
 public static void initSysVersion(Connection connection,String id,String versionNum,String dateStr){
 PreparedStatement ps=null;
 String sql = "insert into sys_version values('" +id+"','"+versionNum+"','"+dateStr+"')";
 try {
 ps = connection.prepareStatement(sql);
 ps.execute(sql);
 System.out.println("初始化插入sys_version版本号成功");
 } catch (SQLException e) {
 e.printStackTrace();
 System.out.println("初始化插入sys_version版本号失败");
 }
 }
 /**
 * 执行升级sql
 * @param connection
 * @param sql
 * @throws SQLException
 */
 private static void doUpgradeSql(Connection connection, String sql) {
 if(StringUtils.isEmpty(sql)){
 System.out.println("带升级的sql不存在");
 return;
 }
 Statement statement = null;
 try {
 statement = connection.createStatement();
 String[] sqlCommands = sql.split(";");
 for (String sqlCommand : sqlCommands) {
 if (!sqlCommand.trim().isEmpty()) {
 try {
 statement.executeUpdate(sqlCommand);
 } catch (SQLException e) {
 e.printStackTrace();
 System.out.println("执行升级sql失败");
 }
 }
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }finally {
 try {
 statement.close();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }
 }
 /**
 * 获取数据库的最新版本号
 * @param connection
 * @return
 */
 public static VersionRecord getDbVersionRecord(Connection connection) {
 String sql = "select * from sys_version";
 PreparedStatement ps = null;
 ResultSet rs = null;
 VersionRecord versionRecord = null;
 try {
 ps = connection.prepareStatement(sql);
 rs = ps.executeQuery();
 if (rs.next()) {
 versionRecord = new VersionRecord();
 versionRecord.setId(rs.getString("id"));
 versionRecord.setBuildDate(rs.getString("BUILD_DATE"));
 versionRecord.setVersionNum(rs.getString("VERSION_NUM"));
 }
 } catch (SQLException e) {
 e.printStackTrace();
 System.out.println("获取最新版本号失败");
 }
 return versionRecord;
 }
 private static Pattern oldVersionTagPattern = Pattern.compile("^--version\\s[0-9\\.]+,build\\s.*");
 public static boolean isValidVersionTagLine(String tagLine) {
 return oldVersionTagPattern.matcher(tagLine).matches();
 }
}

--version 1.0.0,build 2023-08-13 ,创建索引alter table tb_user add index idx_name(user_name);

七、写在文末