插入选择时MySQL自动增量跳跃 [英] Mysql auto increment jumps when insert-select

问题描述

我正在测试插入选择查询，并注意到一个奇怪的结果.

I am testing insert-select query and noticed an weird result.

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

First I created two tables, and insert some values into 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 |
+-----+------+------+

自动递增仅跳过了cnt3.我想知道这是怎么回事.

The auto increment just skipped cnt for 3. I want to know what is this about.

推荐答案

您可以从 my.ini 更改innodb_autoinc_lock_mode = 0(传统"锁定模式)，以避免在某些情况下跳过主键中的值案件.有关innodb自动增量处理的信息，请参见手册 mysql手册以获得更多详细信息.

You can change innodb_autoinc_lock_mode=0 ("traditional" lock mode) from my.ini to avoid skipping values in primary key in some cases. See the manual mysql manual for innodb auto increment handling for more details.

按照手册由于语义上可能存在差异，提供了传统的锁定模式选项以实现向后兼容性，性能测试以及解决混合模式插入"问题."

As per manual 'The traditional lock mode option is provided for backward compatibility, performance testing, and working around issues with "mixed-mode inserts", due to possible differences in semantics'.

'在这种锁定模式下，所有类似于INSERT"的语句均获得特殊的表级AUTO-INC锁定，以便将其插入具有AUTO_INCREMENT列的表中.此锁定通常保持在语句的末尾(而不是事务的末尾)，以确保为给定的INSERT语句序列以可预测和可重复的顺序分配自动递增值，并确保自动递增任何给定语句分配的值都是连续的.

'In this lock mode, all "INSERT-like" statements obtain a special table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction) to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of INSERT statements, and to ensure that auto-increment values assigned by any given statement are consecutive'.

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

Another thing to check is the value of the auto_increment_increment config variable. It's 1 by default, but you may have changed this. it is very uncommon to set it to something higher than 1 or 2, but possible.

或者，如果在您的情况下不起作用，您也可以在同一页面中使用类似AnandPhadke的答案之类的查询:

Or if it dont work in your case you can also use query like the answer of AnandPhadke in this same page like :

ALTER TABLE表名AUTO_INCREMENT = 1;
INSERT INTO表名(col1，col2，col3)从表名中选择col1，col2，col3;

ALTER TABLE tablename AUTO_INCREMENT = 1;
INSERT INTO tablename (col1,col2,col3) SELECT col1,col2,col3 FROM tablename;

这篇关于插入选择时MySQL自动增量跳跃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！