一. 基础知识

注：int类型为4字节，最大值就到2147483648，以下为MySQL自增主键用满的内容截图：

注：bigint为8字节，对于自增主键存储足够大，是不用考虑自增ID达到最大值这个问题

二、解决方案

（一）实验环境准备：

root@localhost [test]>create table demo(id int not null auto_increment primary key,name varchar(32) default null);
Query OK, 0 rows affected (0.03 sec)

root@localhost [test]>insert into demo (name) VALUES('n1'),('n2'),('n3');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@localhost [test]>select * from demo ;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  2 | n2   |
|  3 | n3   |
+----+------+
3 rows in set (0.00 sec)

root@localhost [test]>delete from demo where id = 3 ;
Query OK, 1 row affected (0.01 sec)

root@localhost [test]>insert into demo(name) VALUES('n4');
Query OK, 1 row affected (0.00 sec)

root@localhost [test]>select * from demo ;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  2 | n2   |
|  4 | n4   |
+----+------+
3 rows in set (0.00 sec)

注：从返回结果可以看到，我们删除 ID 3 这一行后，再插入一行 ID 自增是从 4 开始的。那么需要如何重置 ID，使它回复到连续数？

（二）使用alter table方式重置id

root@localhost [test]>delete from demo where id = 4 ;
Query OK, 1 row affected (0.01 sec)
##注：当前要重置自增 ID 的表名。value 必须是大于当前表中最大 ID 数，本示例中当前表最大数为 2，我们可以设置成 3
root@localhost [test]>alter table demo auto_increment = 3; 
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]>insert into demo(name) VALUES('n4');
Query OK, 1 row affected (0.01 sec)
##插入数据后，可以看到id自增值为3
root@localhost [test]>select * from demo ;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  2 | n2   |
|  3 | n4   |
+----+------+
3 rows in set (0.00 sec)

（三）truncate table重置id

root@localhost [test]>truncate table demo;
Query OK, 0 rows affected (0.06 sec)

root@localhost [test]>show table status where name ='demo' \G
*************************** 1. row ***************************
           Name: demo
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2024-09-28 12:22:02
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.06 sec)

注：表数据被清空后，新写入的数据，肯定就从 ID 1 重新开始了，但在现实操作中多数场景中并不实际。

（四）通过重建id列处理

##先删除id列
root@localhost [test]>alter table demo drop id ;
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@localhost [test]>select * from demo;
+------+
| name |
+------+
| n1   |
| n2   |
| n3   |
+------+
3 rows in set (0.00 sec)
## 选择bigint类型，插入新的id
root@localhost [test]>alter table demo add id bigint(8)primary key not null auto_increment first ;
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 1

root@localhost [test]>select * from demo;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  2 | n2   |
|  3 | n3   |
+----+------+
3 rows in set (0.00 sec)

注：这种方法对于数据量较大的表有一定风险。此方法在删除 ID 后，重新加入新 ID 时，应用不停写有可能顺序会被打乱，保险起见，我们可以使用另一种在保护数据的前提下，重置 ID 的方法

（五）通过新增列替换处理

实验环境准备：

truncate table table_demo ;
insert into table_demo(test) VALUES('kalacloud 1'),('kalacloud 2'),('kalacloud 3');
delete from table_demo where id = 3 ;
insert into table_demo(test) VALUES('kalacloud insert 3');
select * from table_demo ;
+----+--------------------+
| id | test               |
+----+--------------------+
|  1 | kalacloud 1        |
|  2 | kalacloud 2        |
|  4 | kalacloud insert 3 |
+----+--------------------+

注：table_demo表的数据为1 2 4

root@localhost [test]>show create table demo\G
*************************** 1. row ***************************
       Table: demo
Create Table: CREATE TABLE `demo` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
## 取消id列自增主键定义
root@localhost [test]>alter table demo modify column id bigint not null first ,drop primary key ;
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0
##新增 id_new 列，设置为自增（AUTO_INCREMENT）主键（PRIMARY KEY），一会使用这个列来替换 ID 列
root@localhost [test]>alter table demo add column id_new bigint not null auto_increment first,add primary key(id_new);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]>select * from demo ;
+--------+----+------+
| id_new | id | name |
+--------+----+------+
|      1 |  1 | n1   |
|      2 |  2 | n2   |
|      3 |  3 | n3   |
+--------+----+------+
3 rows in set (0.00 sec)
## 删除掉原来乱序的 ID 列
root@localhost [test]>alter table demo drop column id ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
##将id_new重命名为id
root@localhost [test]>alter table demo change column id_new id bigint(8) not null auto_increment first ;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1