MySQL自增id超大题目查询

小A行使的数据库默认值也是1，当做简朴插入（可以确定插入行数）的时辰，直接将auto_increment加1，而不会去锁表，这也就进步了机能。当插入的语句相同insert into select ...这种伟大语句的时辰，提前不知道插入的行数，这个时辰就要要锁表（一个名为AUTO_INC的非凡表锁）了，这样auto_increment步崆精确的，守候语句竣事的时辰才开释锁。尚有一种称为Mixed-mode inserts的插入，好比INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d')，个中一部门明晰指定了自增主键值，一部门未指定，尚有我们这里接头的INSERT ... ON DUPLICATE KEY UPDATE ...也属于这种，这个时辰会说明语句，然后按尽也许多的环境去分派auto_incrementid，这个要怎么领略呢，我看下面这个例子:

truncate table t1;  
insert into t1 values(NULL, 100, "test1"),(NULL, 101, "test2"),(NULL, 102, "test2"),(NULL, 103, "test2"),(NULL, 104, "test2"),(NULL, 105, "test2");  
-- 此时数据表下一个自增id是7  
delete from t1 where id in (2,3,4);  
-- 此时数据表只剩1，5，6了，自增id照旧7  
insert into t1 values(2, 106, "test1"),(NULL, 107, "test2"),(3, 108, "test2");  
-- 这里的自增id是几多呢？ 

上面的例子执行完之后表的下一个自增id是10，你领略对了吗，由于最后一条执行的是一个Mixed-mode inserts语句，innoDB会说明语句，然后分派三个id，此时下一个id就是10了，但分派的三个id并不必然都行使。此处 @老是迟到 多谢指出，看官方文档领略错了

模式0的话就是不管什么环境都是加上表锁，等语句执行完成的时辰在开释，假如然的添加了记录，将auto_increment加1。

至于模式2，什么环境都不加AUTO_INC锁，存在安详题目，当binlog名目配置为Statement模式的时辰，从库同步的时辰，执行功效也许跟主库纷歧致，题目很大。由于也许有一个伟大插入，还在执行呢，其它一个插入就来了，规复的时辰是一条条来执行的，就不能重现这种并发题目，导致记录id也许对不上。

至此，id跳跃的题目算是说明完了，因为innodb_autoinc_lock_mode值是1，INSERT ... ON DUPLICATE KEY UPDATE ...是简朴的语句，预先就可以计较出影响的行数，以是不管是否更新，这里都将auto_increment加1（多行的话大于1）。

假如将innodb_autoinc_lock_mode值改为0，再次执行INSERT ... ON DUPLICATE KEY UPDATE ...的话，你会发明auto_increment并没有增进，由于这种模式直接加了AUTO_INC锁，执行完语句的时辰开释，发明没有增进行数的话，不会增进自增id的。

INSERT ... ON DUPLICATE KEY UPDATE ...影响的行数是1为什么返回2？

为什么会这样呢，按理说影响行数就是1啊，看看官方文档的声名

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values

官方明晰声名白，插入影响1行，更新影响2行，0的话就是存在且更新前后值一样。是不是很欠好领略？

着实，你要这样想就好了，这是为了区分到底是插入了照旧更新了，返回1暗示插入乐成，2暗示更新乐成。

办理方案

将innodb_autoinc_lock_mode配置为0必定可以办理题目，但这样的话，插入的并发性也许会受很大影响，因此小A本身想着DBA也不会赞成。颠末思量，今朝筹备了两种较为也许的办理方案：

修改营业逻辑

修改营业逻辑，将INSERT ... ON DUPLICATE KEY UPDATE ...语句拆开，先去查询，然后去更新，这样就可以担保主键不会不受节制的增大，但增进了伟大性，原本的一次哀求也许变为两次，先查询有没有，然后去更新。

删除表的自增主键

删除自增主键，让独一索引来做主键，这样子根基不消做什么变换，只要确定今朝的自增主键没有现实的用处即可，这样的话，插入删除的时辰也许会影响服从，但对付查询多的环境来说，小A较量两种之后更乐意选择后者。

结语

着实INSERT ... ON DUPLICATE KEY UPDATE ...这个影响行数是2的，小A很早就发明白，只是没有保持好奇心，不觉得然而已，没有穷究个中的题目，这穷究就起来会带出来一大串新常识，挺好，看来小A照旧要对外界保持好奇心，保持敏感，这样才会有前进。

【编辑保举】

1. 记一次出产数据库"不测"重启的经验
2. ERP技能全打仗：数据库、编程和前端技能
3. 超适用的Oracle数据库自动备份剧本
4. 阿里数据库的极致弹性之路
5. 运维 | 美团数据库智能运维试探与实践

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!