MySQL -使用多个主键进行单次自动递增，或者在MySQL中使用max()+1进行安全插入

我正在寻找一个解决方案，两个有我的第二个主键列将自动递增取决于第一个主键。
考虑以下示例案例，以理解我的目标的各个方面：
一个orderItems表，其中存储了order_id (INT)和order_item (SMALLINT)沿着与问题无关的几个列。您可以假设order_item列作为自动递增的值，就像收据的排序键一样。
主要目标是插入orderItems表，并使order_item列自动递增最大值order_item（+1），即存在于相同的order_id中。基本上插入一个orderItems行order_id=1应该设置order_item=1。使用另一个order_id进行第二次插入假设我们的二阶=2，应该使用它自己的自动增量和设置orderItem=1，由于插入另一个订单。

我想到/尝试过的可能解决方案：

我尝试在order_items列上设置一个自动递增，假设它会自动递增而不关心第一个主键列（order_id），结果正如我所假设的那样。因此插入一个新的order_id自动递增，继续使用以前插入的值。
首先想到的是如何在没有自动递增的情况下解决这个问题，选择MAX（order_item）+1并插入新行和一个额外的查询，我认为这是不安全的，因为并发查询可能会同时插入。
第二个想法是在插入查询中使用最大选择。然而，尝试在插入的值中使用SELECT MAX(order_item)+1 FROM orderItems，被MySQL使用版本8.0.30禁止（错误：不能在FROM子句中指定要更新的目标表'orderItems'）。
我认为通过使用SELECT ... FOR UPDATE插入会做的伎俩，因为它锁定了我的条目时，更新，并很快就足够开明的思考第二次关于它和思想，我如何能够锁定的东西还没有存在（我知道，做轻）让我停下来，并放弃这个想法非常快。
我还考虑过在insert和know之前创建一个TRIGGER（或者至少听说过它），它会触发减慢查询速度，应该谨慎使用。也许你可以告诉，它是否会比将它分成两个查询（第一个用于检索MAX（order_item），第二个用于插入）更慢。
我不知道使用TRIGGER或我不得不提出的每一个解决方案的瓶颈是否可以通过使用MySQL提供的一些本地解决方案来防止，我还没有想到，如果你对此有任何建议，我将不胜感激。
也许有一种方法可以配置AUTO_INCREMENT流，以实现我所期望的方法，即根据第一个pk列自动递增第二列。

您可以使用TRIGGER BEFORE INSERT来计算使用相同order_id插入了多少行，并确保order_items表中的order_id不是主键。

CREATE TABLE order_items(
    no INT AUTO_INCREMENT PRIMARY KEY, 
    order_id INT, 
    order_item INT, 
    qty SMALLINT
);

-- DROP TRIGGER BIorder_items;
DELIMITER $$ 
CREATE TRIGGER BIorder_items 
BEFORE INSERT ON order_items FOR EACH ROW 
BEGIN 
    SET NEW.order_item = (SELECT COUNT(*)+1 FROM order_items WHERE order_id = NEW.order_id); 
END$$ 
DELIMITER ;

INSERT INTO order_items(order_id, qty) VALUES(1, 30),(2, 10),(2, 15),(2, 1),(1, 10);
SELECT * FROM order_items;

使用INSERT INTO SELECT不使用TRIGGER

SET @order_id = 1;
INSERT INTO order_items(order_id, qty, order_item)    
WITH cte(c) AS (
    SELECT COUNT(*)+1 FROM order_items WHERE order_id = @order_id
)
SELECT @order_id, 10, c FROM cte;

SELECT * FROM order_items;

MySQL -使用多个主键进行单次自动递增，或者在MySQL中使用max()+1进行安全插入

1条答案

相关问题

热门标签

最新问答