Liquibase 4.16脚本向MySQL 8自增列插入指定值失败

问题原因 #

你的问题核心在于MySQL默认sql_mode不支持直接向AUTO_INCREMENT字段插入0值：

• 默认情况下，MySQL会把插入AUTO_INCREMENT字段的0当作NULL处理，自动生成下一个自增值（空表的自增起始值默认是1）。
• 你插入的第一条记录(0,4)会被自动转为主键1，紧接着插入(1,5)就触发主键重复报错。
• MySQL Workbench能正常执行，是因为它默认开启了NO_AUTO_VALUE_ON_ZERO这个sql模式，该模式允许将0作为AUTO_INCREMENT字段的有效值插入。

另外，多次执行Liquibase update时，即使事务回滚，InnoDB的自增计数器不会回滚，导致每次尝试插入时自增起始值递增，最后一次执行时自增值刚好跳过了你指定的冲突值，最终插入的是自增生成的1、2、3，而非你指定的值。

解决方案 #

方案1：在Liquibase变更集中临时设置sql_mode #

在插入语句前后添加sql_mode的设置与恢复，确保插入期间允许0作为自增字段值：

-- 保存当前sql_mode
SET @OLD_SQL_MODE = @@SQL_MODE;
-- 开启NO_AUTO_VALUE_ON_ZERO
SET SQL_MODE = CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO');

-- 执行插入
INSERT INTO `mytable` (`key`, `field`)
VALUES (0, 4), (1, 5), (2, 3);

-- 恢复原sql_mode
SET SQL_MODE = @OLD_SQL_MODE;

如果用XML变更集，写法如下：

<changeSet id="insert-mytable-data" author="your-name">
    <sql>SET @OLD_SQL_MODE = @@SQL_MODE;</sql>
    <sql>SET SQL_MODE = CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO');</sql>
    <insert tableName="mytable">
        <column name="key" value="0"/>
        <column name="field" value="4"/>
    </insert>
    <insert tableName="mytable">
        <column name="key" value="1"/>
        <column name="field" value="5"/>
    </insert>
    <insert tableName="mytable">
        <column name="key" value="2"/>
        <column name="field" value="3"/>
    </insert>
    <sql>SET SQL_MODE = @OLD_SQL_MODE;</sql>
</changeSet>

方案2：修改JDBC连接URL，全局开启sql_mode #

在Liquibase配置的MySQL JDBC URL中添加sql_mode参数，永久为该连接开启NO_AUTO_VALUE_ON_ZERO：

jdbc:mysql://localhost:3306/your_db?useSSL=false&sql_mode=NO_AUTO_VALUE_ON_ZERO

如果原有sql_mode已有其他值，用逗号拼接：

jdbc:mysql://localhost:3306/your_db?useSSL=false&sql_mode=STRICT_TRANS_TABLES,NO_AUTO_VALUE_ON_ZERO

方案3：调整插入值（如果不需要0作为主键） #

如果可以放弃插入主键0的需求，将第一条记录的主键改为大于当前自增起始值的数值，比如：

INSERT INTO `mytable` (`key`, `field`)
VALUES (3, 4), (1, 5), (2, 3);

不过这种方法仅适用于不需要指定0作为主键的场景。

内容的提问来源于stack exchange，提问作者Jorge Gastaldi