liquibase常用命令总结
Usage: java -jar liquibase.jar [options] [command]
Standard Commands:
update Updates database to current version
updateSQL Writes SQL to update database to current
version to STDOUT
updateCount <num> Applies next NUM changes to the database
updateCountSQL <num> Writes SQL to apply next NUM changes
to the database
updateToTag <tag> Updates the database to the changeSet with the
specified tag
updateToTagSQL <tag> Writes (to standard out) the SQL to update to
the changeSet with the specified tag
rollback <tag> Rolls back the database to the the state is was
when the tag was applied
rollbackSQL <tag> Writes SQL to roll back the database to that
state it was in when the tag was applied
to STDOUT
rollbackToDate <date/time> Rolls back the database to the the state is was
at the given date/time.
Date Format: yyyy-MM-dd'T'HH:mm:ss
rollbackToDateSQL <date/time> Writes SQL to roll back the database to that
state it was in at the given date/time version
to STDOUT
rollbackCount <value> Rolls back the last <value> change sets
applied to the database
rollbackCountSQL <value> Writes SQL to roll back the last
<value> change sets to STDOUT
applied to the database
futureRollbackSQL Writes SQL to roll back the database to the
current state after the changes in the
changeslog have been applied
futureRollbackSQL <value> Writes SQL to roll back the database to the
current state after <value> changes in the
changeslog have been applied
futureRollbackFromTagSQL <tag> Writes (to standard out) the SQL to roll back
the database to its current state after the
changes up to the specified tag have been
applied
updateTestingRollback Updates database, then rolls back changes before
updating again. Useful for testing
rollback support
generateChangeLog Writes Change Log XML to copy the current state
of the database to standard out
snapshot Writes the current state
of the database to standard out
snapshotReference Writes the current state
of the referenceUrl database to standard out
Diff Commands
diff [diff parameters] Writes description of differences
to standard out
diffChangeLog [diff parameters] Writes Change Log XML to update
the database
to the reference database to standard out
Documentation Commands
dbDoc <outputDirectory> Generates Javadoc-like documentation
based on current database and change log
Maintenance Commands
tag <tag string> 'Tags' the current database state for future rollback
tagExists <tag string> Checks whether the given tag is already existing
status [--verbose] Outputs count (list if --verbose) of unrun changesets
unexpectedChangeSets [--verbose]
Outputs count (list if --verbose) of changesets run
in the database that do not exist in the changelog.
validate Checks changelog for errors
calculateCheckSum <id> Calculates and prints a checksum for the changeset
with the given id in the format filepath::id::author.
clearCheckSums Removes all saved checksums from database log.
Useful for 'MD5Sum Check Failed' errors
changelogSync Mark all changes as executed in the database
changelogSyncSQL Writes SQL to mark all changes as executed
in the database to STDOUT
markNextChangeSetRan Mark the next change changes as executed
in the database
markNextChangeSetRanSQL Writes SQL to mark the next change
as executed in the database to STDOUT
listLocks Lists who currently has locks on the
database changelog
releaseLocks Releases all locks on the database changelog
dropAll Drop all database objects owned by user
Required Parameters:
--changeLogFile=<path and filename> Migration file
--username=<value> Database username
--password=<value> Database password. If values
is PROMPT, Liquibase will
prompt for a password
--url=<value> Database URL
Optional Parameters:
--classpath=<value> Classpath containing
migration files and JDBC Driver
--driver=<jdbc.driver.ClassName> Database driver class name
--databaseClass=<database.ClassName> custom liquibase.database.Database
implementation to use
--propertyProviderClass=<properties.ClassName> custom Properties
implementation to use
--defaultSchemaName=<name> Default database schema to use
--contexts=<value> ChangeSet contexts to execute
--labels=<expression> Expression defining labeled
ChangeSet to execute
--defaultsFile=</path/to/file.properties> File with default option values
(default: ./liquibase.properties)
--delimiter=<string> Used with executeSql command to set
the string used to break up files
that consist of multiple statements.
--driverPropertiesFile=</path/to/file.properties> File with custom properties
to be set on the JDBC connection
to be created
--changeExecListenerClass=<ChangeExecListener.ClassName> Custom Change Exec
listener implementation to use
--changeExecListenerPropertiesFile=</path/to/file.properties> Properties for
Custom Change Exec listener
--liquibaseCatalogName=<name> The name of the catalog with the
liquibase tables
--liquibaseSchemaName=<name> The name of the schema with the
liquibase tables
--databaseChangeLogTableName=<name> The name of the Liquibase ChangeLog
table (default: DATABASECHANGELOG)
--databaseChangeLogLockTableName=<name> The name of the Liquibase ChangeLog
Lock table
(default: DATABASECHANGELOGLOCK)
--liquibaseSchemaName=<name> The name of the schema with the
liquibase tables
--includeSystemClasspath=<true|false> Include the system classpath
in the Liquibase classpath
(default: true)
--promptForNonLocalDatabase=<true|false> Prompt if non-localhost
databases (default: false)
--logLevel=<level> Execution log level
(debug, info, warning, severe, off
--logFile=<file> Log file
--currentDateTimeFunction=<value> Overrides current date time function
used in SQL.
Useful for unsupported databases
--outputDefaultSchema=<true|false> If true, SQL object references
include the schema name, even if
it is the default schema.
Defaults to true
--outputDefaultCatalog=<true|false> If true, SQL object references
include the catalog name, even if
it is the default catalog.
Defaults to true
--outputFile=<file> File to write output to for commands
that write output, e.g. updateSQL.
If not specified, writes to sysout.
--help Prints this message
--version Prints this version information
Required Diff Parameters:
--referenceUsername=<value> Reference Database username
--referencePassword=<value> Reference Database password. If
value is PROMPT, Liquibase will
prompt for a password
--referenceUrl=<value> Reference Database URL
Optional Diff Parameters:
--defaultCatalogName=<name> Default database catalog to use
--defaultSchemaName=<name> Default database schema to use
--referenceDefaultCatalogName=<name> Reference database catalog to use
--referenceDefaultSchemaName=<name> Reference database schema to use
--schemas=<name1,name2> Database schemas to include
objects from in comparison
--referenceSchemas=<name1,name2> Reference database schemas to
include objects from in comparison
only needed if different than
--schemas
--outputSchemaAs=<name1,name2> On diffChangeLog/generateChangeLog,
use these names as schemaName
instead of the real names.
--includeCatalog=<true|false> If true, the catalog will be
included in generated changeSets
Defaults to false
--includeSchema=<true|false> If true, the schema will be
included in generated changeSets
Defaults to false
--referenceDriver=<jdbc.driver.ClassName> Reference database driver class name
--dataOutputDirectory=DIR Output data as CSV in the given
directory
--diffTypes List of diff types to include in
Change Log expressed as a comma
separated list from: tables, views,
columns, indexes, foreignkeys,
primarykeys, uniqueconstraints
data.
If this is null then the default
types will be: tables, views,
columns, indexes, foreignkeys,
primarykeys, uniqueconstraints.
Change Log Properties:
-D<property.name>=<property.value> Pass a name/value pair for
substitution in the change log(s)
Default value for parameters can be stored in a file called
'liquibase.properties' that is read from the current working directory.
Full documentation is available at
http://www.liquibase.org/documentation/command_line.html
Process finished with exit code 0
生成某个changeSet的md5Sum
md5Sum生成规则（md5Sum与filepath无关）
如果一个changeSet中有多个操作，如下：
<changeSet author="alexy" id="bizbase-20181113-1811" dbms="mysql">
<preConditions onFail="MARK_RAN">
<not>
<columnExists schemaName="${schema.dlsys}" tableName="tc_wf_trans_def" columnName="trans_priority" ></columnExists>
</not>
</preConditions>
<addColumn schemaName="${schema.dlsys}" tableName="tc_wf_trans_def">
<column name="trans_priority" type="int" defaultValue="-100"/>
</addColumn>
<addColumn schemaName="${schema.dlsys}" tableName="temp_trans_inst">
<column name="trans_priority" type="int"/>
</addColumn>
</changeSet>
则liquibase会为每个<addColumn>生成一个md5（这里有两个addColumn对象），然后拼接这两个对象的md5，再生成整个changeLog对象的md5Sum。
见：liquibase.changelog.ChangeSet#generateCheckSum
public CheckSum generateCheckSum() {
if (checkSum == null) {
StringBuffer stringToMD5 = new StringBuffer();
for (Change change : getChanges()) {
stringToMD5.append(change.generateCheckSum()).append(":");
}
for (SqlVisitor visitor : this.getSqlVisitors()) {
stringToMD5.append(visitor.generateCheckSum()).append(";");
}
checkSum = CheckSum.compute(stringToMD5.toString());
}
return checkSum;
}
注意：Change接口有generateCheckSu()方法有多个子类重写了。
changeSet的id规则
liquibase判断两个changset是否是同一个就是根据id来判断的。id是由“filepath::id::author”组成的。对应databasechangelog表中的以下字段：
logicalFilePath
假设有A和B两个项目，如果我想用B项目单独测试A项目的某个changeSet，我新建一个B/changelog.xml文件，存放待验证的changeset。但是 A项目的FilePath（changelog文件到路径为） A/a-changelog.xml 。而liquibase判断两个changset是否是同一个就是根据id来判断的，id是由“filepath::id::author”组成的，所以如果A和B的filepath（即changelog.xml文件路径）不同，则liquibase就不会认为这个同一个changeset了。所以，这就是logicalFilePath属性的意义。
可以放在<databaseChangeLog>标签内，即整个changelog.xml都会用这个作为逻辑地址。
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog logicalFilePath="META-INF/resources/bizbase-db-changelog.xml"
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
或者<changeSet>标签内：
<changeSet author="alexy" id="bizbase-20181113-1811" dbms="mysql" logicalFilePath="META-INF/resources/bizbase-db-changelog.xml">
<preConditions onFail="MARK_RAN">
<not>
<columnExists schemaName="${schema.dlsys}" tableName="tc_wf_trans_def" columnName="trans_priority" ></columnExists>
</not>
</preConditions>
<addColumn schemaName="${schema.dlsys}" tableName="tc_wf_trans_def">
<column name="trans_priority" type="int" defaultValue="-100"/>
</addColumn>
<addColumn schemaName="${schema.dlsys}" tableName="temp_trans_inst">
<column name="trans_priority" type="int"/>
</addColumn>
</changeSet>
问题1：修改changeSet，避免md5Sum校验问题
当一个changelog执行后，则会为在databasechangelog表中为每一个changeSet生成一个md5Sum。如果我们后面因为某些原因再次修改通一个changeSet就会有类似下面的报错：
2017-05-06 10:52:15,082 | ERROR | Error while applying liquibase changelogs db/changelog/wiki.db.changelog-1.0.0.xml - Cause : Validation Failed:
1 change sets check sum
db/changelog/wiki.db.changelog-1.0.0.xml::1.0.0-42::wiki is now: 7:21d239448c33f39a9300ea433349d470
这是因为liquibase每次启动都会将已执行的changeSet与changelog.xml中的id相同的changeSet进行md5Sum比较，如果你修改了某个changeSet，而导致md5Sum的改变就会有上面的报错了。
解决办法：
方法1.使用使用<validCheckSum>标签。
使用具体的md5Sum，该值既可以从报错中获得，也可以直接使用liquibase的calculateCheckSum命令来生成。
<changeSet id="1.0.0-42" author="wiki">
<validCheckSum>7:21d239448c33f39a9300ea433349d470</validCheckSum>
… // content of the changeset (with the update)
</changeSet>
或者
<changeSet id="1.0.0-42" author="wiki">
<validCheckSum>any</validCheckSum>
… // content of the changeset (with the update)
</changeSet>
或者
<changeSet id="1.0.0-42" author="wiki">
<validCheckSum>*</validCheckSum>
… // content of the changeset (with the update)
</changeSet>
或者
<changeSet id="1.0.0-42" author="wiki">
<validCheckSum>all</validCheckSum>
… // content of the changeset (with the update)
</changeSet>
方法2. 手动修改databasechangelog表中的数据
自己去databasechangelog表中，将对应的changeSet的md5Sum字段改为最新的md5值或者是把该字段清空。
官方的文档有些不是很详细，最好的办法是直接看Liquibase的源代码，下面附上liquibase中changeSet的md5Sum比对的代码和注释：
liquibase/changelog/ChangeSet.java：
public boolean isCheckSumValid(CheckSum storedCheckSum) {
//注意:storedCheckSum：databasechangelog表中该changeSet的md5Sum值。
//validCheckSums : 即<validCheckSum>标签的值所组成的列表，既然是列表，则说明一个changeSet元素可以添加多个<validCheckSum>标签
// no need to generate the checksum if any has been set as the valid checksum
for (CheckSum validCheckSum : validCheckSums) {
//如果<validCheckSum>标签的值为“any”，“all”或者“*”，则认为该changeSet没有修改过
if (validCheckSum.toString().equalsIgnoreCase("1:any") || validCheckSum.toString().equalsIgnoreCase("1:all") || validCheckSum.toString().equalsIgnoreCase("1:*")) {
return true;
}
}
//currentMd5Sum : 表示当前changeSet的md5Sum值
CheckSum currentMd5Sum = generateCheckSum();
//currentMd5Sum为空，则返回true
if (currentMd5Sum == null) {
return true;
}
//storedCheckSum（意思是表里的md5Sum）为空，则返回true
if (storedCheckSum == null) {
return true;
}
//如果表里的与xml中的md5Sum一致，则返回true
if (currentMd5Sum.equals(storedCheckSum)) {
return true;
}
//或者是当前xml中changeSet的md5Sum值与任何一个<validCheckSum>标签的值相等时，则返回true
for (CheckSum validCheckSum : validCheckSums) {
if (currentMd5Sum.equals(validCheckSum) || storedCheckSum.equals(validCheckSum)) {
return true;
}
}
return false;
}
问题2 表字段名中包含关键字，导致sql执行失败
由于有些表的字段与数据库的关键字一样，就会导致sql执行失败，我们需要将这些字段用反引号（quotetick）包裹起来，liquibase为我们提供了配置项。
可以在 “<databaseChangeLog> 或者 <changeset>中加入 objectQuotingStrategy=”QUOTE_ALL_OBJECTS” 属性。
如下：
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog logicalFilePath="META-INF/resources/bizbase-db-changelog.xml"
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd" objectQuotingStrategy="QUOTE_ALL_OBJECTS" >
或
<changeSet id="bizbase-20190413-1541" author="dabu" objectQuotingStrategy="QUOTE_ALL_OBJECTS">
<insert tableName="testquote">
<column name="join">xxx</column>
</insert>
</changeSet>
objectQuotingStrategy有三种策略：
- LEGACY //默认策略
- QUOTE_ALL_OBJECTS //对所有字段加反引号（建议使用）
- QUOTE_ONLY_RESERVED_WORDS //只对关键字加反引号
测试如下：
当objectQuotingStrategy=”QUOTE_ALL_OBJECTS ” ，使用liquibase的updateSQL，输出如下：
-- Changeset META-INF/resources/bizbase-db-changelog.xml::bizbase-20190413-1541::xxxx
INSERT INTO `cgdb1025`.`testquote` (`join`) VALUES ('xxx');
总结：
公司用了liquibase后遇到不少问题，但是感觉貌似他们也不是太熟悉Liquibase的用法之类的，索性抽个时间看了下Liquibase的源代码，其中md5Sum的值的计算方式比我想的要复杂，看来果然还是不能瞎猜。看了Liquibase代码后，在为知笔记了写了点笔记，很乱，所以本文只是从笔记中抽出了部分整理后放到博客上，希望对大家有用，后面可能会不定期更新吧，有问题可以留言，我尽力解答。
请教一个问题，我初始化完以后，总是提示 Table ‘databasechangelog’ already exists这个错误，用的是mysql5.7
liquibase版本是多少？ 官方说3.4.X的版本schema有bug，mysql会忽略。