我有这样的情况之一:
CREATE TABLE table (
`col1` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`col2` VARCHAR(512) NOT NULL COLLATE 'utf8mb4_general_ci',
PRIMARY KEY (`col1`) USING BTREE,
UNIQUE INDEX `col2` (`col2`) USING BTREE,
[...]
然后我有这样的声明:
INSERT INTO table (col2, col3) VALUES ( val2, val3) ON DUPLICATE KEY UPDATE (col2=val2, col3=val3)
如果 col2 上有重复的键 UPDATE 将 auto_incrementcol1
问题有没有办法让表仅在 INSERT 中自动递增?
你执行
INSERT INTO .. ON DUPLICATE KEY UPDATE
.首先
INSERT INTO
是执行。您的值部分不包含定义为自动增量的主键列的值。因此,会为这个新插入的行生成新的自动增量值。如果查询中提供的值不会导致重复违规,则将插入具有新生成的自动增量值的行。
如果查询中提供的值导致重复违规,则将执行查询的 ON DUPLICATE KEY UPDATE 部分中指定的 UPDATE 操作。
但在这两种情况下都已经生成了新的自动增量值。因此,在这两种情况下,下一个查询将生成下一个自动增量值。您不能“如果触发了 ON DUPLICATE KEY UPDATE,则不生成”。
在默认的InnoDB锁定模式下,答案是否定的。在完成重复键检查之前,会快速生成自动增量值。一旦表的自动增量锁被释放,就没有“撤消”,并且生成的值将不会被重用。
但是,您可以使用不同的锁定模式,这就是 InnoDB 多年前的工作方式。表的自动增量锁将一直保持到 INSERT 语句结束,包括任何重复键检查和后续更新。如果未使用新的 auto-inc 值,则可以保留它们以供其他会话使用。
演示:
我更改了锁定模式
/etc/my.cnf
并重新启动了 MySQL 服务器进程。该选项是全局只读选项,只能在选项文件中更改。然后我测试了:
请注意,表的值
AUTO_INCREMENT
并未前进,就像innodb_autoinc_lock_mode
默认值 2 一样。一般来说,默认的锁定模式是首选,因为这意味着表锁的持有时间更短,当多个会话插入同一个表时,这一点很重要。它允许更大的并行吞吐量。
在这里阅读有关 InnoDB 锁定模式的更多信息:https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html