MySQL存储过程如何将总更新行数设置到ResultSetHeader的affectedRows

问题 #

我用最新版MySQL写了个包含三条UPDATE语句的存储过程，想让返回的ResultSetHeader里的affectedRows字段显示所有UPDATE的总更新行数，但现在默认只返回最后一条UPDATE的影响行数（测试里总共更了4行，可返回的affectedRows是1）。我已经用SELECT @rows拿到了总行数，但这会多返回一个普通对象，我不想有额外数据，只想让这个总数出现在affectedRows里，怎么在存储过程里实现？

相关代码 #

-- 前面的代码已省略

UPDATE `Column` SET `Index` = 999
  WHERE `Id` = ColumnId;
SET @rows = ROW_COUNT();
UPDATE `Column` SET `Index` = IF(NewIndex < OldIndex, `Index` + 1, `Index` - 1)
  WHERE (NewIndex <= `Column`.`Index` AND `Column`.`Index` < OldIndex)
    OR (OldIndex < `Column`.`Index` AND `Column`.`Index` <= NewIndex)
  ORDER BY IF(OldIndex < NewIndex, `Index`, -`Index`);
SET @rows = @rows + ROW_COUNT();
UPDATE `Column` SET `Index` = NewIndex
  WHERE `Id` = ColumnId;

当前返回结果 #

• 默认返回的JS对象：

  ResultSetHeader { fieldCount: 0, affectedRows: 1, insertId: 0, info: '', serverStatus: 2, warningStatus: 0 }
  

• 加了SELECT @rows后返回的对象：

  { @rows: 4 }, ResultSetHeader { fieldCount: 0, affectedRows: 1, insertId: 0, info: '', serverStatus: 2, warningStatus: 0 }
  

解决方案 #

MySQL驱动返回的affectedRows只会取最后一条DML语句的影响行数，所以要让它返回总更新行数，得让存储过程最后执行的DML语句的ROW_COUNT()等于三条UPDATE的累计行数。可以用临时表的技巧实现：

完整修改后的存储过程代码 #

DELIMITER //
CREATE PROCEDURE your_procedure_name(IN ColumnId INT, IN NewIndex INT, IN OldIndex INT)
BEGIN
    -- 用局部变量累计总行数，避免污染会话变量
    DECLARE total_rows INT DEFAULT 0;

    -- 第一条UPDATE，累计行数
    UPDATE `Column` SET `Index` = 999 WHERE `Id` = ColumnId;
    SET total_rows = total_rows + ROW_COUNT();

    -- 第二条UPDATE，累计行数
    UPDATE `Column` SET `Index` = IF(NewIndex < OldIndex, `Index` + 1, `Index` - 1)
        WHERE (NewIndex <= `Column`.`Index` AND `Column`.`Index` < OldIndex)
           OR (OldIndex < `Column`.`Index` AND `Column`.`Index` <= NewIndex)
        ORDER BY IF(OldIndex < NewIndex, `Index`, -`Index`);
    SET total_rows = total_rows + ROW_COUNT();

    -- 第三条UPDATE，累计行数（之前你漏加了这条的行数）
    UPDATE `Column` SET `Index` = NewIndex WHERE `Id` = ColumnId;
    SET total_rows = total_rows + ROW_COUNT();

    -- 生成临时表并插入对应行数的数据，再删除这些行
    -- 这样最后这条DELETE的ROW_COUNT()就是total_rows
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_row_count (dummy INT);
    -- 从系统表取数据生成total_rows行，若行数不够可换成INFORMATION_SCHEMA.COLUMNS
    INSERT INTO temp_row_count SELECT 1 FROM INFORMATION_SCHEMA.TABLES LIMIT total_rows;
    DELETE FROM temp_row_count;

    -- 可选：清理临时表，会话结束后临时表也会自动删除
    DROP TEMPORARY TABLE IF EXISTS temp_row_count;
END //
DELIMITER ;

原理说明 #

1. 用局部变量total_rows代替会话变量@rows，避免影响其他会话的变量；
2. 每条UPDATE执行后，用ROW_COUNT()获取当前语句的影响行数，累加到total_rows里；
3. 最后创建临时表，插入刚好total_rows行数据，再删除这些行——这条DELETE语句的影响行数就是total_rows；
4. 因为DELETE是存储过程最后执行的DML操作，驱动返回的ResultSetHeader里的affectedRows就会等于这个总数，而且不会额外返回任何结果集。

替代方案（递归CTE生成数据） #

如果你的MySQL版本支持递归CTE（8.0及以上），可以用更精准的方式生成临时数据，不用依赖系统表：

-- 替换上面的INSERT语句
WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < total_rows
)
INSERT INTO temp_row_count SELECT n FROM cte;

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