MySQL-从选择查询插入查询中更新字段

Question

我有以下两个表A和B。

我想将表 A 中的记录插入表 B 中，但在将新记录添加到表中时使用新的 orderNum 值表B。

我有以下查询，但未按预期工作。

INSERT INTO B (refId, userId, orderNum)
SELECT id, '1', (SELECT count(refId) + 1 FROM B WHERE userId = 1) as orderNum
FROM A 
WHERE is_mendatory IS NOT NULL
ORDER BY is_mendatory ASC

假设表 B 中有 3 条记录，并且使用 SELECT 查询我将再插入 5 条记录。因此，这 5 条新记录应插入 orderNum 为 4,5,6,7,8 的订单号。

但目前它仅将 4 设置为新 5 条记录的 orderNum。

有人可以对此提出建议吗？

编辑:

我在 orderNum 字段中获得了 4 个新 5 条记录。

Answer 1

创建一个插入前触发器，为每个 INSERT 计算 B 中的行数，将数字存储在变量中，用行数更新表。

DELIMITER //

CREATE TRIGGER orderNum_after_insert
BEFORE INSERT
   ON B FOR EACH ROW  
BEGIN

SET @orderNo:=(SELECT COUNT(*) FROM B);
SET NEW.orderNum=@orderNo;

END; //

DELIMITER ;