MySQL / MariaDB 重置自增 ID (AUTO_INCREMENT)教程 - 完美保留表数据的终极解决方案

A kitten
蒋川
B 端产品经理,卡拉云联合创始人
阅读时长 1 分钟

MySQL 重置自增 ID (AUTO_INCREMENT)教程  - 完美保留表数据的终极解决方案

MySQL 表的主键一般都要使用自增 ID (AUTO_INCREMENT) ,当你删除其中一条,会造成自增 ID 不连续,这可能导致需要使用 ID 进行判断时的不准确,这时可能会考虑重置自增 ID。

本教程将介绍多种重置 MySQL 自增 ID 的方法,特别是不会删除数据的重置自增 ID 法,非常好用。另外推荐一下只要会写 MySQL 就能根据自己的工作流搭建工具的低代码平台卡拉云,详情请见本文文末。

一. 创建测试示例

首先我们创建一个数据库 kalacloud_demo 及表 kalacloud_table_demo 来作为本教程的演示示例:

CREATE DATABASE kalacloud_demo;

USE kalacloud_demo;

CREATE TABLE kalacloud_table_demo (
  id INT NOT NULL AUTO_INCREMENT,
  test VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (id)
);

然后我们向 kalacloud_table_demo 表中插入一组测试数据:

INSERT INTO kalacloud_table_demo(test)
VALUES('kalacloud 1'),
      ('kalacloud 2'),
      ('kalacloud 3');

我们来使用 SELECT 看一下效果:

SELECT * FROM kalacloud_table_demo;

新建自增ID演示示例

我们可以看到示例表中,id 列的值是连续的1、2、3

接着,我们删除 ID 3,然后再插入一条新数据,看看变化。

DELETE FROM kalacloud_table_demo WHERE ID = 3;

INSERT INTO kalacloud_table_demo(test)
VALUES('kalacloud insert 3');

SELECT * FROM kalacloud_table_demo;

插入新数据

从返回结果可以看到,我们删除 ID 3 这一行后,再插入一行 ID 自增是从 4 开始的。接下来,我们来讲解如何重置 ID,使它回复到连续数。

二. 使用 ALTER TABLE 重置自增 ID

接前文示例,我们先删掉 ID 4 ,再使用 ALTER TABLE 重置表 ID:

DELETE FROM kalacloud_table_demo WHERE ID = 4;

使用 ALTER TABLE 重置自增 ID:

ALTER TABLE table_name AUTO_INCREMENT = value;
  • table_name 为当前要重置自增 ID 的表名。
  • value 必须是大于当前表中最大 ID 数,本示例中当前表最大数为 2,我们可以设置成 3。

我们使用以下命令将 kalacloud_table_demo 自增 ID 重置为 3

ALTER TABLE kalacloud_table_demo AUTO_INCREMENT = 3;

接着,我们插入一行数据,看一下效果。

INSERT INTO kalacloud_table_demo(test)
VALUES('kalacloud insert 3');

SELECT * FROM kalacloud_table_demo;

使用 alfter table 更新自增 ID

从返回结果我们可以看到,新插入的数据不再从 4 开始,而是按照我们刚刚重置的数,从 3 开始。

三. 使用 TRUNCATE TABLE 重置自增 ID

TRUNCATE TABLE 原理是删除掉表中全部数据并释放空间,达到重置 ID 的效果,但它与 DROP TABLE (直接删除表)不同,它不会删掉表本身,也不会删掉表的定义,仅清空表数据。

TRUNCATE TABLE kalacloud_table_demo;

TRUNCATE TABLE 重置自增 ID

表数据被清空后,新写入的数据,肯定就从 ID 1 重新开始了。

但在现实操作中,我们怎么可能就为了重置 ID ,把一个填满内容的数据库清空呢,这个操作在多数场景中并不实际。

接下来,我们来讲解如何使用 直接删除 ID 法和替换法来保住数据的前提下,重置 ID。

四. 保留数据重置 - 直接删除 ID 法

此方法的原理是直接把 ID 列删掉,然后重新新建 ID 列,从而达到保留数据,重置 ID 的效果。

接上文数据,大家可以跟着本教程一起重置演示的表数据,方便继续演示操作:

TRUNCATE TABLE kalacloud_table_demo;

INSERT INTO kalacloud_table_demo(test)
VALUES('kalacloud 1'),
      ('kalacloud 2'),
      ('kalacloud 3');

DELETE FROM kalacloud_table_demo WHERE ID = 3;

INSERT INTO kalacloud_table_demo(test)
VALUES('kalacloud insert 3');

SELECT * FROM kalacloud_table_demo;

重置示例数据

我们使用 SELECT 查看表数据,可以看到 ID 为 1、2、4

演示数据就绪,接着我们开始重置 ID

我们先删除 ID 列:

ALTER  TABLE kalacloud_table_demo DROP id;

然后再插入新 ID 列:

ALTER  TABLE  kalacloud_table_demo ADD id mediumint(6) PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;

最后用 SELECT 看一下表的变化:

SELECT * FROM kalacloud_table_demo;

可以看到表 ID 已经重置。

表 ID 已经重置

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

五. 保留数据重置 - 两步替换 ID 法

为了保证删除 ID 列后,再加入新 ID 列这种方法不会打乱原有数据的顺序,我们可以使用更保守一些的替换法,达到重置 ID 的效果。

我们重置一下示例数据:

TRUNCATE TABLE kalacloud_table_demo;

INSERT INTO kalacloud_table_demo(test)
VALUES('kalacloud 1'),
      ('kalacloud 2'),
      ('kalacloud 3');

DELETE FROM kalacloud_table_demo WHERE ID = 3;

INSERT INTO kalacloud_table_demo(test)
VALUES('kalacloud insert 3');

SELECT * FROM kalacloud_table_demo;

重置示例数据

接下来,我们使用替换法把示例数据的 ID 重置为 1、2、3

首先,取消 kalacloud_table_demo 表中 ID 列的自增和主键的定义:

ALTER TABLE kalacloud_table_demo MODIFY COLUMN id int NOT NULL FIRST ,DROP PRIMARY KEY;

在表里新增 id_renew 列,设置为自增(AUTO_INCREMENT)主键(PRIMARY KEY),我们将使用这个列来替换 ID 列。

ALTER TABLE kalacloud_table_demo ADD COLUMN id_renew int NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (id_renew);

新增的id_renew列已经是连续数字

使用 SELECT 查看当前表情况,可以看到新增的 id_renew 这一列的顺序已经是连续的了。

接着,我们可以删除掉原来乱序的 ID 列

ALTER TABLE kalacloud_table_demo DROP COLUMN id;

最后,我们将 id_renew ,重命名,更为 id

ALTER TABLE kalacloud_table_demo CHANGE COLUMN id_renew id int(7) NOT NULL AUTO_INCREMENT FIRST;

我们使用 SELECT 来看一下效果:

使用ID替换法重置自增ID

可以看到,在保护数据以及数据顺序不被打乱的前提下,我们重置了 ID 列的数字顺序。

六. 总结

本教程详细讲解了四种 MySQL 重置自增 ID 的方法,他们各有各的特点。

  • ALTER TABLE 重置法:适合表格刚刚被打乱,乱序的位置之后的数据量不多的情况
  • TRUNCATE TABLE 重置法:适合表格中数据不重要,可清空重来。
  • 保留数据重置 - 直接删除 ID 法:数据量较小时。
  • 保留数据重置 - 两步替换 ID 法:前一种方法的保守方法,数据更安全。

接着推荐一下卡拉云,只要你会写 MySQL ,就能使用卡拉云搭建自己的数据工具,比如,数据看板,企业 CRM、ERP,权限管理后台,对账系统等。

卡拉云是新一代低代码开发工具,免安装部署,可一键接入包括 MySQL 在内的常见数据库及 API。可根据自己的工作流,定制开发。无需繁琐的前端开发,只需要简单拖拽,即可快速搭建企业内部工具。数月的开发工作量,使用卡拉云后可缩减至数天,欢迎免费试用卡拉云

卡拉云可一键接入常见的数据库及 API

卡拉云可一键接入常见的数据库及 API,快速接入简单轻松。

卡拉云可根据公司工作流需求,轻松搭建数据看板或其他内部工具,并且可一键分享给组内的小伙伴。

使用卡拉云轻松搭建企业内部工具

下图为使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台,仅需要简单拖拽即可快速生成前端组件,只要会写 SQL,便可搭建一套趁手的数据库工具。欢迎免费试用卡拉云

使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台

更多数据库相关教程可访问 卡拉云 查看。如果你还有什么疑问,欢迎一起讨论。我的微信 HiJiangChuan。

卡拉云联合创始人蒋川的微信

有关 MySQL 教程,可继续拓展学习:

© 2021 卡拉云 Built with ❤️ in Beijing

京ICP备15049164号-9