如何将表的IDENTITY 自增列修改为AUTO INCREMENT 自增列

---

评论 2 条

• 

  1843880570 2024-04-12 08:58:01

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

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

  1、查看全局自增值

  SHOW VARIABLES LIKE 'AUTO_INC%';
  

  • 1

  复制

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

  

  01

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

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

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

  • 1
  • 2

  复制

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

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

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

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

  • 1

  复制

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

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

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

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

  SHOW TABLE STATUS FROM TEST LIKE 'test';
  

  • 1

  复制

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

  0x02. 自增值的取值问题

  1、默认取值

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

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

  注意：

  • 将 0 插入到自增列中的效果等同于插入 NULL 值；

  • 当插入记录时，如果没有为自增列指明一个值，那么也等同于插入 NULL；

  • 使用

  INSERT
  

  • 1

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

  • 情况一，插入的值与已有的编号重复，则会出现报错

  • 情况二，插入的值大于列的自增值，成功插入这条记录，并且会更新自增值为新值

  • 情况三，插入的值小于列的自增值且与已有的编号不重复，则成功插入这条记录，但自增值不会更新，如果插入的值与已有的值重复，参考情况一

  • 如果用 UPDATE 语句更新自增列，情况与 INSERT 语句相同。

  2、建表时指定

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

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

  • 1
  • 2
  • 3

  复制

  3、直接指定

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

  • 1

  复制

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

  4、修改自增字段属性

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

  • 1
  • 2

  复制

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

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

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

   

  赞同 0 反对 0

  回复

  ---
• 

  prtyaa 2024-04-17 10:18:43

  这个需要先把自增列删除，再创建自增列，语句如下
  alter table "模式名"."表名" drop identity;
  alter table "模式名"."表名" add column "列名" AUTO_INCREMENT;

  赞同 0 反对 0

  回复

  ---

  丫哈哈哈哈哈哈哈各个环节