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:# 是否启用flywayenabled: true# 编码格式，默认UTF-8encoding: UTF-8# 迁移sql脚本文件存放路径，默认db/migrationlocations: classpath:db/migration# 迁移sql脚本文件名称的前缀，默认Vsql-migration-prefix: V# 迁移sql脚本文件名称的分隔符，默认2个下划线__sql-migration-separator: __# 迁移sql脚本文件名称的后缀sql-migration-suffixes: .sql# 迁移时是否进行校验，默认truevalidate-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:# 是否启用flywayenabled: true# 编码格式，默认UTF-8encoding: UTF-8# 迁移sql脚本文件存放路径，默认db/migrationlocations: classpath:db/migration# 迁移sql脚本文件名称的前缀，默认Vsql-migration-prefix: V# 迁移sql脚本文件名称的分隔符，默认2个下划线__sql-migration-separator: __# 迁移sql脚本文件名称的后缀sql-migration-suffixes: .sql# 迁移时是否进行校验，默认truevalidate-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:# 是否启用flywayenabled: false# 编码格式，默认UTF-8encoding: UTF-8# 迁移sql脚本文件存放路径，默认db/migrationlocations: classpath:db/migration# 迁移sql脚本文件名称的前缀，默认Vsql-migration-prefix: V# 迁移sql脚本文件名称的分隔符，默认2个下划线__sql-migration-separator: __# 迁移sql脚本文件名称的后缀sql-migration-suffixes: .sql# 迁移时是否进行校验，默认truevalidate-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 {@Autowiredprivate DataSource dataSource;@PostConstructpublic 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
{@Beanpublic 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);// 设置自动启动，默认为truefactory.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: 0Using 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: 0Using thread pool 'org.quartz.simpl.SimpleThreadPool' - with 20 threads.Using job-store 'org.springframework.scheduling.quartz.LocalDataSourceJobStore' - which supports persistence. and is clustered.
