这个贴子中,myisam类型表 指定表选项auto_increment=36832,id为int
最大值为126的情况下,插入127报重复键,重启后仍报错,插入150正常
mysqlcheck修复后正常,估计是表的index(key)与表数据不一致了,在没有损坏情况下无法重现这个错误;
应该是程序曾经异常终止,导致表损坏了(表与索引不一致);当指定127插入时,虽然表中无127这条记录,但索引中已经存在key
出问题的时候我用了check 列出了类似这样的错误：
error | Found xxxx keys of yyyy
error | corrupt
其中xxxx != yyyy
select max(id) from Syslog;
返回的值也不等于 最后一条记录的ID
下面的测试有利于清楚解释表中auto_increment列与auto_increment计数器对像(其实是一个变量)
的关系,及重新gap中数据的问题.
没有指定表选项的情况
mysql> CREATE TABLE `sys_log` (
-> `log_id` int(11) NOT NULL AUTO_INCREMENT,
-> name varchar(30),
-> PRIMARY KEY (`log_id`)
-> ) ENGINE=innodb DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into sys_log values(126,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into sys_log values(127,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sys_log values(128,'aaa'); ==>指定值128给列及计数器
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from sys_log;
+--------+------+
| log_id | name |
+--------+------+
| 126 | aaa |
+--------+------+
1 row in set (0.00 sec)
mysql> insert into sys_log values(127,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> select * from sys_log;
+--------+------+
| log_id | name |
+--------+------+
| 126 | aaa |
| 127 | aaa | ==>回滚后,如果insert给gap范围内指定值,则赋值给列指定值,而auto increment计数器的值不变仍为128
+--------+------+
2 rows in set (0.00 sec)
mysql> insert into sys_log values(null,'aaa'); ==>随后,insert给null/0时,则会从计数器取值并+1后重新赋值给计数器及列值
Query OK, 1 row affected (0.00 sec)
mysql> select * from sys_log;
+--------+------+
| log_id | name |
+--------+------+
| 126 | aaa |
| 127 | aaa |
| 129 | aaa | ==>
+--------+------+
3 rows in set (0.00 sec)
指定表选项为 AUTO_INCREMENT = 1000
mysql> drop table sys_log;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE `sys_log` (
-> `log_id` int(11) NOT NULL AUTO_INCREMENT,
-> name varchar(30),
-> PRIMARY KEY (`log_id`)
-> ) ENGINE=innodb AUTO_INCREMENT = 1000 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into sys_log values(126,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into sys_log values(127,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sys_log values(128,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from sys_log;
+--------+------+
| log_id | name |
+--------+------+
| 126 | aaa |
+--------+------+
1 row in set (0.00 sec)
mysql> insert into sys_log values(127,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> select * from sys_log;
+--------+------+
| log_id | name |
+--------+------+
| 126 | aaa |
| 127 | aaa |
+--------+------+
2 rows in set (0.00 sec)
mysql> insert into sys_log values(null,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> select * from sys_log;
+--------+------+
| log_id | name |
+--------+------+
| 126 | aaa |
| 127 | aaa |
| 1000 | aaa | ==>取值为1000
+--------+------+
3 rows in set (0.00 sec)
因为rollback造成序列间隙(gap),随后的insert语句可以指定gap中的值,这时mysql会正确赋值给列值,而序列计数器的值仍是rollback掉最后一条insert语句获得的序列值,所以,如果随后的insert语句是这三种情况:不指定/指定为null/指定为0 时,insert是从计数器获得值后+1再赋值给计数器及列值!
当建表时指定了auto_increment表选项时,则受这个option制约,计数器的起始值是选项的指定值;
总之,间隙中的序列值是可以重用的!
