数据库自增id 的总结 - wzhiju - ITeye技术网站
博客分类：
mysql数据库使用auto_increment 字段来辅助为自增列赋值。
SHOW VARIABLES LIKE 'auto_incre%';
auto_increment_increment
auto_increment_offset
auto_increment_increment
= 1 ，每次插入数据，id++；auto_increment_offset =1 ，说明从1开始。
一般情况下auto_increment_increment=1,auto_increment_offset=1.是默认初始值。
我们可以在my.cnf文件中进行重新指定。auto_increment满足一个数学当纳法推导公
value=auto_increment_increment*N+auto_increment_offset
表中有auto_increment列，innodb用一种锁策略来保正这个auto_increment列值。
如果我们创建一个表，指定了auto_increment表。那么innodb会维护一个auto_increment
当访问这个计数器，innodb会使用表级锁来锁定这访问过程，这个发生在分析auto_increment
值的过程中，而不是在一个执行事务中。也就是说：这个访问发生在事务执行前面。所以和这个锁
不是发生整个事务过程中，仅仅是分析语句判断出auto_increment这一过程中.
auto_increment计数器，一直维护在内存中，当server重启或是停止后重启，innodb会为每个表
初始化这个计数器.
对一个含有自增列（通常为id）的表执行
mysql& SHOW CREATE TABLE table_
可以得到类似输出：
CREATE TABLE `news` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`url` varchar(255) NOT NULL,
`pub_date` datetime NOT NULL,
`site` tinyint(4) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime DEFAULT NULL,
`org` varchar(255) DEFAULT NULL,
`author` varchar(255) DEFAULT NULL,
`trend` tinyint(4) DEFAULT '-1',
`md5url` char(32) NOT NULL,
`content` longtext NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `md5url` (`md5url`),
KEY `pub_date` (`pub_date`),
KEY `created_at` (`created_at`),
KEY `title` (`title`)) ENGINE=InnoDB AUTO_INCREMENT=704677 DEFAULT CHARSET=utf8
其中该状态下，表中AUTO_INCREMENT 字段值为704677 ，说明下一个插入的id 为704677 ，同时插入成功后，AUTO_INCREMENT=704678.
mysql 数据库中的id设为自增，容易产生id不连续的问题。有时将一个表中的所有数据清除，但是在插入数据时，id还是在未清除前的基础上累加。
要解决的问题：
1. 删除数据，同时要求再次插入数据时，id从1 开始累计
（1） TRUNCATE TABLE table_
（2） DELETE FROM table_
ALTER TABLE table_name auto_increment =1 ;
对上面两种方法进行一下分析。Truncate table 表名 速度快，而且效率高，因为：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
　　TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
2. 查看当前状态下一个表的auto_increment 值：
SHOW TABLE STATUS LIKE 'table_name'
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2449 Avg_row_length: 93
Data_length: 229376Max_data_length: 0
Index_length: 114688
Data_free: 0 Auto_increment: 2353
Create_time:
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL Create_options:
可以查看该表当前的Auto_increment的值。
3. 对有些失败插入操作，如果不是语法错误，比如重复的唯一键值，也会造成自增id的不连续。
4. 5.1.22之前，innodb使用一个表锁解决自增字段的一致性问题（内部是用一个计数器维护，每次自增时要加表锁），如果一行一行的插入数据则没有什么问题，但是如果大量的并发插入就废了，表锁会引起SQL堵塞，不但影响效率，而且可能会瞬间达到max_connections而崩溃。在 5.1.22之后，innodb使用新的方式解决自增字段一致性问题，对于可以预判行数的insert语句，innodb使用一个轻量级的互斥量。如：某一insert语句1执行前，表的AUTO_INCREMENT=1，语句1的插入行数已知为3，innodb在语句1的实际插入操作执行前就预分配给该语句三个自增值，当有一个新的insert语句2要执行时，读取的AUTO_INCREMENT=4，这样虽然语句1可能还没有执行完，语句2就可直接执行无需等待语句2。这种方式对于可预判插入行数的插入语句有效，如：insert和replace。对于无法提前获知插入行数的语句，如：insert...select...、replace...select...和load
data则innodb还是使用表锁。insert语句中有时会显示的设置自增字段的值，对于这种情况innodb还是会预分配给语句总行数的自增值而不是只有实际使用系统自增的行。因而有可能会造成自增字段的值不连续。如：
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
c1 为自增字段，实际的AUTO_INCREMENT 会大2。
设置新自增互斥方式：通过配置选项：innodb_autoinc_lock_mode，它是专门用来在使用auto_increment的情况下调整锁策略的，目前有三种选择：innodb_autoinc_lock_mode = 0 (“traditional” lock mode：全部使用表锁)innodb_autoinc_lock_mode = 1 (默认)(“consecutive” lockmode：可预判行数时使用新方式，不可时使用表锁) innodb_autoinc_lock_mode = 2 (“interleaved” lockmode：全部使用新方式，不安全，不适合replication)
浏览 16875
浏览: 69272 次
来自: 北京
如果使用到ADSL ,是不是用到第【3.IIOP.NET实现服 ...
您连接服务端是用adsl还是VPN的方式进行连接的？
用的是IIOPNet.src.1.9.0.final那个包，如 ...
您能具体说一下，IIOP.NET是哪个版本吗,如(IIOPNe ...MySQL中自增主键不连续之解决方案。（）_netmore吧_百度贴吧
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&签到排名：今日本吧第个签到，本吧因你更精彩，明天继续来努力！
本吧签到人数：0可签7级以上的吧50个
本月漏签0次！成为超级会员，赠送8张补签卡连续签到：天&&累计签到：天超级会员单次开通12个月以上，赠送连续签到卡3张
关注：134贴子：
MySQL中自增主键不连续之解决方案。（）
今天只做了一件事情，但解决了很大的问题。相信这也是令很多和数据库管理员头疼的事情。假设在一MySQL数据表中，自增的字段为id，唯一字段为abc，还有其它字段若干。自增：AUTO_INCREMENTA、使用insert into插入数据时，若abc的值已存在，因其为唯一键，故不会插入成功。但此时，那个AUTO_INCREMENT已然+1了。eg : insert into `table` set `abc` = '123'B、使用replace插入数据时，若abc的值已存在，则会先删除表中的那条记录，尔后插入新数据。eg : replace into `table` set `abc` = '123'（注：上一行中的into可省略；这只是一种写法。）这两种方法，效果都不好：A会造成id不连续，B会使得原来abc对应的id值发生改变，而这个id值会和其它表进行关联，这是更不允许的。那么，有没有解决方案呢？笨办法当然是有：每次插入前先查询，若表中不存在要插入的abc的值，才插入。但这样，每次入库之前都会多一个操作，麻烦至极。向同学请教，说用触发器。可在网上找了半天，总是有问题。可能是语法不对，或者是某些东西有限制。其实，最终要做的，就是在每次插入数据之后，修正那个AUTO_INCREMENT值。于是就想到，把这个最实质的SQL语句↓，合并在插入的SQL中。PS：ALTER TABLE
`table` AUTO_INCREMENT =1执行之后，不一定再插入的id就是1；而是表中id最大值+1。这是MySQL中的执行结果。其它数据库不清楚。。。。到这里，问题就变的异常简单了：在每次插入之后都重置AUTO_INCREMENT的值。如果插入的自定义函数或类的名称被定义成insert的话，那么就在此基础上扩展一个函数insert_continuous_id好了，其意为：保证自增主键连续的插入。为什么不直接修改原函数呢？这是因为，并不是所有的insert都需要修正AUTO_INCREMENT。只有在设置唯一键、且有自增主键时才有可能需要。虽然重置不会有任何的副作用（经试验，对各种情况都无影响），但没有必要就不要额外增加这一步。一个优秀的，就是要尽量保证写出的每一个字符都有意义而不多余。啰啰嗦嗦的说了这么多，其实只有一句话：解决MySQL中自增主键不连续的方法，就是上面PS下的那一行代码。附：我写的不成功的触发器的代码。-- 触发器CREATE TRIGGER trigger_table after insert ON table FOR EACH ROW ALTER TABLE `table` AUTO_INCREMENT =1;大家有想说的，请踊跃发言。期待更好更完美的解决方案。
3D双端东方魔幻网游「大青云」勾魂公测,穿越逆转,封神故事,全新演绎!
空间那份：
　update：可以把原函数扩展一下，增加一个可以不使用的变量，从而既实现了这个功能，也不会影响到之前用到这个函数的地方。示例：function insert($table=,$datas=NULL,$is=false){
//程序。。。。
//执行这一句："ALTER TABLE `{$table}` AUTO_INCREMENT =1"
//程序。。。。}
　update：一段时间的实践，发现有一定量、不过很大的浏览量之后，对于频繁使用写入操作的SQL会有灾难。原因是因为那个修正自增主键的操作会锁表。于是，咱们还是先查询，若不存在再写入吧。当然，对于特殊的情况，也可以使用更加合适的方法。
敢问楼主，到底用什么方法比较好，能不能写一个小小的教程。
贴吧热议榜
使用签名档&&
保存至快速回贴为什么PostgreSQL的自增ID是非连续的？ - 开源中国社区
当前访客身份：游客 [
当前位置：
while(1)插入数据。id为自增，结果 select * from test order by id &limit 100。得到的结果，id时非连续的，是这样的：
共有4个答案
<span class="a_vote_num" id="a_vote_num_
: 你是不是使用了事务？pgsql的sequence发生器不支持回滚，有可能是插入时新序列号已经获取但写入请求失败
--- 共有 3 条评论 ---
: 看看pg_log下面日志是否有插入失败
(2年前)&nbsp&
并没有使用事物，就是单纯的一个死循环不停的插入数据而已。
(2年前)&nbsp&
这个回答靠谱
(2年前)&nbsp&
<span class="a_vote_num" id="a_vote_num_
select * from test order by id ASC &limit 100&
--- 共有 1 条评论 ---
如果不加ASC 默认就是ASC的.
(2年前)&nbsp&
<span class="a_vote_num" id="a_vote_num_
我记得 pgsql 手册 说过 ，你不指定 排序 ，pgsql 不保证 值 是顺序的 ，可能是 磁盘上的顺序
--- 共有 5 条评论 ---
我的问题是order by id asc出来的结果ID却不是连续的。。。。.
(2年前)&nbsp&
: 我的问题是order by id asc出来的结果ID却不是连续的。。。。
(2年前)&nbsp&
手册上说的意思是如果没有ORDER BY排序，那么输出字段顺序是未知的。不过楼主已经指定了ORDER BY id，那么默认就是在id字段上做ASC排序。
(2年前)&nbsp&
: 就是楼上的回答啊 ，order by 你要指定 ，直接 select 不保证顺序
(2年前)&nbsp&
如何指定排序？建表的时候确实没有指定排序
(2年前)&nbsp&
<span class="a_vote_num" id="a_vote_num_
就像mysql 的uuid一样，每次select nextval('user_seq')都会自动递增的。就像楼主所说，可能你的事务没成功，但seq不会回滚，除非你rockback的时候设置一下seq
INSERT INTO my_users(u_id,u_name)VALUES(nextval('user_seq'),'oschina');
更多开发者职位上
有什么技术问题吗？
cevin的其它问题
类似的话题当前位置： >
> PHP中ID设置自增后不连续的原因分析及解决办法PHP中ID设置自增后不连续的原因分析及解决办法更新：&&&&编辑：天玉&&&&来源：转载&&&&人气：加载中...&&&&字号：|标签：&&&&&&&&&&&&
PHP中ID后的如下所述：
alter table tabl
alter table tablename add id mediumint(8) not null primary key auto_
每次删除把这两行家伙加上就行了
还有就是这个
mysqli对象中的query()法子每次调用只能履行一条SQL命令。
如果需要一次履行多条SQL命令，就必须mysqli对象中的 multi_query()法子。具体做法是把多条SQL命令写在同一个字符串里作为参数传递给multi_query()法子，多条SQL之间应用分号 （;）分隔。如果条SQL命令在履行时没有出错，这个法子就会返回TRUE，否则将返回FALSE。
以上所述是给大家介绍的PHP中ID后的及，愿望对大家有所，，如果大家有任何疑问欢迎给我留言，会及时回复大家的，在此也非常感谢大家的支持！
评论列表（网友评论仅供网友表达个人看法，并不表明本站同意其观点或证实其描述）
分类选择您可能在找这些帮设计师节省1,085,543,232小时