当使用insert-select键时，Mysql自动增量跳转

Question

我正在测试insert-select查询，并注意到一个奇怪的结果。

CREATE TABLE `test` (
  `cnt` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`cnt`)
)

CREATE TABLE `test_current` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
)

首先，我创建了两个表，并将一些值插入到test_current中

mysql> insert into test_current (a,b) values (1,1),(2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

我做了这个查询

mysql> INSERT INTO test (a,b) SELECT a,b FROM test_current;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from test;
+-----+------+------+
| cnt | a    | b    |
+-----+------+------+
|   1 |    1 |    1 |
|   2 |    2 |    2 |
+-----+------+------+
2 rows in set (0.00 sec)

但是当我再次执行查询时

mysql> INSERT INTO test (a,b) SELECT a,b FROM test_current;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from test;
+-----+------+------+
| cnt | a    | b    |
+-----+------+------+
|   1 |    1 |    1 |
|   2 |    2 |    2 |
|   4 |    1 |    1 |
|   5 |    2 |    2 |
+-----+------+------+

自动增量刚刚跳过cnt for 3。我想知道这是怎么回事。

Answer 1

您可以从my.ini更改innodb_autoinc_lock_mode=0 (“传统”锁定模式)，以避免在某些情况下跳过主键中的值。有关更多详细信息，请参阅手册mysql manual for innodb auto increment handling。

根据手册“提供传统锁模式选项是为了向后兼容，性能测试，以及解决”混合模式插入“的问题，这是由于语义上的可能差异。”

另一件需要检查的事情是auto_increment_increment配置变量的值。默认情况下是1，但您可能已经更改了此设置。将其设置为高于1或2的值是很少见的，但也是可能的。

或者，如果它在您的情况下不起作用，您也可以在同一页面中使用query，如AnandPhadke的答案，如下所示：

ALTER TABLE tablename AUTO_INCREMENT = 1；

INSERT INTO tablename (col1，col2，col3) SELECT col1，col2，col3 FROM tablename;

Answer 2

每次将值插入到表中之前，都可以将auto_increment值重置为1：

ALTER TABLE `test` AUTO_INCREMENT = 1;
INSERT INTO test (a,b) SELECT a,b FROM test_current;

Answer 3

输入你的My.cnf：

innodb_autoinc_lock_mode=0