Flyway使用说明

1.什么是 Flyway

我们在公司做开发时，由于项目需求的变化，或者前期设计缺陷，导致在后期需要修改数据库，这应该是一个比较常见的事情，如果项目还没上线，你可能把表删除了重新创建，但是如果项目已经上线了，就不能这样简单粗暴了，我们需要通过 SQL 脚本在已有数据表的基础上进行升级。

接下来我们就来看看用 Flyway，然后再来说说 Flyway 的一个大致原理。

2.嵌入到服务

项目创建成功后，resources目录下也会多出来一个db/migration目录，这个目录用来存放数据库脚本，如下：

这个如果创建项目时就选择了 Flyway 依赖，就会有这个目录。现在我要在已经做好的微人事中加入 Flyway，这个目录就需要我手动创建了。

首先在微人事中添加 flyway 依赖：org.flywaydbflyway-core，然后在 vhr-web 模块下的 resources 目录下，手动创建 db/migration 目录，然后在该目录下创建数据库脚本，数据库脚本的命名方式如下：

V__.sql首先是大写字母 V，然后是版本号，要是有小版本可以用下划线隔开，例如 2_1，版本号后面是两个下划线，然后是脚本名称，文件后缀是 .sql。

例如我这里创建我的第一个数据库脚本，取名为V1__DML_210806_01.sql。

完了之后，可以不用添加额外配置，大家只需要在本地 MySQL 中创建一个空的 vhr 数据库即可，然后直接启动微人事项目，项目启动成功后从启动日志中，我们可以看到 Flyway 的执行信息，数据库脚本的执行执行，同时这里还说了，Flyway 还给创建了一个 flyway_schema_history 表，这个表用来记录数据库的更新历史。

这个时候，打开本地数据库，我们发现库中该有的表都有了。同时还发现了 flyway_schema_history 表，如下：

有了这条记录，下次再启动 vhr 项目，V1__DML_210806_01.sql 这个脚本文件就不会执行了，因为系统知道这个脚本已经执行过了，如果你还想让 V1__DML_210806_01.sql 脚本再执行一遍，需要手动删除 flyway_schema_history 表中的对应记录，那么项目启动时，这个脚本就会被执行了。

3.执行细节

我们在定义脚本的时候，除了 V 字开头的脚本之外，还有一种 R 字开头的脚本，V 字开头的脚本只会执行一次，而 R 字开头的脚本，只要脚本内容发生了变化，启动时候就会执行。使用了 Flyway 之后，如果再想进行数据库版本升级，就不用该以前的数据库脚本了，直接创建新的数据库脚本，项目在启动时检测了有新的更高版本的脚本，就会自动执行，这样，在和其他同事配合工作时，也会方便很多。因为正常我们都是从 Git 上拉代码下来，不拉数据库脚本，这样要是有人更新了数据库，其他同事不一定能够收到最新的通知，使用了 Flyway 就可以有效避免这个问题了。所有的脚本，一旦执行了，就会在 flyway_schema_history 表中有记录，如果你不小心搞错了，可以手动从 flyway_schema_history 表中删除记录，然后修改 SQL 脚本后再重新启动（生产环境不建议）。

4.其他配置

在 Spring Boot 中，关于 Flyway 也有不少配置，这些配置都在 application.properties 中进行配置，常用的几个来和大家说下：

spring.flyway.enabled：是否开启 flyway，默认就是开启的spring.flyway.encoding：flyway 字符编码spring.flyway.locations：sql 脚本的目录，默认是 classpath:db/migration，如果有多个，用 , 隔开spring.flyway.clean-disabled：这个属性非常关键，它表示是否要清除已有库下的表，如果执行的脚本是 V1__xxx.sql，那么会先清除已有库下的表，然后再执行脚本，这在开发环境下还挺方便，但是在生产环境下就要命了，而且它默认就是要清除，生产环境一定要自己配置设置为 true。spring.flyway.table：配置数据库信息表的名称，默认是 flyway_schema_history。

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

5.项目添加实施：

6.Pom文件引入：

<dependency>
   <groupId>org.flywaydb</groupId>
   <artifactId>flyway-core</artifactId>
</dependency>

7.本地试运行

7.1 application.properties

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

7.2 pom.xml

<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-core</artifactId>
</dependency>

<plugin>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-maven-plugin</artifactId>
</plugin>

7.3 resources

添加需要执行的SQL（按照上述命名规范），例如

INSERT INTO `test`.`t_user_vaccination`(`id`, `user_id`, `actual_name`, `identity_number`, `mobile`, `type`, `street_name`, `collective_name`, `detailed_address`, `building_number`, `unit_number`, `house_number`, `member_number`, `created_by`, `created_date`, `last_modified_by`, `last_modified_date`, `remarks`, `del_flag`, `version`, `disabled_date`)
VALUES (5, 21080603, '张三', '22072319960403061X', '13666666666', 1, '街道名称', '所在集体名称', '所在集体详细地址', '所在集体楼号', '所在楼单元号', '所在单元室号', 1, '11005304', '2021-08-06 16:36:25', '11005304', '2021-08-06 14:10:42', NULL, 1, 0, NULL);

7.4 创建flyway_schema_history

CREATE TABLE `test`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    CONSTRAINT `flyway_schema_history_pk` PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB

7.5 flyway_schema_history记录

本地启动后，执行SQL，并生成记录

查看数据

8.常见问题

8.1 文件路径无法访问

8.2 创建flyway_schema_history失败

11:24:20.300 [main] INFO  o.f.c.i.database.DatabaseFactory - Database: jdbc:mysql://10.0.53.201:3306/ziguang-smart-city-vaccine (MySQL 5.7)
11:24:20.334 [main] INFO  o.f.core.internal.command.DbValidate - Successfully validated 1 migration (execution time 00:00.016s)
11:24:21.329 [main] INFO  o.f.c.i.s.JdbcTableSchemaHistory - Creating Schema History table `ziguang-smart-city-vaccine`.`flyway_schema_history` with baseline ...
11:24:21.372 [main] WARN  o.f.c.i.s.DefaultSqlScriptExecutor - DB: Statement violates GTID consistency: CREATE TABLE ... SELECT. (SQL State: HY000 - Error Code: 1786)
11:24:22.381 [main] WARN  o.f.c.i.s.DefaultSqlScriptExecutor - DB: Statement violates GTID consistency: CREATE TABLE ... SELECT. (SQL State: HY000 - Error Code: 1786)
11:24:23.402 [main] WARN  o.f.c.i.s.DefaultSqlScriptExecutor - DB: Statement violates GTID consistency: CREATE TABLE ... SELECT. (SQL State: HY000 - Error Code: 1786)
11:24:24.415 [main] WARN  o.f.c.i.s.DefaultSqlScriptExecutor - DB: Statement violates GTID consistency: CREATE TABLE ... SELECT. (SQL State: HY000 - Error Code: 1786)
11:24:25.422 [main] WARN  o.f.c.i.s.DefaultSqlScriptExecutor - DB: Statement violates GTID consistency: CREATE TABLE ... SELECT. (SQL State: HY000 - Error Code: 1786)
11:24:26.440 [main] WARN  o.f.c.i.s.DefaultSqlScriptExecutor - DB: Statement violates GTID consistency: CREATE TABLE ... SELECT. (SQL State: HY000 - Error Code: 1786)
11:24:27.461 [main] WARN  o.f.c.i.s.DefaultSqlScriptExecutor - DB: Statement violates GTID consistency: CREATE TABLE ... SELECT. (SQL State: HY000 - Error Code: 1786)
11:24:28.477 [main] WARN  o.f.c.i.s.DefaultSqlScriptExecutor - DB: Statement violates GTID consistency: CREATE TABLE ... SELECT. (SQL State: HY000 - Error Code: 1786)
11:24:29.483 [main] WARN  o.f.c.i.s.DefaultSqlScriptExecutor - DB: Statement violates GTID consistency: CREATE TABLE ... SELECT. (SQL State: HY000 - Error Code: 1786)
11:24:30.494 [main] WARN  o.f.c.i.s.DefaultSqlScriptExecutor - DB: Statement violates GTID consistency: CREATE TABLE ... SELECT. (SQL State: HY000 - Error Code: 1786)
11:24:30.501 [main] WARN  o.s.b.w.s.c.AnnotationConfigServletWebServerApplicationContext - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: 
Migration  failed
-----------------
SQL State  : HY000
Error Code : 1786
Message    : Statement violates GTID consistency: CREATE TABLE ... SELECT.
Location   :  ()
Line       : 1
Statement  : CREATE TABLE `test`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    CONSTRAINT `flyway_schema_history_pk` PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB AS SELECT     1 as "installed_rank",     '1' as "version",     '<< Flyway Baseline >>' as "description",     'BASELINE' as "type",     '<< Flyway Baseline >>' as "script",     NULL as "checksum",     'root' as "installed_by",     CURRENT_TIMESTAMP as "installed_on",     0 as "execution_time",     TRUE as "success"


11:24:30.501 [main] INFO  o.s.s.c.ThreadPoolTaskExecutor - Shutting down ExecutorService 'applicationTaskExecutor'
11:24:30.519 [main] INFO  c.alibaba.druid.pool.DruidDataSource - {dataSource-1} closing ...
11:24:30.527 [main] INFO  c.alibaba.druid.pool.DruidDataSource - {dataSource-1} closed
11:24:30.528 [main] INFO  o.a.catalina.core.StandardService - Stopping service [Tomcat]
11:24:30.545 [main] INFO  o.s.b.a.l.ConditionEvaluationReportLoggingListener - 

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
11:24:30.553 [main] ERROR o.s.boot.SpringApplication - Application run failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: 
Migration  failed
-----------------
SQL State  : HY000
Error Code : 1786
Message    : Statement violates GTID consistency: CREATE TABLE ... SELECT.
Location   :  ()
Line       : 1
Statement  : CREATE TABLE `test`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    CONSTRAINT `flyway_schema_history_pk` PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB AS SELECT     1 as "installed_rank",     '1' as "version",     '<< Flyway Baseline >>' as "description",     'BASELINE' as "type",     '<< Flyway Baseline >>' as "script",     NULL as "checksum",     'root' as "installed_by",     CURRENT_TIMESTAMP as "installed_on",     0 as "execution_time",     TRUE as "success"


	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1796)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:882)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:878)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550)
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747)
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:315)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215)
	at com.ziguang.spring.vaccine.SmartCityStatisticsVaccineApplication.main(SmartCityStatisticsVaccineApplication.java:29)
Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: 
Migration  failed
-----------------
SQL State  : HY000
Error Code : 1786
Message    : Statement violates GTID consistency: CREATE TABLE ... SELECT.
Location   :  ()
Line       : 1
Statement  : CREATE TABLE `test`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    CONSTRAINT `flyway_schema_history_pk` PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB AS SELECT     1 as "installed_rank",     '1' as "version",     '<< Flyway Baseline >>' as "description",     'BASELINE' as "type",     '<< Flyway Baseline >>' as "script",     NULL as "checksum",     'root' as "installed_by",     CURRENT_TIMESTAMP as "installed_on",     0 as "execution_time",     TRUE as "success"


	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:274)
	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:220)
	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:127)
	at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory$1$1.call(JdbcTableSchemaHistory.java:113)
	at org.flywaydb.core.internal.jdbc.TransactionTemplate.execute(TransactionTemplate.java:74)
	at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory$1.call(JdbcTableSchemaHistory.java:106)
	at org.flywaydb.core.internal.database.mysql.MySQLNamedLockTemplate.execute(MySQLNamedLockTemplate.java:60)
	at org.flywaydb.core.internal.database.mysql.MySQLConnection.lock(MySQLConnection.java:161)
	at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.create(JdbcTableSchemaHistory.java:97)
	at org.flywaydb.core.internal.command.DbBaseline.baseline(DbBaseline.java:77)
	at org.flywaydb.core.Flyway.doBaseline(Flyway.java:196)
	at org.flywaydb.core.Flyway.access$200(Flyway.java:79)
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:172)
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:149)
	at org.flywaydb.core.Flyway.execute(Flyway.java:511)
	at org.flywaydb.core.Flyway.migrate(Flyway.java:149)
	at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:65)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792)
	... 18 common frames omitted
Caused by: java.sql.SQLException: Statement violates GTID consistency: CREATE TABLE ... SELECT.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:762)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:646)
	at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:632)
	at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:244)
	at org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:111)
	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:208)
	... 35 common frames omitted
Disconnected from the target VM, address: '127.0.0.1:61727', transport: 'socket'

Process finished with exit code 1

8.2.1 情况描述

在执行sql：create table 表A as select * from 表B时，发现sql执行后，并未生成新的表，而是提示Statement violates GTID consistency: CREATE TABLE ... SELECT.

8.2.2 问题分析

MySQL5.6及以上的版本，开启了 enforce_gtid_consistency=true 功能导致的，MySQL官方解释说当启用 enforce_gtid_consistency 功能的时候，MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporarytable语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行。

8.2.3 解决方法

8.2.3.1 方法一：

修改 ：SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = off;

配置文件中 ：ENFORCE_GTID_CONSISTENCY = off;

8.2.3.2 方法二（推荐）：

create table 表A as select 表B 的方式会拆分成两部分。

create table 表A like 表B ;
insert into 表A select *from 表B ;

CREATE TABLE `test`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    CONSTRAINT `flyway_schema_history_pk` PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB AS SELECT     1 as "installed_rank",     '1' as "version",     '<< Flyway Baseline >>' as "description",     'BASELINE' as "type",     '<< Flyway Baseline >>' as "script",     NULL as "checksum",     'root' as "installed_by",     CURRENT_TIMESTAMP as "installed_on",     0 as "execution_time",     TRUE as "success"