SpringBoot三十四：Flyway管理数据库脚本

官网： https://flywaydb.org/

什么是Flyway?

Flyway是一款开源的数据库版本管理工具，它更倾向于规约优于配置的方式。Flyway可以独立于应用实现管理并跟踪数据库变更，支持数据库版本自动升级，并且有一套默认的规约，不需要复杂的配置，Migrations可以写成SQL脚本，也可以写在Java代码中，不仅支持Command Line和Java API，还支持Build构建工具和Spring Boot等，同时在分布式环境下能够安全可靠地升级数据库，同时也支持失败恢复等。

支持的数据库包括：Oracle, SQL Server (including Amazon RDS and Azure SQL Database), DB2, MySQL (including Amazon RDS, Azure Database & Google Cloud SQL), Aurora MySQL, MariaDB, Percona XtraDB Cluster, PostgreSQL (including Amazon RDS, Azure Database, Google Cloud SQL & Heroku), Aurora PostgreSQL, Redshift, CockroachDB, SAP HANA, Sybase ASE, Informix, H2, HSQLDB, Derby, Snowflake, SQLite and Firebird.

Flyway 知识补充

1. Flyway 默认会去读取 classpath:db/migration，可以通过 spring.flyway.locations去指定自定义路径，多个路径使用半角英文逗号分隔，内部资源使用 classpath:，外部资源使用 file:
2. 如果项目初期没有数据库文件，但是又引用了 Flyway，那么在项目启动的时候，Flyway 会去检查是否存在 SQL文件，此时你需要将这个检查关闭，spring.flyway.check-location = false
3. Flyway 会在项目初次启动的时候创建一张名为 flyway_schema_history的表，在这张表里记录数据库脚本执行的历史记录，当然，你可以通过 spring.flyway.table 去修改这个值
4. Flyway 执行的 SQL 脚本必须遵循一种命名规则，V__.sql 首先是 V，然后是版本号，如果版本号有多个数字，使用_分隔，比如1_0、1_1，版本号的后面是 2 个下划线，最后是 SQL 脚本的名称。
   注意：V 开头的只会执行一次，下次项目启动不会执行，也不可以修改原始文件，否则项目启动会报错，如果需要对 V 开头的脚本做修改，需要清空flyway_schema_history表，如果有个 SQL 脚本需要在每次启动的时候都执行，那么将 V 改为 R 开头即可
5. Flyway 默认情况下会去清空原始库，再重新执行 SQL脚本，这在生产环境下是不可取的，因此需要将这个配置关闭，spring.flyway.clean-disabled = true

集成Flyway

pom.xml引入依赖

Flyway 依赖

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

初始化表结构，需要操作数据库，因此引入数据库驱动以及数据源依赖（这里用 spring-boot-starter-data-jdbc）

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>

application.yml配置

spring:
  datasource:
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/javakf_test1?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
    username: root
    password: 123456
  flyway:
    locations: db/init # 数据库脚本路径
    table: db_upgrade # 指定数据脚本执行的历史记录表（自动创建）
    check-location: false # 校验路径下是否存在sql脚本
    baseline-on-migrate: true # 最开始数据库已经存在表结构，且不存在db_upgrade表时，需要设置为 true
    baseline-version: 0 # 基础版本

测试

测试 1.0 版本的 SQL 脚本
创建 V1_0__sys_user.sql

DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL > COMMENT '用户名',
  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4276 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '系统用户' ROW_FORMAT = Dynamic;

启动项目，可以看到日志输出

2020-05-22 11:28:58.214  INFO 12184 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 5.2.4 by Boxfuse
2020-05-22 11:28:58.221  INFO 12184 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2020-05-22 11:28:58.365  INFO 12184 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2020-05-22 11:28:58.368  INFO 12184 --- [           main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:mysql://localhost:3306/javakf_test1 (MySQL 5.5)
2020-05-22 11:28:58.444  INFO 12184 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.017s)
2020-05-22 11:28:58.513  INFO 12184 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table: `javakf_test1`.`db_upgrade`
2020-05-22 11:28:58.741  INFO 12184 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema `javakf_test1`: << Empty Schema >>
2020-05-22 11:28:58.744  INFO 12184 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema `javakf_test1` to version 1.0 - sys user
2020-05-22 11:28:58.756  WARN 12184 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: Unknown table 'sys_user' (SQL State: 42S02 - Error Code: 1051)
2020-05-22 11:28:58.832  WARN 12184 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. (SQL State: HY000 - Error Code: 1478)
2020-05-22 11:28:58.833  WARN 12184 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: InnoDB: assuming ROW_FORMAT=COMPACT. (SQL State: HY000 - Error Code: 1478)
2020-05-22 11:28:58.890  INFO 12184 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema `javakf_test1` (execution time 00:00.377s)
2020-05-22 11:28:59.050  INFO 12184 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2020-05-22 11:28:59.052  INFO 12184 --- [           main] cn.com.javakf.flyway.Application         : Started Application in 3.515 seconds (JVM running for 3.974)

查看数据库，创建了 2 张表

数据脚本执行的历史记录

测试 1.1 版本的 SQL 脚本
创建 V1_1__sys_menu.sql

DROP TABLE IF EXISTS `sys_menu`;
CREATE TABLE `sys_menu`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) NULL DEFAULT NULL COMMENT '父菜单ID，一级菜单为0',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜单名称',
  `url` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜单URL',
  `perms` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '授权(多个用逗号分隔，如：user:list,user:create)',
  `type` int(11) NULL DEFAULT NULL COMMENT '类型   0：目录   1：菜单   2：按钮',
  `icon` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜单图标',
  `order_num` int(11) NULL DEFAULT NULL COMMENT '排序',
  `is_active` tinyint(1) NULL DEFAULT 1 COMMENT '0 禁用 1 启用',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 31 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '菜单管理' ROW_FORMAT = Dynamic;

启动项目，可以看到日志输出

2020-05-22 11:36:36.785  INFO 2404 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 5.2.4 by Boxfuse
2020-05-22 11:36:36.792  INFO 2404 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2020-05-22 11:36:36.943  INFO 2404 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2020-05-22 11:36:36.946  INFO 2404 --- [           main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:mysql://localhost:3306/javakf_test1 (MySQL 5.5)
2020-05-22 11:36:37.029  INFO 2404 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 2 migrations (execution time 00:00.024s)
2020-05-22 11:36:37.039  INFO 2404 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema `javakf_test1`: 1.0
2020-05-22 11:36:37.040  INFO 2404 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema `javakf_test1` to version 1.1 - sys menu
2020-05-22 11:36:37.044  WARN 2404 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: Unknown table 'sys_menu' (SQL State: 42S02 - Error Code: 1051)
2020-05-22 11:36:37.146  WARN 2404 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. (SQL State: HY000 - Error Code: 1478)
2020-05-22 11:36:37.146  WARN 2404 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: InnoDB: assuming ROW_FORMAT=COMPACT. (SQL State: HY000 - Error Code: 1478)
2020-05-22 11:36:37.175  INFO 2404 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema `javakf_test1` (execution time 00:00.139s)
2020-05-22 11:36:37.314  INFO 2404 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2020-05-22 11:36:37.316  INFO 2404 --- [           main] cn.com.javakf.flyway.Application         : Started Application in 3.336 seconds (JVM running for 3.751)

查看数据库，创建sys_menu表

数据脚本执行的历史记录

代码托管：springboot_flyway