auto_increment在不影响查询的情况下增加。

Question

在对“会话”执行以下查询时：

INSERT IGNORE INTO sessions (user_id, archived) VALUES (3, 0)

同时：

CREATE TABLE `sessions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `archived` tinyint(3) unsigned NOT NULL,
  `valid_until` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `user_id_UNIQUE` (`user_id`),
  KEY `fk_sessions_users1_idx` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

带扳机：

CREATE TRIGGER before_insert_on_sessions BEFORE INSERT ON `sessions`
FOR EACH ROW SET new.valid_until = IFNULL(new.valid_until,DATE_ADD(NOW(), INTERVAL 1 DAY));

第一次执行查询时，一切正常，并插入以下内容：

id  user_id date_created    date_modified   archived    valid_until
1   3       ...             ...             0           ...

现在再输入两次相同的查询，它会说：

No errors, 0 rows affected, taking 8,0ms

现在将查询更改为：

INSERT IGNORE INTO sessions (user_id, archived) VALUES (4, 0)

因此，基本上您只更改了user_id的值。

您会期望下一行是：

id  user_id date_created    date_modified   archived    valid_until
**2**   4       ...             ...             0           ...

相反，你会得到：

id  user_id date_created    date_modified   archived    valid_until
**4**   4       ...             ...             0           ...

因此，基本上正在发生的是，unique_key (id)'s auto_increment正在对每个查询进行更新，即使没有实际的新行。

这不应该是对的，对吧？

Server version:     5.6.22 Homebrew

Answer 1

这不应该是对的，对吧？

这是正确的。

数据库(不仅仅是MySQL)通常确保序列只会增加(一旦事务被提交)，而不是确保它们没有间隙。例如，TFM说：

如果回滚使用计数器生成数字的事务，则可能会看到分配给AUTO_INCREMENT列的值序列中的空白。

以及：

从MySQL 5.1.22开始，InnoDB可以避免对预先知道行数的一类INSERT语句使用表级AUTO-INC锁，并且仍然为基于语句的复制保留确定性的执行和安全。此外，如果您没有使用二进制日志作为恢复或复制的一部分来重放SQL语句，则可以完全消除使用表级自动锁定来获得更高的并发性和性能，而代价是允许语句分配的自动增量数字出现空白，并且可能会将并发执行语句分配的数字交织在一起。

考虑在MVCC系统(如InnoDB )中存在两个未提交的事务的情况。在提交之前，它们都可能需要一个唯一的主键，但是第一个事务可能失败。因此，你最终会有一个缺口。

您无法避免这种行为，因此请接受您的粗俗的主键:)

https://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html