MySQL数据表中的auto_increment自增值属性及修改

最新推荐文章于 2025-09-12 10:53:30 发布

转载最新推荐文章于 2025-09-12 10:53:30 发布 · 8.7k 阅读

7 ·

CC 4.0 BY-SA版权

原文链接：https://cloud.tencent.com/developer/article/2299300

文章标签：

#mysql #数据库

数据库#mysql 专栏收录该内容

23 篇文章

订阅专栏

本文讲述了作者如何在MySQL5.7和8.0环境下解决博客文章主键非连续的问题，通过查看和修改自增值，探讨了如何确保新数据表的自增顺序。作者介绍了查看全局和特定表的自增值方法，以及自增值的取值规则和修改策略。

环境说明： MySQL 5.7 、MySQL 8.0

长期以来，我的博客数据库中连续文章的主键编号一直都不是连续的，让我这个强迫症晚期患看着很不舒服。在忍受了这么长时间以后，趁着给博客换域名的时机，我把所有的文章编号全部改成了连续的，可算是舒服多了。

把改完主键编号的文章数据导入新数据库之后，就产生了一个新问题：现在新数据表的主键自增值还是旧数据表的主键自增值。比如说博客数据库的文章表中有80条数据，下次新创建文章的编号是以旧数据库的自增值202开始递增，生成的新文章编号是203，而不是81。如果要保持文章编号能连续的话，就只能每次发布完新博客之后再去改数据库中的编号，可谓是相当麻烦。

所以我便想着是否可以通过修改数据表里主键的 AUTO_INCREMENT 自动递增值来一步到位，查了一些资料之后，就有了这篇博客。

0x01. 查看自增值

一般来说，数据表中具有自增属性 AUTO_INCREMENT 的字段主要是数据表的主键或者具有唯一性的字段。

如果要查阅其递增值，有这么两种方式：

1、查看全局自增值

SHOW VARIABLES LIKE 'AUTO_INC%';

复制

这条命令的执行结果会返回两条数据：

AUTO_INCREMENT_INCREMENT 表示自增的初始值，AUTO_INCREMENT_OFFSET 表示自增的步长，即每次的自增量。

修改自增初始值与自增量的命令：

SET @@AUTO_INCREMENT_INCREMENT=新初始值;
SET @@AUTO_INCREMENT_OFFSET=新步长;

复制

注意：这个表示的是数据库全局的自增设置，因此修改以后只会影响到下次新增的带有 AUTO_INCREMENT 属性的列，其自增初始值与自增步长就是新设置的值，对当前已经带有 AUTO_INCREMENT 属性的列的自增初始值与自增步长不起作用。

2、查看特定数据表的自增值

要想查看某个数据表中自增字段的当前自增值，可用以下命令：

SHOW TABLE STATUS FROM [数据库名] LIKE [表名];

复制

FROM [数据库名] 与 LIKE [表名] 这两个子句是可选的。

FROM [数据库名] 表示指定查询所在的数据库；LIKE [表名] 指定该数据库下要查询的某个表，如果省略了 LIKE [表名] 子句，则表示查看该数据库下的所有表的信息。

另外，需要注意的是，表名要加引号。

比如查询 TEST 数据库下 test 表的信息：

SHOW TABLE STATUS FROM TEST LIKE 'test';

复制

查询结果中有一个字段名为 “AUTO_INCREMENT”，表示的就是自增值，该表下一条记录的编号就是这个值。

0x02. 自增值的取值问题

1、默认取值

默认情况下，自增值从1开始，每增加一条新记录，自增值便会自增 1。

所以，对于具有 AUTO_INCREMENT 属性的列，不用特意设置列值，而是直接将 NULL 值插入到自增列中去，数据库会自动根据当前的自增值生成列值。

注意：

将 0 插入到自增列中的效果等同于插入 NULL 值；
当插入记录时，如果没有为自增列指明一个值，那么也等同于插入 NULL；
使用

INSERT

语句插入记录时，如果为自增列设置了一个值，那么会出现这样三种情况：

情况一，插入的值与已有的编号重复，则会出现报错
情况二，插入的值大于列的自增值，成功插入这条记录，并且会更新自增值为新值
情况三，插入的值小于列的自增值且与已有的编号不重复，则成功插入这条记录，但自增值不会更新，如果插入的值与已有的值重复，参考情况一
如果用 UPDATE 语句更新自增列，情况与 INSERT 语句相同。

2、建表时指定

我们也可在建表时使用 “AUTO_INCREMENT=自增值” 来指定一个自增的初始值，比如：

CREATE TABLE TEST{
    -- 建表语句
}AUTO_INCTEMENT=自增值;

复制

3、直接指定

ALTER TABLE [表名] AUTO_INCREMENT=自增值;

复制

如果执行完以后没有效果，那么可以再执行一次 commit 指令以提交更改，使其生效。

4、修改自增字段属性

ALTER TABLE [表名]
    MODIFY [字段名] [字段类型和约束条件], AUTO_INCREMENT=自增值;

复制

同样的，如果执行完以后没有效果，再执行一次 commit 提交更改。

这个语句相当于直接修改自增字段的属性，包括其数据类型和约束条件。

另外，我在尝试中发现，使用 SQL 语句更改了自增值以后再执行 SHOW TABLE STATUS 语句来验证是否修改成功，本地的数据库显示自增值已经修改过来了。但是，服务器上的数据库自增值依然还是原来的值，可是，当我在重新插入一条记录时，新记录的自增值却是修改过后的值。这属实让我有点摸不着头脑，我也不太确定是不是 MySQL 版本不同的原因，也懒得再尝试了，所以把这种情况记录上来以供参考吧。

转载：MySQL数据表中的auto_increment自增值属性及修改