spring-cloud集成数据库版本迁移工具flyway

Spring-Cloud集成数据库版本迁移工具Flyway

Flyway实现数据库版本同步有两种方式，一种就是直接导包，通过配置文件使用，还有一种就是自定义的方式。

一 、依赖+配置文件

1 flyway实现sql初始化

1.1 首先需要添加依赖

<!--mysql数据库版本管理控制器flyway-->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>5.2.4</version>
</dependency>

1.2 然后添加配置文件

spring:
  flyway:
    # 是否启用flyway
    enabled: true
    # 编码格式，默认UTF-8
    encoding: UTF-8
    # 迁移sql脚本文件存放路径，默认db/migration
    locations: classpath:db/migration
    # 迁移sql脚本文件名称的前缀，默认V
    sql-migration-prefix: V
    # 迁移sql脚本文件名称的分隔符，默认2个下划线__
    sql-migration-separator: __
    # 迁移sql脚本文件名称的后缀
    sql-migration-suffixes: .sql
    # 迁移时是否进行校验，默认true
    validate-on-migrate: true
    # 当迁移发现数据库非空且存在没有元数据的表时，自动执行基准迁移，新建schema_version表
    baseline-on-migrate: true

1.3 测试表sql

CREATE TABLE `test`  (
                         `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
                         `test_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '测试id',
                         `test_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '测试名',
                         `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
                         `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建者',
                         `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
                         `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新者',
                         `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
                         `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
                         PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

1.4 在迁移sql脚本文件存放路径，默认db/migration目录下添加sql脚本文件

格式：V20220922.14.51__Test_Init.sql （我这里用的是微服务作为示例，所以格式为 时间戳+模块名+用途名）

注意：以时间戳来命名有一个问题就是：V20220922.04.51__Test_Init.sql 可能会被认为 比 V20220922.14.51__Test_Init.sql 版本更（四声）新，时间上明明是04更早其实不然，‘.’前面的‘0’可能会被省略然后去做比较。

1.5 运行微服务控制台输出

控制台打印出如下信息即表示test库初始化执行成功了

14:46:03.192 [main] INFO  o.f.c.i.l.VersionPrinter - [info,49] - Flyway Community Edition 5.2.4 by Boxfuse
14:46:03.205 [main] INFO  o.f.c.i.d.DatabaseFactory - [info,49] - Database: jdbc:mysql://localhost:3306/ry_cloud (MySQL 8.0)
14:46:03.397 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Current version of schema `ry_cloud`: << Empty Schema >>
14:46:03.404 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Migrating schema `ry_cloud` to version 20220922.14.51 - Test Init
14:46:03.454 [main] WARN  o.f.c.i.s.DefaultSqlScriptExecutor - [warn,53] - DB: Integer display width is deprecated and will be removed in a future release. (SQL State: HY000 - Error Code: 1681)
14:46:03.484 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Successfully applied 1 migration to schema `ry_cloud` (execution time 00:00.152s)

1.6 查看数据库表flyway_schema_history是否有迁移数据，是否生成test表

2 flyway实现sql迭代

2.1 首先还是添加依赖

<!--mysql数据库版本管理控制器flyway-->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>5.2.4</version>
</dependency>

2.2 然后还是添加配置文件

spring:
  flyway:
    # 是否启用flyway
    enabled: true
    # 编码格式，默认UTF-8
    encoding: UTF-8
    # 迁移sql脚本文件存放路径，默认db/migration
    locations: classpath:db/migration
    # 迁移sql脚本文件名称的前缀，默认V
    sql-migration-prefix: V
    # 迁移sql脚本文件名称的分隔符，默认2个下划线__
    sql-migration-separator: __
    # 迁移sql脚本文件名称的后缀
    sql-migration-suffixes: .sql
    # 迁移时是否进行校验，默认true
    validate-on-migrate: true
    # 当迁移发现数据库非空且存在没有元数据的表时，自动执行基准迁移，新建schema_version表
    baseline-on-migrate: true

2.3 在迁移sql脚本文件存放路径，默认db/migration目录下添加sql脚本文件

2.3.1测试添加：

格式：V20220926.14.57__Test_Add.sql（我这里用的是微服务作为示例，所以格式为 时间戳+模块名+用途名）

ALTER TABLE `test` ADD COLUMN `test_add01` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'flyway测试添加字段' AFTER `remark`;

15:07:02.002 [main] INFO  o.f.c.i.l.VersionPrinter - [info,49] - Flyway Community Edition 5.2.4 by Boxfuse
15:07:02.016 [main] INFO  o.f.c.i.d.DatabaseFactory - [info,49] - Database: jdbc:mysql://localhost:3306/ry_cloud (MySQL 8.0)
15:07:02.219 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Current version of schema `ry_cloud`: 20220922.14.51
15:07:02.225 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Migrating schema `ry_cloud` to version 20220926.14.57 - Test Add
15:07:02.280 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Successfully applied 1 migration to schema `ry_cloud` (execution time 00:00.135s)

2.3.2测试修改：

ALTER TABLE `test` CHANGE `test_add01` `test_update01` varchar(50);

15:12:55.668 [main] INFO  o.f.c.i.l.VersionPrinter - [info,49] - Flyway Community Edition 5.2.4 by Boxfuse
15:12:55.682 [main] INFO  o.f.c.i.d.DatabaseFactory - [info,49] - Database: jdbc:mysql://localhost:3306/ry_cloud (MySQL 8.0)
15:12:55.882 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Current version of schema `ry_cloud`: 20220926.14.57
15:12:55.887 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Migrating schema `ry_cloud` to version 20220926.15.08 - Test Update
15:12:55.940 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Successfully applied 1 migration to schema `ry_cloud` (execution time 00:00.131s)

2.3.3测试删除：

ALTER TABLE `test` DROP COLUMN `test_update01`;

15:17:17.842 [main] INFO  o.f.c.i.l.VersionPrinter - [info,49] - Flyway Community Edition 5.2.4 by Boxfuse
15:17:17.856 [main] INFO  o.f.c.i.d.DatabaseFactory - [info,49] - Database: jdbc:mysql://localhost:3306/ry_cloud (MySQL 8.0)
15:17:18.075 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Current version of schema `ry_cloud`: 20220926.15.08
15:17:18.081 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Migrating schema `ry_cloud` to version 20220926.15.16 - Test Delete
15:17:18.132 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Successfully applied 1 migration to schema `ry_cloud` (execution time 00:00.137s)

注意：如果没有执行过的sql脚本有严格的执行顺序要求，如果执行过了最新版本的再去执行低版本的会直接 跳过 或者 报错 （关闭校验时跳过，没关闭时校验报错）

未执行过的低版本sql
V20220926.15.15__Test_Error.sql

15:24:19.082 [main] INFO  o.f.c.i.l.VersionPrinter - [info,49] - Flyway Community Edition 5.2.4 by Boxfuse
15:24:19.097 [main] INFO  o.f.c.i.d.DatabaseFactory - [info,49] - Database: jdbc:mysql://localhost:3306/ry_cloud (MySQL 8.0)
15:24:19.342 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Current version of schema `ry_cloud`: 20220926.15.16
15:24:19.343 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Schema `ry_cloud` is up to date. No migration necessary.

15:40:42.354 [main] WARN  o.s.b.w.s.c.AnnotationConfigServletWebServerApplicationContext - [refresh,591] - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.yxtp.common.flyway.FlywayConfig': Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: Validate failed: Detected resolved migration not applied to database: 20220926.15.15

还有其他的很多错误情况：

​ 例如在默认配置下，删除了sql脚本或者更改了，和数据库表flyway_schema_history checksum(校验和)这个字段对应不上就会报错

15:36:03.728 [main] INFO  o.f.c.i.l.VersionPrinter - [info,49] - Flyway Community Edition 5.2.4 by Boxfuse
15:36:03.740 [main] INFO  o.f.c.i.d.DatabaseFactory - [info,49] - Database: jdbc:mysql://localhost:3306/ry_cloud (MySQL 8.0)
15:36:03.954 [main] WARN  o.s.b.w.s.c.AnnotationConfigServletWebServerApplicationContext - [refresh,591] - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.yxtp.common.flyway.FlywayConfig': Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: Validate failed: Migration checksum mismatch for migration version 20220926.14.57
-> Applied to database : -106777664
-> Resolved locally    : 2114797040

# 迁移时是否进行校验，默认true
validate-on-migrate: false

#设置为false可以关闭校验也就是不对checksum(校验和)这个字段进行校验了（具体的原理可以自己研究一下）

二 、依赖+代码自定义配置

2.1 首先需要添加依赖

<!--mysql数据库版本管理控制器flyway-->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>5.2.4</version>
</dependency>

2.2 然后修改配置文件

关闭flyway开关，不然默认还是会启动

spring:
  flyway:
    # 是否启用flyway
    enabled: false
    # 编码格式，默认UTF-8
    encoding: UTF-8
    # 迁移sql脚本文件存放路径，默认db/migration
    locations: classpath:db/migration
    # 迁移sql脚本文件名称的前缀，默认V
    sql-migration-prefix: V
    # 迁移sql脚本文件名称的分隔符，默认2个下划线__
    sql-migration-separator: __
    # 迁移sql脚本文件名称的后缀
    sql-migration-suffixes: .sql
    # 迁移时是否进行校验，默认true
    validate-on-migrate: true
    # 当迁移发现数据库非空且存在没有元数据的表时，自动执行基准迁移，新建schema_version表
    baseline-on-migrate: true

2.3 添加自定义flyway配置类

package com.yxtp.common.flyway;

import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

/**
 * 【 flyway配置 自动生成flyway_schema_history表 】
 *
 * @Date 2022/9/22
 * @Author xin yi
 */
@Configuration
//@Order(Integer.MIN_VALUE)/*这个注解百来的解决奇怪的问题遇到可以放开*/
public class FlywayConfig {

    @Autowired
    private DataSource dataSource;

    @PostConstruct
    public void migrate() {
        Flyway flyway = Flyway.configure()
                /*可以在这里跟配置文件一样添加配置*/
                .baselineOnMigrate(true)
                .ignoreIgnoredMigrations(false)
                .validateOnMigrate(true)
                .dataSource(dataSource)
                .load();
                flyway.migrate();
    }
}

2.4 引用flyway公共模块

其余模块引用该模块就不用每个地方都配置一次了

<!--mysql数据库版本管理控制器flyway-->
<dependency>
    <groupId>com.ruoyi</groupId>
    <artifactId>yxtp-common-flyway</artifactId>
    <version>3.6.0</version>
</dependency>

微服务记得install

2.5 flyway实现

所以问题就来了，那么操作都一样的，为什么还要自定义这么麻烦呢，大部分的情况没有问题是因为都是没有比flyway更早的需要对数据库进行操作，假如定时任务类的方法，需要提前对数据库进行操作的，flyway初始化的时候是不是就不行了，因为还没有初始化就要操作数据库表，显然是不行的，所以自定义就给了我们发挥的空间。

2.6 控制bean执行顺序

控制执行顺序 Flyway -> Scheduler
注解：@DependsOn({“FlywayConfig”})

同理适用于其它需要控制bean执行顺序的情况

package com.ruoyi.job.config;

import java.util.Properties;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.scheduling.quartz.SchedulerFactoryBean;

/**
 * 定时任务配置（单机部署建议删除此类和qrtz数据库表，默认走内存会最高效）
 * 
 * @author ruoyi
 */
@DependsOn({"FlywayConfig"})
@Configuration
public class ScheduleConfig
{
    @Bean
    public SchedulerFactoryBean schedulerFactoryBean(DataSource dataSource)
    {
        SchedulerFactoryBean factory = new SchedulerFactoryBean();
        factory.setDataSource(dataSource);

        // quartz参数
        Properties prop = new Properties();
        prop.put("org.quartz.scheduler.instanceName", "RuoyiScheduler");
        prop.put("org.quartz.scheduler.instanceId", "AUTO");
        // 线程池配置
        prop.put("org.quartz.threadPool.class", "org.quartz.simpl.SimpleThreadPool");
        prop.put("org.quartz.threadPool.threadCount", "20");
        prop.put("org.quartz.threadPool.threadPriority", "5");
        // JobStore配置
        prop.put("org.quartz.jobStore.class", "org.springframework.scheduling.quartz.LocalDataSourceJobStore");
        // 集群配置
        prop.put("org.quartz.jobStore.isClustered", "true");
        prop.put("org.quartz.jobStore.clusterCheckinInterval", "15000");
        prop.put("org.quartz.jobStore.maxMisfiresToHandleAtATime", "1");
        prop.put("org.quartz.jobStore.txIsolationLevelSerializable", "true");

        // sqlserver 启用
        // prop.put("org.quartz.jobStore.selectWithLockSQL", "SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?");
        prop.put("org.quartz.jobStore.misfireThreshold", "12000");
        prop.put("org.quartz.jobStore.tablePrefix", "QRTZ_");
        factory.setQuartzProperties(prop);

        factory.setSchedulerName("RuoyiScheduler");
        // 延时启动
        factory.setStartupDelay(1);
        factory.setApplicationContextSchedulerContextKey("applicationContextKey");
        // 可选，QuartzScheduler
        // 启动时更新己存在的Job，这样就不用每次修改targetObject后删除qrtz_job_details表对应记录了
        factory.setOverwriteExistingJobs(true);
        // 设置自动启动，默认为true
        factory.setAutoStartup(true);

        return factory;
    }
}

16:15:55.780 [main] INFO  o.f.c.i.l.VersionPrinter - [info,49] - Flyway Community Edition 5.2.4 by Boxfuse
16:15:55.794 [main] INFO  c.z.h.HikariDataSource - [getConnection,110] - HikariPool-1 - Starting...
16:15:57.940 [main] INFO  c.z.h.HikariDataSource - [getConnection,123] - HikariPool-1 - Start completed.
16:15:57.952 [main] INFO  o.f.c.i.d.DatabaseFactory - [info,49] - Database: jdbc:mysql://localhost:3306/ry_cloud (MySQL 8.0)
16:15:58.156 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Current version of schema `ry_cloud`: 20220926.15.16
16:15:58.162 [main] INFO  o.f.c.i.c.DbMigrate - [info,49] - Migrating schema `ry_cloud` to version 20220926.16.15 - Job Init

  Scheduler class: 'org.quartz.core.QuartzScheduler' - running locally.
  NOT STARTED.
  Currently in standby mode.
  Number of jobs executed: 0
  Using thread pool 'org.quartz.simpl.SimpleThreadPool' - with 20 threads.
  Using job-store 'org.springframework.scheduling.quartz.LocalDataSourceJobStore' - which supports persistence. and is clustered.
926.16.15 - Job Init

  Scheduler class: 'org.quartz.core.QuartzScheduler' - running locally.
  NOT STARTED.
  Currently in standby mode.
  Number of jobs executed: 0
  Using thread pool 'org.quartz.simpl.SimpleThreadPool' - with 20 threads.
  Using job-store 'org.springframework.scheduling.quartz.LocalDataSourceJobStore' - which supports persistence. and is clustered.