MySQL批量插入时按userID分组取position最大值自增的实现方法

你要的批量插入可以通过INSERT ... SELECT语法实现，全程仅查询一次对应用户的最大position值，无需每条记录单独执行查询，具体实现如下：

通用写法（支持MySQL8.0+、PostgreSQL、SQL Server） #

INSERT INTO UserItemOrders (userID, itemID, position)
SELECT
    1 AS userID,
    new_items.itemID,
    base.max_pos + ROW_NUMBER() OVER (ORDER BY new_items.itemID) AS position
FROM (
    -- 此处填写需要批量插入的itemID列表
    VALUES (352), (353), (354)
) AS new_items(itemID)
CROSS JOIN (
    -- 仅执行一次查询获取对应用户的当前最大position，无历史记录时默认从1开始计数
    SELECT COALESCE(MAX(position), 0) AS max_pos
    FROM UserItemOrders
    WHERE userID = 1
) AS base;

逻辑说明：

• 内层base子查询仅执行1次，拿到userID=1的当前最大position值，示例中历史最大为3，所以max_pos为3
• ROW_NUMBER()为待插入的每条商品生成1、2、3的连续序号，和max_pos相加后得到4、5、6的连续position值，完全符合需求

低版本MySQL兼容写法（不支持窗口函数场景） #

可以通过用户变量生成行序号实现同样效果：

INSERT INTO UserItemOrders (userID, itemID, position)
SELECT
    1 AS userID,
    new_items.itemID,
    base.max_pos + (@row := @row + 1) AS position
FROM (
    SELECT 352 AS itemID UNION ALL
    SELECT 353 UNION ALL
    SELECT 354
) AS new_items
CROSS JOIN (
    SELECT COALESCE(MAX(position), 0) AS max_pos
    FROM UserItemOrders
    WHERE userID = 1
) AS base
CROSS JOIN (SELECT @row := 0) AS init_row;

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