使用auto_increment产生间隙！

0 0

使用auto_increment产生间隙！20

表结构如下：

SQL code

mysql> show create table tb_user \G
*************************** 1. row ***************************
       Table: tb_user
Create Table: CREATE TABLE `tb_user` (
  `pk_user` int(10) NOT NULL AUTO_INCREMENT COMMENT 'PK主键',
  `true_name` varchar(20) DEFAULT NULL COMMENT '用户姓名，必填',
  `country` varchar(4) DEFAULT NULL 
,
  `province` varchar(4) DEFAULT NULL,
  `city` varchar(4) DEFAULT NULL,
  `company_name` varchar(30) DEFAULT NULL ,
  `department` varchar(20) DEFAULT NULL ,
  `position` varchar(4) DEFAULT NULL ,
  `address` varchar(100) DEFAULT NULL ,
  `phone` varchar(20) DEFAULT NULL ,
  `company_property` varchar(4) DEFAULT NULL ,
  `company_product` varchar(4) DEFAULT NULL ,
  `company_person_count` varchar(4) DEFAULT NULL ,
  `info_mode` varchar(4) DEFAULT NULL ,
  `register_date` datetime DEFAULT NULL ,
  `active_state` varchar(4) DEFAULT NULL ,
  `lock_state` varchar(4) DEFAULT NULL ,
  `use_state` varchar(4) DEFAULT NULL ,
  `creater` varchar(20) DEFAULT NULL ,
  `create_date` datetime DEFAULT NULL ,
  `updater` varchar(20) DEFAULT NULL ,
  `update_date` datetime DEFAULT NULL ,
  PRIMARY KEY (`pk_user`)
) ENGINE=InnoDB AUTO_INCREMENT=2424797 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

这里我只是想在表中快速插入千万条数据，因此首先我手动插入2条数据：

SQL code

insert into tb_user (true_name) values ('jack'); insert into tb_user (true_name) values ('tom');

然后使用下列语句实现快速插入：

SQL code

insert into tb_user (true_name)（select true_name from tb_user）;

但是问题来了，在查询前20条数据就发现pk_user不是按照顺序递增的，中间有很多间隙，前20条数据少了5、10、11、12、21-27， 20条数据直接把pk_user分配到了31.。
另外，这是在本机单机无并发情况下无事务，纯语句级别使用JAVA循环插入实现的

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

请教大虾分析下是什么原因产生的，如何解决？拜谢！

问题补充：

robertliudeqiang 写道

可以试试直接在数据库执行：

1 先用你提供的sql语句建表

2 再执行
insert into tb_user (true_name) values ('jack'); insert into tb_user (true_name) values ('tom');

3 再反复执行
insert into tb_user (true_name) values ('jack'); insert into tb_user (true_name) values ('tom');

看是否仍然会出现你说的问题，如果没有出现这个问题，则很可能是你的java程序写的有问题。

手动插入反复执行是insert into tb_user (true_name) values ('jack'); insert into tb_user (true_name) values ('tom'); 是没有问题的，我其实是觉得会不会是
insert into tb_user (true_name) (select true_name from tb_user)；语句出的毛病

问题补充：

robertliudeqiang 写道

手动插入反复执行是insert into tb_user (true_name) values ('jack'); insert into tb_user (true_name) values ('tom'); 是没有问题的，我其实是觉得会不会是 
insert into tb_user (true_name) (select true_name from tb_user)；语句出的毛病

1 insert into tb_user (true_name) (select true_name from tb_user)；
这条语句是没有问题的

2 如果你手动执行没有出现问题的话，很可能是你写的程序有些小问题，程序不大的话发上来看看

3 另外，看mysql手册
http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html
有这么一段：

“Lost” auto-increment values and sequence gaps
In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost.” Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table.
意思是说，如果你执行一条语句失败，为这条语句产生的auto-increment值将会丢失。结果就是出现你提到的这种现象。

据上推断：是否在你程序中写的sql有些没有执行成功，没有执行成功的sql语句产生的auto-increment值不会被再次使用而造成丢失。

首先，感谢你的回答，问题已经确定是insert into tb_user (true_name) (select true_name from tb_user)；的问题，因为我手动反复插入这句语句时(都成功)，也会发生这种问题，另外，从间隙的结果分析，上述推断也不成立，比如，我开始有2条数据，在第一次select后插入后pk_user应该递增到4，下一次select4条应该是5-8才是，如果产生间隙也应该是5-8，从9开始才是

MySQL 数据库

2010年3月04日 13:07

leebin1986
10
0 0 0

5个答案按时间排序按投票排序

0 0

采纳的答案

引用

我用mysql试了，反复执行
insert into tb_user (true_name) (select true_name from tb_user)；
没有问题

很奇怪，你可以换一个mysql试试看，会不会还有这个问题，另外，看看你现在用的mysql的版本，换个不同的版本试试看。

2010年3月05日 13:05

robertliudeqiang
2195
0 0 0

添加评论

0 0

我一直试到序列号256都是连续的。

2010年3月05日 13:06

robertliudeqiang
2195
0 0 0

添加评论

0 0

手动插入反复执行是insert into tb_user (true_name) values ('jack'); insert into tb_user (true_name) values ('tom'); 是没有问题的，我其实是觉得会不会是 
insert into tb_user (true_name) (select true_name from tb_user)；语句出的毛病