MySQL中删除数据后主键id重置的问题

朴灿烈づ我的快乐病毒、 2022-12-30 07:42 172阅读 0赞

# MySQL中删除数据后主键id重置的问题 #

创建数据库

create database db1;
    use db1;
    create table user3(
        id int primary key auto_increment,
        username varchar(16) not null unique ,
        password varchar(16) not null,
        createDatetime date
    );
    
    insert into user3 (username,password,createDatetime) values
    ("张伟","pass1","2019-07-11"),("王伟","pass2","2019-07-11"),
    ("王芳","pass3","2019-07-12"),("李伟","pass4","2019-07-11"),
    ("王秀英","pass5","2019-07-12"),("李秀英","pass6","2019-07-11"),
    ("李娜","pass7","2019-07-10"),("张秀英","pass8","2019-07-10"),
    ("刘伟","pass9","2019-07-12"),("张敏","pass10","2019-07-10");

创建数据表

use db1;
    create table user3(
        id int primary key auto_increment,
        username varchar(16) not null unique ,
        password varchar(16) not null,
        createDatetime date
    );

在数据表中添加数据

insert into user3 (username,password,createDatetime) values
    ("张伟","pass1","2019-07-11"),("王伟","pass2","2019-07-11"),
    ("王芳","pass3","2019-07-12"),("李伟","pass4","2019-07-11"),
    ("王秀英","pass5","2019-07-12"),("李秀英","pass6","2019-07-11"),
    ("李娜","pass7","2019-07-10"),("张秀英","pass8","2019-07-10"),
    ("刘伟","pass9","2019-07-12"),("张敏","pass10","2019-07-10");

选中最后一行数据进行删除后插入新数据

delete from user3 where password="pass10";
    
    insert into user3 (username,password,createDatetime) values
    ("张三","pass","2019-7-13"),("李四","pass52","2019-7-10")

此时发现新插入的数据id未能从10开始

![1588223935639][]

为达到id按序输出，我们需要对id进行初始化后重新设置

alter table user3 drop id;
    alter table user3 add id int not null primary key auto_increment first;

运行后，我们就可以发现id重新按照序列输出了  
![1588224107478][]

[1588223935639]: /images/20221120/ae63610ca7bb429c9530d4a1a9c013a1.png
[1588224107478]: /images/20221120/ff89681b167340f0b1c66dddd419ab1e.png