MySQL：如何在已经使用的数据表中增加一个自动递增的字段_mysql 增加一个自增字段 并有值

目录

一、需求

二、实现步骤

（一）数据表students

（二）添加整型字段

（三）更新SID字段的值

1、使用用户定义的变量和JOIN操作

2、用SET语句和@rownum变量

（1）操作方法

（2）实际操作

（3）操作效果

（四）设置AUTO_INCREMENT（可选不推荐）

---

一、需求

        在MySQL中，数据表students已经使用了一段时间，其中存在很多记录。但是数据表students在建立之初，没有设定一个id字段，因此给统计和分析带来不便。现在想在数据表students增加一个整型的非主键字段SID，并希望这个字段的值从1开始递增，不能影响现存的数据。

        这种在数据表中自动递增的字段，在视频监控平台的众多应用中非常多，合理的自动递增字段，对整个系统的数据表的调用、统计非常有用。

二、实现步骤

（一）数据表students

       如下图所示，为数据表students的定义，可以看出学号studentNo是主键，不能为空；name也不能为空。

（二）添加整型字段

        需要在students表中添加一个整型字段SID。由于这个字段不是主键，并且是递增的，但不需要它作为表的唯一标识，可以简单地将它添加为普通整型字段，而不立即设置AUTO_INCREMENT属性（因为AUTO_INCREMENT通常与主键一起使用）。

        命令如下：

ALTER TABLE students ADD COLUMN SID INT;

        实际操作如下：

        查看数据表students的结构，如下：

        由图可以看出，字段添加成功。

（三）更新SID字段的值

        要求SID字段的值从1开始递增，那需要更新这个字段以反映这一点。由于MySQL没有直接的内置函数来按行号递增更新列（像SQL Server中的ROW_NUMBER()函数那样），需要使用一些创造性的方法来实现这一点。

1、使用用户定义的变量和JOIN操作

        一种方法是使用用户定义的变量和UPDATE语句的JOIN操作（或者更具体地说，是一个子查询或派生表），但这在MySQL中可能有些复杂，特别是当涉及到保持递增顺序与表中现有行的顺序一致时。

2、用SET语句和@rownum变量

        一个更简单但可能不是最优化的方法是使用SET语句和@rownum变量在单个查询中更新所有行。

（1）操作方法

       操作命令的语法如下：

SET @rownum := 0;
 
UPDATE students
 
SET SID = (@rownum := @rownum + 1)
 
ORDER BY <some_column> ASC;  -- <some_column>应该是希望根据它来递增SID的列，比如ID或创建日期

        实际操作语句如下：

SET @rownum := 0;
 
UPDATE students
 
SET SID = (@rownum := @rownum + 1)
 
ORDER BY studentNo ASC; 

（2）实际操作

        实际操作如下：

（3）操作效果

       上述操作完成后，查看数据表，可以看出SID的数据实现了递增，达到需求的目标。具体如下图：

        注意：虽然上面的UPDATE语句在MySQL中通常是有效的，但官方文档并没有明确保证ORDER BY在UPDATE语句中的行为是确定的，特别是在没有LIMIT子句的情况下。然而，在大多数情况下，它按预期工作，并且是按ORDER BY指定的顺序递增SID的。

（四）设置AUTO_INCREMENT（可选不推荐）

        虽然SID字段不是主键，但如果确实希望MySQL在将来插入新行时自动递增这个字段（尽管这通常不是非主键字段的用途），可以尝试将其设置为AUTO_INCREMENT。但是，由于AUTO_INCREMENT通常与主键相关联，并且每个表只能有一个AUTO_INCREMENT列，因此如果students表已经有一个AUTO_INCREMENT主键，则不能这样做。

        此外，即使能够设置SID为AUTO_INCREMENT（例如，如果表没有主键或移除了现有的AUTO_INCREMENT主键），MySQL也不会在现有行上自动更新SID的值以反映递增序列；它只会在新插入的行上自动设置SID的值。

---

文章正下方可以看到我的联系方式：鼠标“点击” 下面的 “威迪斯特-就是video system 微信名片”字样，就会出现我的二维码，欢迎沟通探讨。

---