Liquibase----SQL格式通过update更新MySQL数据库

【原文链接】

1 文件准备

因为操作MySQL数据库，因此，这里只需要将liquibase安装目录中的example文件中的sql文件夹拷贝出来即可，如：D:\ProgrameFile\liquibase-4.9.1\examples\sql

2 修改文件名

sql文件夹中只保留changelog.sql文件和liquibase.properties文件，其中changelog.sql由example-changelog.sql重命名而来

3 数据库准备

首先在要操作的MySQL数据库中创建demo数据库

4 编辑配置liquibase.properties文件

# Enter the path for your changelog file.
changeLogFile=changelog.sql

#### Enter the Target database 'url' information  ####
liquibase.command.url=jdbc:mysql://xx.xx.xx.xx:3306/demo

# Enter the username for your Target database.
liquibase.command.username: root

# Enter the password for your Target database.
liquibase.command.password: xxxxxxxxx

5 编辑changelog.sql文件

如下，创建两个表person和company，然后向person表增加一列country

--liquibase formatted sql

--changeset redrose2100:1
--comment: create table person
create table person (
    id int primary key auto_increment not null,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
)
--rollback DROP TABLE person;

--changeset redrose2100:2
--comment: create table company
create table company (
    id int primary key auto_increment not null,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
)
--rollback DROP TABLE company;

--changeset redrose2100:3
--comment: add column country to table person
alter table person add column country varchar(2)
--rollback ALTER TABLE person DROP COLUMN country;

6 打开cmd并进入到sql文件夹下

执行如下命令

liquibase update

执行结果如下：

D:\src\sql>liquibase update
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 09:59:17 (version 4.9.1 #1978 built at 2022-03-28 19:39+0000)
Liquibase Version: 4.9.1
Liquibase Community 4.9.1 by Liquibase
Do you want to see this operation's report in Liquibase Hub, which improves team collaboration?
If so, enter your email. If not, enter [N] to no longer be prompted, or [S] to skip for now, but ask again next time [S]:
N
No operations will be reported. Simply add a liquibase.hub.apiKey setting to generate free deployment reports. Learn more at https://hub.liquibase.com
* Updated properties file liquibase.properties to set liquibase.hub.mode=off
Running Changeset: changelog.sql::1::redrose2100
Running Changeset: changelog.sql::2::redrose2100
Running Changeset: changelog.sql::3::redrose2100
Liquibase command 'update' was executed successfully.

D:\src\sql>

7 进入数据库查看

如下，可以查看到对应的数据库已经创建成功，person表中也已经存在country列

mysql> use demo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------+
| Tables_in_demo        |
+-----------------------+
| DATABASECHANGELOG     |
| DATABASECHANGELOGLOCK |
| company               |
| person                |
+-----------------------+
4 rows in set (0.00 sec)

mysql>mysql> desc person;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50) | NO   |     | NULL    |                |
| address1 | varchar(50) | YES  |     | NULL    |                |
| address2 | varchar(50) | YES  |     | NULL    |                |
| city     | varchar(30) | YES  |     | NULL    |                |
| country  | varchar(2)  | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

8 通过命令行方式

这里可以不使用liquibase.properties文件，可以通过使用命令行参数来指定配置，如下：

liquibase update --changelog-file=changelog.sql --url=jdbc:mysql://xx.xx.xx.xx:3306/demo --username=root --password=xxxxxxxx

如此时将changelog.sql文件增加以下修改内容，即向person表增加age一列

--changeset redrose2100:4
--comment: add column age to table person
alter table person add column age int(3)
--rollback ALTER TABLE person DROP COLUMN age;

执行回显如下：

D:\src\sql>liquibase update --changelog-file=changelog.sql --url=jdbc:mysql://xx.xx.xx.xx:3306/demo --username=root --password=xxxxxxxx
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 10:10:48 (version 4.9.1 #1978 built at 2022-03-28 19:39+0000)
Liquibase Version: 4.9.1
Liquibase Community 4.9.1 by Liquibase
Do you want to see this operation's report in Liquibase Hub, which improves team collaboration?
If so, enter your email. If not, enter [N] to no longer be prompted, or [S] to skip for now, but ask again next time [S]:
N
No operations will be reported. Simply add a liquibase.hub.apiKey setting to generate free deployment reports. Learn more at https://hub.liquibase.com
* Updated properties file liquibase.properties to set liquibase.hub.mode=off
Running Changeset: changelog.sql::4::redrose2100
Liquibase command 'update' was executed successfully.

D:\src\sql>

查看数据库中person表，已经存在age字段了

mysql> desc person;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50) | NO   |     | NULL    |                |
| address1 | varchar(50) | YES  |     | NULL    |                |
| address2 | varchar(50) | YES  |     | NULL    |                |
| city     | varchar(30) | YES  |     | NULL    |                |
| country  | varchar(2)  | YES  |     | NULL    |                |
| age      | int(3)      | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql>

可能遇到的问题

• 出现如下错误，因为没安装数据库驱动
  如下表示liquibase缺少mysql驱动

Unexpected error running Liquibase: java.lang.RuntimeException: Cannot find database driver: com.mysql.jdbc.Driver

解决办法：
下载 mysql-connector-java-5.1.36.jar 包，具体版本根据数据库版本选择，然后放入liquibase的安装目录，这里如：D:\ProgrameFile\liquibase-4.9.1\lib