MariaDB 11按forum_id分组实现order字段自动递增的方法

针对你的需求，以下是几种适配MariaDB 11的实现方式：

方法1：INSERT ... SELECT 直接计算插入 #

这是最直观的方式，插入时通过子查询获取对应forum_id的最大order值，加1作为新的order值。

示例代码：

INSERT INTO your_table_name (`order`, name, forum_id)
SELECT COALESCE(MAX(`order`), 0) + 1, '新主题名称', 1
FROM your_table_name
WHERE forum_id = 1;

• COALESCE用来处理首次插入某个forum_id的情况（此时MAX(order)为NULL，转成0后加1得到初始值1）
• 因为有UNIQUE('order', 'forum_id')约束，高并发下可能出现冲突报错，需要在应用层处理重试逻辑

方法2：触发器自动填充order值 #

创建BEFORE INSERT触发器，插入数据时自动计算并设置order字段，无需手动指定：

先创建触发器：

DELIMITER //
CREATE TRIGGER trigger_auto_inc_order
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    SET NEW.`order` = (
        SELECT COALESCE(MAX(`order`), 0) + 1
        FROM your_table_name
        WHERE forum_id = NEW.forum_id
    );
END //
DELIMITER ;

之后插入数据只需指定name和forum_id：

INSERT INTO your_table_name (name, forum_id) VALUES ('新主题', 2);

注意：高并发场景下可能出现竞态条件，两个线程同时插入同一forum_id时，可能算出相同的order值触发唯一约束报错，同样需要应用层重试。

方法3：CTE+窗口函数批量插入（MariaDB 10.2+支持） #

如果需要批量插入多个不同forum_id的数据，用CTE预先计算每个分组的最大order值，再批量插入：

WITH forum_max_order AS (
    SELECT forum_id, COALESCE(MAX(`order`), 0) AS max_order
    FROM your_table_name
    GROUP BY forum_id
)
INSERT INTO your_table_name (`order`, name, forum_id)
SELECT fmo.max_order + 1, '批量主题', f.forum_id
FROM (
    SELECT 1 AS forum_id UNION ALL SELECT 2
) f
LEFT JOIN forum_max_order fmo ON f.forum_id = fmo.forum_id;

高并发场景优化 #

如果是高并发环境，为避免竞态冲突，可以在插入前先锁定对应forum_id的相关行：

SELECT MAX(`order`) FROM your_table_name WHERE forum_id = 1 FOR UPDATE;

执行这条语句后再插入，能确保同一时间只有一个线程计算该forum_id的order值，但会增加锁开销，需要根据业务场景权衡。

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