关于MySQL的AUTO_INCREMENT列出现不连续的原因,本文列出了几个比较常见的场景。MySQL 5.1.42, InnoDB Plugin 1.0.6, innodb_autoinc_lock_mode = 1
Scenario 1
CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10)) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO test VALUES(NULL, '1');
ROLLBACK;
INSERT INTO test VALUES(NULL, '2');
SELECT * FROM test;
+----+------+
| id | name |
+----+------+
| 2 | 2 |
+----+------+
Scenario 2
CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10)) ENGINE=InnoDB;
INSERT INTO test VALUES(NULL, '1');
INSERT INTO test VALUES(3, '3');
SELECT * FROM test;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 3 | 3 |
+----+------+
Scenario 3
CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10)) ENGINE=InnoDB;
ALTER TABLE test AUTO_INCREMENT=100;
INSERT INTO test VALUES(NULL, '100'), (99, '99'), (NULL, '101');
INSERT INTO test VALUES(NULL, '103');
SELECT * FROM test;
+-----+------+
| id | name |
+-----+------+
| 99 | 99 |
| 100 | 100 |
| 101 | 101 |
| 103 | 103 |
+-----+------+
Scenario 4
CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10)) ENGINE=InnoDB;
CREATE TABLE t(id INT) ENGINE=InnoDB;
INSERT INTO t VALUES(1), (2), (3), (4), (5);
INSERT INTO test SELECT NULL, id FROM t;
INSERT INTO test VALUES(NULL, '8');
SELECT * FROM test;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 8 | 8 |
+----+------+
点评:在这种场景下,InnoDB无法在执行Insert语句之前知道确切的插入记录数,因此会使用表级的AUTO_INC锁(该锁比较特殊,并不像通常的锁那样,在事务结束时释放,而是在该语句执行完毕后释放)。对于AUTO_INCREMENT值,目前InnoDB会采取预分配的策略,即首先分配1,如果用尽则double,如果用尽再double,即1,2,4,8...。需要注意的是,如果innodb_autoinc_lock_mode =2,那么InnoDB不会使用AUTO_INC锁。
Scenario 5
CREATE TABLE test (a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT, UNIQUE INDEX(b)) ENGINE=InnoDB;
INSERT INTO test values(NULL, 1, 1), (NULL, 2, 2);
INSERT INTO test (a,b,c) VALUES (NULL,2,3) ON DUPLICATE KEY UPDATE c=c+1;
INSERT INTO test values(NULL, 4, 4);
SELECT * FROM test;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 3 |
| 4 | 4 | 4 |
+---+------+------+
点评:在INSERT语句执行之前,InnoDB无法知道数据最终是被插入还是更新,因此可能会导致InnoDB预分配的AUTO_INCREMENT值最终没有被使用。
@see also:
http://dinglin.iteye.com/blog/1279536
分享到:
相关推荐
跨越叶序研究中的数学-植物学鸿沟—大对称序叶序图形形成的机理,杨威生,赵汝光,几个世纪以来,与植物学家一起,数学家,物理学家,晶体学家等,都为叶序研究奉献出了各自的努力。但是,显然是因为他们之间的沟
信息安全_数据安全_Red_Team_View:Gaps_in_the_Server 移动安全 安全架构 应急响应 解决方案 安全防护
关于GAPS技术的,一部分的开发规范和步骤,很适合用的
gaps一种用于拼图基于遗传算法和OpenCV的求解器
The off-plane propagation of electromagnetic (EM) waves in a two-dimensional (2D) graphite photonic crystal structure was studied using transfer matrix method. Transmission spectra calculations ...
Filling Execution Gaps: How Executives and Project Managers Turn Corporate Strategy into Successful Projects By 作者: Todd C. Williams ISBN-10 书号: 1501515209 ISBN-13 书号: 9781501515200 出版日期: ...
关于战略的HBR论文~ Developing_the_Strategy_Vision,Value_Gaps,_and_Analysis
i3-gaps-deb:用于创建和安装i3-gaps的Debian(或Ubuntu)软件包的工具
这个指标在柱形图上显示了差距。
matlab导入excel代码utl_identify_gaps_in_magazine_subscriptons 确定订阅者之间有3个月或3个月以上的时间间隔的订阅者。 关键字:sas sql join合并大数据分析宏oracle teradata mysql sas社区stackoverflow ...
The remaining chapters focus exclusively on EDM’s emerging role in helping to advance educational research—from identifying at-risk students and closing socioeconomic gaps in achievement to aiding ...
A photonic crystal fiber has been filled with a cholesteric liquid crystal. A temperature sensitive photonic band gap effect was observed, which was especially pronounced around the liquid crystal ...
用于x11-wm / i3 GAPS选件的FreeBSD端口 测试端口 您可以通过克隆此存储库并在配置中启用GAPS选项来测试端口。 git clone https://github.com/tony/i3-gaps-freebsd.git cd i3-gaps-freebsd # one way make WITH=...
Leon Starr and Andrew Mangogna, "Models to Code: With No Mysterious Gaps" English | ISBN: 1484222164 | 2017 | 305 pages | PDF | 18 MB Learn how to translate an executable model of your application ...
Models to Code With No Mysterious Gaps 英文epub 本资源转载自网络,如有侵权,请联系上传者或csdn删除 查看此书详细信息请在美国亚马逊官网搜索此书
there is an expanding science base from diverse disciplines that can support science communicators in making these determinations....gaps in knowledge about how to communicate effectively about science...
Models to Code With No Mysterious Gaps 英文无水印原版pdf pdf所有页面使用FoxitReader、PDF-XChangeViewer、SumatraPDF和Firefox测试都可以打开 本资源转载自网络,如有侵权,请联系上传者或csdn删除 查看...
This book targets engineers and researchers ... While not an exhaustive text, we hope to illuminate fundamental concepts for the reader as well as identify trends and gaps in on-chip network research.
and confusing because of just a few gaps in this prerequisite education. This second edition of Hacking: The Art of Exploitation makes the world of hacking more accessible by providing the complete ...
安装克隆回购: $ git clone https://github.com/nemanja-m/gaps.git$ cd gaps 安装要求: $ pip install -r requirements.txt$ sudo apt-get install python-tk 以可编辑模式安装项目: $ pip install -e ....