MySQL社区

 找回密码
 注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
查看: 10227|回复: 6

[列及列类型] 关于InnoDB中的自增列(自动增长autoincrement)的一个问题

[复制链接]
发表于 2007-11-30 09:45:10 | 显示全部楼层 |阅读模式
最近工作时,发现Mysql在innodb中对于自增列的处理有点问题,表现为:
假设有一个表A,其中id字段是自增列,当前A中有10条数据,id连续。
1.如果我删除id=10的记录,然后再insert一条新记录,其id为11,这样是正常的。
2.但是如果我删除id=10的记录,然后重启Mysql,再insert一条新记录,其id却还是10。显然是不正常的。

我有尝试过用ALTER TABLE A AUTO_INCREMENT = 11;
来解决这个问题,但是如果表中记录数很多,则该语句执行很慢。不能满足要求。


请问:如何才能做到重启Mysql,id也不会出现重复(复用)的情况?
发表于 2007-11-30 12:09:15 | 显示全部楼层
在重启之前执行 commit ;

或重启之前让MySQL做完所有的操作,如mysqladmin refresh . mysqladmin flush-tables ...

还要就是要用MySQl正常重启方法。不要shutdown等...
 楼主| 发表于 2007-12-5 17:22:26 | 显示全部楼层
请问:用 mysqladmin shutdown 命令,停mysql,算是正常关闭吗?
 楼主| 发表于 2007-12-5 18:49:07 | 显示全部楼层
15.2.6.3. AUTO_INCREMENT列在InnoDB里如何工作
如果你为一个表指定AUTO_INCREMENT列,在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器,它被用在为该列赋新值。自动增长计数器仅被存储在主内存中,而不是存在磁盘上。
InnoDB使用下列算法来为包含一个名为ai_col的AUTO_INCREMENT列的表T初始化自动增长计数器:服务器启动之后,当一个用户对表T做插入之时,InnoDB执行等价如下语句的动作:
SELECT MAX(ai_col) FROM T FOR UPDATE;
语句取回的值逐次加一,并被赋给列和自动增长计数器。如果表是空的,值1被赋予该列。如果自动增长计数器没有被初始化,而且用户调用为表T显示输出的SHOW TABLE STATUS语句,则计数器被初始化(但不是增加计数)并被存储以供随后的插入使用。注意,在这个初始化中,我们对表做一个正常的独占读锁定,这个锁持续到事务的结束。
InnoDB对为新创建表的初始化自动增长计数器允许同样的过程。
注意,如果用户在INSERT中为AUTO_INCREMENT列指定NULL或者0,InnoDB处理行,就仿佛值还没有被指定,且为它生成一个新值。
自动增长计数器被初始化之后,如果用户插入一个明确指定该列值的行,而且该值大于当前计数器值,则计数器被设置为指定列值。如果没有明确指定一个值,InnoDB给计数器增加一,并且赋新值给该列。
当访问自动增长计数器之时,InnoDB使用专用的表级的AUTO-INC锁定,该锁持续到当前SQL语句的结束而不是到业务的结束。引入了专用锁释放策略,来为对一个含AUTO_INCREMENT列的表的插入改善部署。两个事务不能同时对同一表有AUTO-INC锁定。 注意,如果你回滚从计数器获得数的事务,你可能会在赋给AUTO_INCREMENT列的值的序列中发现间隙。
如果用户给列赋一个赋值,或者,如果值大过可被以指定整数格式存储的最大整数,自动增长机制的行为不被定义。
在CREATE TABLE和ALTER TABLE语句中,InnoDB支持AUTO_INCREMENT = n 表选项来设置计数器初始值或变更当前计数器值。因在本节早先讨论的原因,这个选项的影响在服务器重启后就无效了。

根据红色部分的描述,innodb在处理自增列方面确实是有缺陷的,也就是说,如果重启mysql,那么内存中的最大值不会被保存。这个现象在mysql 5.1.19中已经验证了。当前还没想到解决方法。

哪位高手给指点一下,谢谢!!
发表于 2007-12-6 16:28:44 | 显示全部楼层
commit也没用。。。。下面的方法似乎可行。。。。
强制MySQL不复用已经使用过的序列值的方法是:另外创建一个专门用来生成AUTO_INCREMENT序列的数据表,并做到永远不去删除该表的记录。当需要在主数据表里插入一条记录时,先在那个专门生成序号的表中插入一个NULL值以产生一个编号,然后,在往主数据表里插入数据时,利用LAST_Insert_ID()函数取得这个编号,并把它赋值给主表的存放序列的数据列。如:

insert into id set id = NULL;
insert into main set main_id = LAST_Insert_ID();
可用alter命令给一个数据表增加一个具有AUTO_INCREMENT属性的数据列。MySQL会自动生成所有的编号。

要重新排列现有的序列编号,最简单的方法是先删除该列,再重建该,MySQL会重新生连续的编号序列。

在不用AUTO_INCREMENT的情况下生成序列,可利用带参数的LAST_Insert_ID()函数。如果用一个带参数的LAST_Insert_ID(expr)去插入或修改一个数据列,紧接着又调用不带参数的LAST_Insert_ID()函数,则第二次函数调用返回的就是expr的值。下面演示该方法的具体操作:

先创建一个只有一个数据行的数据表:
create table seq_table (id int unsigned not null);
insert into seq_table values (0);
接着用以下操作检索出序列号:
update seq_table set seq = LAST_Insert_ID( seq + 1 );
select LAST_Insert_ID();
通过修改seq+1中的常数值,可生成不同步长的序列,如seq+10可生成步长为10的序列。
该方法可用于计数器,在数据表中插入多行以记录不同的计数值。再配合LAST_Insert_ID()函数的返回值生成不同内容的计数值。这种方法的优点是不用事务或LOCK,UNLOCK表就可生成唯一的序列编号。不会影响其它客户程序的正常表操作。
发表于 2007-12-6 22:39:05 | 显示全部楼层
 楼主| 发表于 2007-12-7 11:37:50 | 显示全部楼层
,好方法,试一下,谢谢
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|申请友链|小黑屋|Archiver|手机版|MySQL社区 ( 京ICP备07012489号   
联系人:周生; 联系电话:13911732319

GMT+8, 2024-3-28 21:02 , Processed in 0.067175 second(s), 24 queries , Gzip On.

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表