flyway和liquibase的使用样例

--转载 https://www.cnblogs.com/liuyupen/p/11101594.html

在代码上我们有svn和git等诸多的版本控制方法。

但是在数据库上却没有相应的工具。一度导致多环境见的数据库同步难以维持。

flyway和liquibase都是常见的数据库版本控制工具。

flyway社区版的功能相对完全免费的liquibase来说简直毫无可比性。

因此免费用户的话强烈liquibase。

样例代码已上传至Github：https://github.com/hackyoMa/changedb，样例基于Spring Boot。

flyway：

application.yml

spring:
  datasource:
    platform: mysql
    url: jdbc:mysql://xxx:3306/xxx?useUnicode=true&characterEncoding=utf8&useSSL=false
    username: xxx
    password: xxx
    driver-class-name: com.mysql.jdbc.Driver
  jpa:
    database: MYSQL
    show-sql: true
    hibernate:
      ddl-auto: none
  flyway:
    locations: classpath:db/migration

resourcesdbmigration目录下文件：

V1_0__createBook.sql

CREATE TABLE `book`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `create_time` datetime(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
CREATE TABLE `test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
CREATE TRIGGER book_trigger AFTER INSERT ON book FOR EACH ROW BEGIN
  INSERT INTO test (`name`) VALUES ('1');
END;

V1_1__insertBook.sql

INSERT INTO book(name, create_time) VALUES ('测试', '2018-04-21 16:53:48');

liquibase：

application.yml

spring:
  datasource:
    platform: mysql
    url: jdbc:mysql://xxx:3306/xxx?useUnicode=true&characterEncoding=utf8&useSSL=false
    username: xxx
    password: xxx
    driver-class-name: com.mysql.jdbc.Driver
  jpa:
    database: MYSQL
    show-sql: true
    hibernate:
      ddl-auto: none
  liquibase:
    change-log: classpath:db/changelog/db.changelog-master.xml

resourcesdbchangelog目录下文件：

db.changelog-1.0.sql

--liquibase formatted sql
--changeset mahaoyu:1.0
CREATE TABLE `book`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `create_time` datetime(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
--changeset mahaoyu:1.1
CREATE TABLE `test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

db.changelog-1.1.sql

--liquibase formatted sql
--changeset mahaoyu:1.2 splitStatements:false
CREATE TRIGGER book_trigger AFTER INSERT ON book FOR EACH ROW BEGIN
  INSERT INTO test (`name`) VALUES ('1');
END;
--changeset mahaoyu:1.3 splitStatements:false
CREATE PROCEDURE `proc_adder` ( IN a INT, IN b INT, OUT sum INT ) BEGIN
    DECLARE
        c INT;
    IF
        a IS NULL THEN
            SET a = 0;
    END IF;
    IF
        b IS NULL THEN
            SET b = 0;
    END IF;
    SET sum = a + b;
END;
--changeset mahaoyu:1.4
CREATE VIEW Oceania AS SELECT * FROM book;
--changeset mahaoyu:1.5 splitStatements:false
CREATE FUNCTION hello ( ) RETURNS VARCHAR ( 255 ) BEGIN
RETURN 'Hello  world,i am mysql';
END;
--changeset zhangsan:1.6
INSERT INTO book(name, create_time) VALUES ('测试', '2018-04-21 16:53:48');

db.changelog-master.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <include file="db/changelog/db.changelog-1.0.sql"/>
    <include file="db/changelog/db.changelog-1.1.sql"/>

</databaseChangeLog>

这里对liquibase的事务、触发器等进行了示例。另外liquibase还支持xml语法，以增加对不同数据库的支持。