自增长列的一个小问题

AsiaYe

发布于 2019-11-06 11:47:11

8340

发布于 2019-11-06 11:47:11

文章被收录于专栏：DBA随笔

自增长列的一个小问题

之前的文章中写了一个小问题，当我们使用自增长的方式限定了一个自增长字段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的记录已经存在了。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-07-18，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库