Liquibase是数据库版本管理工具,设定的概念是把数据库schema的变动(table的建立,column的变动,index变动…)及数据内容的变动(insert,update,delete)用Liquibase定义的语法写成xml格式change log,然后在数据库里多建立一个名为databasechangelog的table,用来记录db更新了那些change log,当xml新增了更多的change log后,跟db里的版号一比对,就可得知未同步的change log有那些,下次同步时,就会同步这些未同步过的change log。
LiquiBase 支持 10 种数据库类型,包括 DB2、Apache Derby、MySQL、PostgreSQL、Oracle、Microsoft®SQL Server、Sybase 和 HSQL。
要开始使用 LiquiBase,需要以下四个步骤:
- 创建一个数据库 变更日志(change log)文件。
- 在变更日志文件内部创建一个 变更集(change set)。
- 启动程序,数据库运行变更集。
- 检验数据库中的变更。
官网 链接 http://www.liquibase.org/documentation/changes/load_update_data.html
springboot 新建项目可以选择
包括folwable中也使用
LiquiBase在执行changelog时,会在数据库中插入两张表:DATABASECHANGELOG
和DATABASECHANGELOGLOCK
,分别记录changelog的执行日志和锁日志。
LiquiBase在执行changelog中的changeSet时,会首先查看DATABASECHANGELOG
表,如果已经执行过,则会跳过(除非changeSet的runAlways
属性为true),如果没有执行过,则执行并记录changelog日志;
changelog中的一个changeSet对应一个事务,在changeSet执行完后commit,如果出现错误则rollback;
<changeSet>
标签的主要属性有:
- runAlways:即使已经执行过,仍然每次都执行;注意: 由于
DATABASECHANGELOG
表中还记录了changeSet的MD5校验值MD5SUM,如果changeSet的id
和name
没变,而内容变了,则由于MD5值变了,即使runAlways的值为True,执行也是失败的,会报错。这种情况应该使用runOnChange
属性。 - runOnChange:第一次的时候执行以及当changeSet的内容发生变化时执行。不受MD5校验值的约束。
- runInTransaction:是否作为一个事务执行,默认为true。设置为false时需要小心:如果执行过程中出错了则不会rollback,数据库很可能处于不一致的状态;
<!-- Attributes for changeSet -->
<xsd:attributeGroup name="changeSetAttributes">
<xsd:attribute name="id" type="xsd:string" use="required"/>
<xsd:attribute name="author" type="xsd:string" use="required"/>
<xsd:attribute name="context" type="xsd:string"/>
<xsd:attribute name="labels" type="xsd:string" />
<xsd:attribute name="dbms" type="xsd:string"/>
<xsd:attribute name="runOnChange" type="booleanExp"/>
<xsd:attribute name="runAlways" type="booleanExp"/>
<xsd:attribute name="failOnError" type="booleanExp"/>
<xsd:attribute name="onValidationFail" type="onChangeSetValidationFail" />
<xsd:attribute name="runInTransaction" type="booleanExp" default="true"/>
<xsd:attribute name="logicalFilePath" type="xsd:string"/>
<xsd:attribute name="objectQuotingStrategy" type="objectQuotingStrategy" />
<xsd:attribute name="created" type="xsd:string"/>
<xsd:attribute name="runOrder" type="xsd:string"/>
<xsd:attribute name="ignore" type="booleanExp" />
</xsd:attributeGroup>
问题汇总
一、修改rm_rmaorder表新增count字段 然后 在数据库中导入csv格式的数据
使用:Change: ‘loadUpdateData’ 不存在就插入存在就更新
Loads or updates data from a CSV file into an existing table. Differs from loadData by issuing a SQL batch that checks for the existence of a record. If found, the record is UPDATEd, else the record is INSERTed. Also, generates DELETE statements for a rollback.
A value of NULL in a cell will be converted to a database NULL rather than the string ‘NULL’
<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
<!--
Added the constraints for entity DistributorCountry.
-->
<changeSet id="20190730095746-1" author="fanwk1">
<addColumn tableName="rm_rmaorder">
<column name="count" type="integer">
<constraints nullable="true"/>
</column>
</addColumn>
</changeSet>
<changeSet id="20190730095746-1-data" author="xxxx">
<loadUpdateData
file="config/liquibase/data/rm_rmaorder.csv"
separator=";"
tableName="rm_rmaorder" primaryKey="id">
<column name="count" type="numeric"/>
</loadUpdateData>
</changeSet>
</databaseChangeLog>
需要在master.xml 假如xml地址
id;count
1;2
2;2
3;2
4;2
5;2
6;1
7;2
8;2
9;2
10;2
官网的文档
Name | Description | Required For | Supports | Since |
---|---|---|---|---|
catalogName | Name of the catalog | all | 3.0 | |
encoding | Encoding of the CSV file (defaults to UTF-8) | all | ||
file | CSV file to load | all | all | |
primaryKey | Comma delimited list of the columns for the primary key | all | all | |
quotchar | all | |||
schemaName | Name of the schema | all | ||
separator | all | |||
tableName | Name of the table to insert or update data in | all | all |
<changeSet author="liquibase-docs" id="loadUpdateData-example">
<loadUpdateData catalogName="cat"
encoding="UTF-8"
file="com/example/users.csv"
primaryKey="pk_id"
quotchar="A String"
schemaName="public"
separator="A String"
tableName="person">
<column name="address" type="varchar(255)"/>
</loadUpdateData>
</changeSet>
二、.关于数据库liquibase表DATABASECHANGELOG 字段 MD5SUM
当启动时会校验此hash值和数据库存的是否一样,一个changeSet在数据库位一行
- 当changeSet为创建表,删除表,新增字段时 改变MD5SUM 的值
- 当changeSet为loadDate或loadUpdateData 改变csv的值,改变MD5SUM 的值
当数据库和MD5sum不一致时直接改数据库值报错信息
hange sets check sum
config/liquibase/changelog/20190801091745_added_entity_NewCustomerRequest.xml::20190801091745-1::jhipster
was: 8:e37ec0bab5850274fedb4e86af1b3d27 but is now: 8:d68a2b1645c58fd25525f0287da671a3
如果需要强行启动可以在本地启动,在h2数据库找到最新的MD5SUM 值,把此值粘贴到dev数据库的MD5SUM 同样行,就不会本地的hash值和数据库的不一样。
三.编辑csv 可以将csv变成tab格式的,方便查看
四.关于liquibase的xml详细的信息
除了官网,还有可以查看dbchangelog-3.6.xsd 如:
<xsd:element name="addColumn">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="column" minOccurs="1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:complexContent mixed="true">
<xsd:extension base="columnType">
<xsd:attribute name="beforeColumn" type="xsd:string" />
<xsd:attribute name="afterColumn" type="xsd:string" />
<xsd:attribute name="position" type="xsd:integer" />
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attributeGroup ref="changeAttributes" />
<xsd:attributeGroup ref="tableNameAttribute"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="loadUpdateData">
<xsd:complexType mixed="true">
<xsd:sequence>
<xsd:element name="column" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="index" type="integerExp"/>
<xsd:attribute name="header" type="xsd:string"/>
<xsd:attribute name="name" type="xsd:string"/>
<xsd:attribute name="type" type="xsd:string"/>
<xsd:attribute name="defaultValue" type="xsd:string"/>
<xsd:attribute name="defaultValueNumeric" type="xsd:string"/>
<xsd:attribute name="defaultValueDate" type="xsd:string"/>
<xsd:attribute name="defaultValueBoolean" type="booleanExp"/>
<xsd:attribute name="defaultValueComputed" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attributeGroup ref="tableNameAttribute" />
<xsd:attribute name="file" type="xsd:string"/>
<xsd:attribute name="relativeToChangelogFile" type="booleanExp"/>
<xsd:attribute name="encoding" type="xsd:string" default="UTF-8"/>
<xsd:attribute name="primaryKey" type="xsd:string" use="required"/>
<xsd:attribute name="onlyUpdate" type="xsd:boolean" default="false"/>
<xsd:attribute name="separator" type="xsd:string" default=","/>
<xsd:attribute name="quotchar" type="xsd:string" default="""/>
</xsd:complexType>
</xsd:element>
可以方便知道可以写那些标签e
五.对当前数据库状态生成 changlog
步骤:
a.修改liquibase数据库连接信息
在项目pom.xml文件夹中,找到liquibase插件,并修改数据库连接信息,地址,用户名密码等;
b.然后使用cmd进入项目根目录执行 mvn liquibase:generateChangeLog
c.生成的changelog内容会打印在控制台中
mvn liquibase:generateChangeLog
1 | mvn liquibase:generateChangeLog |
只对数据生成 changelog ,(先用别的方式往数据库创建数据后再用此方式生成changelog)
mvn liquibase:generateChangeLog -Dliquibase.diffTypes=data
1 | mvn liquibase:generateChangeLog -Dliquibase.diffTypes=data |
报错:
Failed to execute goal org.liquibase:liquibase-maven-plugin:3.6.3:generateChangeLog (default-cli) on project quotation: The database URL has not been specified either as a parameter or in a properties file. ->
配置上这些:
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://**:3306</url>
<defaultSchemaName>数据库表名</defaultSchemaName>
<username>root</username>
<password></password>
<plugin>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>${liquibase.version}</version>
<configuration>
<changeLogFile>${project.basedir}/src/main/resources/config/liquibase/master.xml</changeLogFile>
<diffChangeLogFile>
${project.basedir}/src/main/resources/config/liquibase/changelog/${maven.build.timestamp}_changelog.xml
</diffChangeLogFile>
<driver></driver>
<url></url>
<defaultSchemaName></defaultSchemaName>
<username>quotation</username>
<password></password>
<referenceUrl>hibernate:spring:com.lenovo.quotation.domain?dialect=&hibernate.physical_naming_strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy&hibernate.implicit_naming_strategy=org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy</referenceUrl>
<verbose>true</verbose>
<logging>debug</logging>
<contexts>!test</contexts>
</configuration>
<dependencies>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>${hibernate-core.version}</version>
</dependency>
<dependency>
<groupId>org.javassist</groupId>
<artifactId>javassist</artifactId>
<version>${javassist.version}</version>
</dependency>
<dependency>
<groupId>org.liquibase.ext</groupId>
<artifactId>liquibase-hibernate5</artifactId>
<version>${liquibase-hibernate5.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>${spring-boot.version}</version>
</dependency>
<dependency>
<groupId>javax.validation</groupId>
<artifactId>validation-api</artifactId>
<version>${validation-api.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${spring.version}</version>
</dependency>
</dependencies>
</plugin>
defaultSchemaName 这个字段是数据表的名称
<parameter>
<name>defaultSchemaName</name>
<type>java.lang.String</type>
<required>false</required>
<editable>true</editable>
<description>The default schema name to use the for database connection.</description>
</parameter>
六、合并changelog(xml文件)和data(csv文件)
1.手动将其他两个changelog表中的column字段拷贝到第一个中
2.删除其余changelog
3.删除mast.xml中的应用
4.本地启动 查看h2数据中DATABASECHANGELOG的MD5SUM:8:348d4a241752350f2db1d8097bf2dc84
复制替换dev(线上)数据库中的同样字段进行替换
5.删除DATABASECHANGELOG中FILENAME为config/liquibase/changelog/20190715095745_xxxx.xml的那行
备注:data中的csv文件处理思路一致
七、excel数据另存csv文件
1.excel中的数据另存为csv
2.打开notepad++ 将“,”替换“;”
3.拷贝到xx.csv 文件即可
4.本地启动 查看h2数据中DATABASECHANGELOG的MD5SUM:8:348d4a241752350f2db1d8097bf2dc84
复制替换dev(线上)数据库中的同样字段进行替换
八、DATABASECHANGELOGLOCK 用途
第一种情况
Liquibase - Waiting for changelog lock
Waiting for changelog lock....
Running the migration script for a database may produce this:
...
INFO … Liquibase: Waiting for changelog lock....
INFO … Liquibase: Waiting for changelog lock....
INFO … Liquibase: Waiting for changelog lock....
解决方法: 看下那个机器锁住了database,执行下面语句;
USE [Database Name]
SELECT * FROM DATABASECHANGELOGLOCK;
一般情况下是本机锁住的;则通过下面sql解锁
UPDATE DATABASECHANGELOGLOCK
SET locked=0, lockgranted=null, lockedby=null
WHERE id=1
正常情况下
第二种情况
当liquibase启动失败后会将LOCKED 为1
报错信息
2019-08-20 07:47:26.824 INFO [quotation,,,] 1 --- [ main] c.lenovo.quotation.config.WebConfigurer : Web application fully configured
2019-08-20 07:47:27.374 DEBUG [quotation,,,] 1 --- [ main] c.l.quotation.config.AsyncConfiguration : Creating Async Task Executor
2019-08-20 07:47:27.455 DEBUG [quotation,,,] 1 --- [ main] c.l.q.config.LiquibaseConfiguration : Configuring Liquibase
2019-08-20 07:47:27.491 DEBUG [quotation,,,] 1 --- [ main] i.g.j.c.liquibase.AsyncSpringLiquibase : Starting Liquibase synchronously
一直在这个地方不动
修改为0即可