如何通过Python向MySQL数据库添加存储条目行号的列

关于ROW_NUMBER()的适用性 #

ROW_NUMBER()是MySQL 8.0+提供的窗口函数，仅用于查询时动态计算排序序号，无法在数据写入时自动为新字段赋值，因此不需要用它来实现写入自动生成行号的需求，仅在你需要对存量数据按自定义规则批量编排行号时可以使用该函数。

无需查询总行数的实现方案 #

根据你的行号规则不同，可选以下两种无需手动查询表总行数的方案：

• 方案1：全局递增连续行号（按插入顺序生成）
  直接将行号字段设置为AUTO_INCREMENT自增属性即可，MySQL会自动维护该字段的递增赋值，全程不需要你做额外的计数查询：

  -- 已有表新增自增行号列
  ALTER TABLE 你的表名
  ADD COLUMN row_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '行号',
  ADD PRIMARY KEY (row_id);
  

  执行上述语句后，存量数据会自动按插入顺序填充连续行号，后续Python脚本写入数据时不需要给该字段传值，MySQL会自动为新插入的数据生成下一位递增序号。

• 方案2：按指定字段分组的组内行号
  如果你的行号需要按某个维度分组排序（比如按用户ID、分类ID分组，每组内单独排序），可以通过MySQL触发器实现插入时自动计算，不需要Python端做任何处理：

  1. 先新增行号存储列

  ALTER TABLE 你的表名 ADD COLUMN group_row_id INT UNSIGNED NOT NULL COMMENT '组内行号';
  

  2. 创建插入前触发器，这里以按user_id分组为例：

  DELIMITER //
  CREATE TRIGGER calc_group_rowid_before_insert
  BEFORE INSERT ON 你的表名
  FOR EACH ROW
  BEGIN
      SET NEW.group_row_id = (
          SELECT IFNULL(MAX(group_row_id), 0) + 1
          FROM 你的表名
          WHERE user_id = NEW.user_id
      );
  END //
  DELIMITER ;
  

  配置完成后，新数据插入时会自动计算对应分组下的最新行号并赋值，不需要手动查询计数。

ROW_NUMBER()的适用场景说明 #

如果你需要对存量数据按自定义规则（比如按创建时间、某个字段大小排序）重新批量更新行号，可以使用ROW_NUMBER()实现，示例如下：

UPDATE 你的表名 t1
JOIN (
    SELECT 主键字段, ROW_NUMBER() OVER (ORDER BY 排序字段 ASC) AS new_row_id
    FROM 你的表名
) t2 ON t1.主键字段 = t2.主键字段
SET t1.row_id = t2.new_row_id;

内容的提问来源于stack exchange，提问作者AKang123.