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

发布于

环境说明： MySQL 5.7 、MySQL 8.0

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

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

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

0x01. 查看自增值

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

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

1、查看全局自增值
代码语言：javascript
复制
SHOW VARIABLES LIKE 'AUTO_INC%';

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

01

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

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

代码语言：javascript
复制
SET @@AUTO_INCREMENT_INCREMENT=新初始值;
SET @@AUTO_INCREMENT_OFFSET=新步长;

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

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

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

代码语言：javascript
复制
SHOW TABLE STATUS FROM [数据库名] LIKE [表名];

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

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

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

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

代码语言：javascript
复制
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=自增值” 来指定一个自增的初始值，比如：

代码语言：javascript
复制
CREATE TABLE TEST{
    -- 建表语句
}AUTO_INCTEMENT=自增值;
3、直接指定
代码语言：javascript
复制
ALTER TABLE [表名] AUTO_INCREMENT=自增值;

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

4、修改自增字段属性
代码语言：javascript
复制
ALTER TABLE [表名]
    MODIFY [字段名] [字段类型和约束条件], AUTO_INCREMENT=自增值;

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

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

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

#数据库#auto#increment#mysql#博客