01
自增长列的一个小问题
之前的文章中写了一个小问题,当我们使用自增长的方式限定了一个自增长字段id以后,如果删除id=7的一条记录,再重新插入新纪录的时候,这个新纪录的id值会是多少?
针对这个问题,我们做一个试验(所有试验MySQL版本为MySQL5.7.16):
mysql:yeyztest >>select * from test1;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
+----+------+
7 rows in set (0.00 sec)
mysql:yeyztest >>delete from test1 where id=7;
Query OK, 1 row affected (0.00 sec)
mysql:yeyztest >>insert into test1 (age) values (7);
Query OK, 1 row affected (0.00 sec)
mysql:yeyztest >>select * from test1;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 8 | 7 |
+----+------+
7 rows in set (0.00 sec)
可以看到这个值已经变为8了,在查看建表语句:
mysql:yeyztest >>show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
上面的试验说明在innodb存储引擎下面这个值会一直往上递增。
然后我们把这个存储引擎改为myisam,再来看看结果:
mysql:yeyztest >>CREATE TABLE `test1` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `age` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql:yeyztest >>insert into test1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql:yeyztest >>delete from test1 where id=7;
Query OK, 1 row affected (0.00 sec)
mysql:yeyztest >>select * from test1;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
+----+------+
6 rows in set (0.00 sec)
mysql:yeyztest >>insert into test1 (age) values (7);
Query OK, 1 row affected (0.00 sec)
mysql:yeyztest >>select * from test1;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 8 | 7 |
+----+------+
7 rows in set (0.00 sec)
CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
可以看到,结果是一样的,都是会继续递增。而不会因为删除了一条记录而保持连续。从最后的show create table语句中可以看出,下一个自增长的值是9。
如果想要让这个表在删除某条记录之后,插入新记录时候自增列保持连续,现在有id为1~7的表,删除id=6和id=7的记录,然后我们需要在插入的时候让自增主键从6开始,有一个办法,就是重新启动该MySQL实例,这个之前做过实验,确认是可行的。有兴趣可以自己动手试一下。
再来看一个有意思的现象,我们使用innodb存储引擎的时候,加入我们提前插入一个记录的id值大于当前的auto_increment的值,看看结果:
mysql:yeyztest >>CREATE TABLE `test1` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `age` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql:yeyztest >>insert into test1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql:yeyztest >>insert into test1 values (10,10);
Query OK, 1 row affected (0.00 sec)
mysql:yeyztest >>show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
可以看到,我们创建了一个表,先插入id=1-7的值,再接着手动插入一条id=10的记录,那么下一个自增长的id不会从8开始了,而是会变为11开始。
再看一个例子:
mysql:yeyztest >>CREATE TABLE `test1` (
-> `id` int(11) NOT NULL,
-> `age` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql:yeyztest >>insert into test1 values (4,4);
Query OK, 1 row affected (0.00 sec)
mysql:yeyztest >>alter table test1 modify id int not null auto_increment;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql:yeyztest >>show create table test1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
在上面这个例子中,我们首先创建了一个表,这个表中没有自增列,然后插入id=4的数据,插入之后修改表的id列为自增属性,然后再查看该表的自增列下一个值,发现自动匹配id=4的下一个值id=5。
这个点也比较重要,如果没有自动匹配的话,自增长属性将会从1开始,这样,再插入三条记录之后,这个表就无法再插入记录了,因为id=4的记录已经存在了。