MySQL批量插入时如何自动生成对应ProductId的连续SortOrder

我有一个名为images的数据表，结构及现有数据如下：

需求是通过一条MySQL查询语句批量插入多行数据，自动为每行填充对应ProductId的下一个连续SortOrder值（基于该ProductId当前最大SortOrder递增）。

我原本尝试的实现方式如下：

const values = [
    [2,'source.com/456546', @max_sort_order := @max_sort_order + 1],
    [2,'source.com/237675', @max_sort_order := @max_sort_order + 1]
]

const query = "SET @max_sort_order := (SELECT COALESCE(MAX( SortOrder ), 1) FROM images i WHERE ProductId = 2);INSERT INTO images (ProductId, Source, SortOrder) VALUES ?"

connection.query({sql: query, timeout: 40000, values: [values]...

理想插入后的数据应该是：

但由于预编译值的限制，无法将@max_sort_order变量放入values数组中，请问该如何解决？

可以通过子查询+自增逻辑结合批量数据源实现，避开在values数组中写入变量的限制，以下是两种可行方案：

方案一：使用WITH子句（MySQL 8.0+支持） #

先通过CTE获取目标ProductId的最大SortOrder，再结合待插入数据源生成递增排序值：

WITH max_sort AS (
    SELECT COALESCE(MAX(SortOrder), 0) AS current_max FROM images WHERE ProductId = 2
)
INSERT INTO images (ProductId, Source, SortOrder)
SELECT 
    2 AS ProductId, 
    src.Source, 
    max_sort.current_max + ROW_NUMBER() OVER () AS SortOrder
FROM (
    SELECT 'source.com/456546' AS Source UNION ALL
    SELECT 'source.com/237675' AS Source
) src, max_sort;

在Node.js中使用时，可以动态生成UNION ALL部分，用参数化查询替换Source值以避免SQL注入。

方案二：用户变量+临时数据集（兼容低版本MySQL） #

先初始化变量，再通过临时数据源关联变量实现递增：

SET @current_max := (SELECT COALESCE(MAX(SortOrder), 0) FROM images WHERE ProductId = 2);
SET @row_num := 0;

INSERT INTO images (ProductId, Source, SortOrder)
SELECT 
    2 AS ProductId, 
    src.Source, 
    @current_max + (@row_num := @row_num + 1) AS SortOrder
FROM (
    SELECT 'source.com/456546' AS Source UNION ALL
    SELECT 'source.com/237675' AS Source
) src;

Node.js中可合并成批处理语句执行，动态生成数据源部分：

const sources = ['source.com/456546', 'source.com/237675'];
const sourceRows = sources.map(() => 'SELECT ? AS Source').join(' UNION ALL ');
const query = `
SET @current_max := (SELECT COALESCE(MAX(SortOrder), 0) FROM images WHERE ProductId = ?);
SET @row_num := 0;
INSERT INTO images (ProductId, Source, SortOrder)
SELECT ? AS ProductId, src.Source, @current_max + (@row_num := @row_num + 1) AS SortOrder
FROM (${sourceRows}) src;
`;
connection.query(query, [2, 2, ...sources], (err, results) => {
    // 处理执行结果
});

补充说明 #

• 两种方案都通过查询逻辑生成SortOrder值，完全符合预编译参数的要求；
• 若需支持多ProductId批量插入，可扩展子查询逻辑，按ProductId分组计算最大SortOrder后关联对应数据源。

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