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

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

知识分子没文化
2021-10-31 / 0 评论 / 58 阅读 / 正在检测是否收录...

目录:

环境说明:

MySQL 5.7 、MySQL 8.0

搭完博客之后,有时会创建文章来测试一些显示效果,这些文章并不发布,而是测试完以后直接删除。这就导致我以前连续的文章其实编号是不连续的,作为一个强迫症晚期患者,看着很不舒服。在忍受了这么长时间以后,趁着给博客换域名的时机,我把所有的文章编号全部改成了连续的,可算是舒服多了。

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

所以我想着要修改数据表里的 AUTO_INCREMENT 属性的自动递增值,查了一些资料之后,便有了这篇博客。

0x01. 查看自增值

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

1、查看全局自增值

SHOW VARIABLES LIKE 'AUTO_INC%';

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

01

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

修改自增值命令:

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

注意:这个表示的是数据库全局的自增设置,因此修改只会影响到下次新增的 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 提交更改。

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

0

评论 (0)

取消