MySQL Liquibase与MySQL自增不能共存的问题解决

在使用MySQL进行数据库开发时，可能会遇到使用MySQL自增（autoincrement）时和Liquibase冲突的问题。下面将对这个问题进行探讨，并提供解决方案。

阅读更多：MySQL 教程

问题描述

在MySQL使用自增的情况下，使用Liquibase更新数据库时会报错。如下所示：

liquibase.exception.DatabaseException: Error executing SQL ALTER TABLE `user` ADD `id` INT(11) NOT NULL AUTO_INCREMENT: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO_INCREMENT' at line 1

原因分析

MySQL自增的原理是，在插入一条新记录时，MySQL会自动取当前表的最大ID值，并使新插入的记录的ID值为最大值加1。Liquibase更新数据库时，会先清空当前表，并重新创建表格，这就导致当前表最大ID值被清空，无法自加。因此，使用Liquibase更新数据库时，不管使用MySQL 5.5还是MySQL 5.6,都会在语法层面出现差异，从而不支持自增列。具体的 SQL 语句如下：

ALTER TABLE `user` ADD `id` INT(11) NOT NULL AUTO_INCREMENT;

解决方案

为了解决这个问题，我们需要对MySQL和Liquibase进行配置。

配置MySQL

我们需要在建表时，先插入一条记录，让自增列从1开始自加，代码如下：

-- 创建表格
CREATE TABLE IF NOT EXISTS user (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL
);

-- 插入一条记录
INSERT INTO user VALUES (1, 'admin', 'admin');

在Liquibase更新数据库时，就可以正常执行自增列语句了，代码如下：

-- 更新数据库表格
<changeSet author="me" id="123">
    <preConditions onFail="MARK_RAN">
        <not>
            <tableExists tableName="user"/>
        </not>
    </preConditions>

    <createTable tableName="user">
        <column name="id" type="INT(11) UNSIGNED NOT NULL AUTO_INCREMENT">
            <constraints primaryKey="true"/>
        </column>
        <column name="username" type="varchar(255)">
            <constraints nullable="false"/>
        </column>
        <column name="password" type="varchar(255)">
            <constraints nullable="false"/>
        </column>
    </createTable>
</changeSet>

配置Liquibase

另外，我们还可以在Liquibase配置文件中启用“skipAutoIncrement”属性，这样Liquibase在更新数据库时，就会忽略自增列的语句，同时我们也没有必要在建表时插入一条记录。

代码如下：

-- Liquibase配置文件
<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.7.xsd">
    <changeSet id="1" author="author">
        <createTable tableName="user">
            <column name="id" type="INT(11) UNSIGNED NOT NULL PRIMARY KEY"/>
            <column name="username" type="varchar(255)"/>
            <column name="password" type="varchar(255)"/>
        </createTable>
    </changeSet>
    <changeSet id="update-1" author="author">
        <addColumn tableName="user">
            <column name="balance" type="decimal(18,2)"/>
        </addColumn>
    </changeSet>
    <changeSet id="update-2" author="author">
        <column name="balance" type="decimal(18,2)"/>
    </changeSet>
    <!-- 使用skipAutoIncrement属性启用忽略自增列 -->
    <changeSet id="update-3"author="author" skipAutoIncrement="true">
        <addColumn tableName="user">
            <column name="score" type="int(11)"/>
        </addColumn>
    </changeSet>
</databaseChangeLog>