MySQL INSERT IF(自定义 if 语句)

2021-11-20 00:00:00 if-statement concurrency insert mysql race-condition

首先，这里是问题的简明摘要:

First, here's the concise summary of the question:

是否可以有条件地运行 INSERT 语句?类似的东西:

Is it possible to run an INSERT statement conditionally? Something akin to this:

IF(expression) INSERT...

现在，我知道我可以使用存储过程来做到这一点.我的问题是:我可以在查询中执行此操作吗?

Now, I know I can do this with a stored procedure. My question is: can I do this in my query?

现在，我为什么要这样做?

Now, why would I want to do that?

假设我们有以下 2 个表:

Let's assume we have the following 2 tables:

products: id, qty_on_hand
orders: id, product_id, qty

现在，假设有 20 个巫毒娃娃(产品 ID 2)的订单进来了.
我们首先检查手头是否有足够的数量:

Now, let's say an order for 20 Voodoo Dolls (product id 2) comes in.
We first check if there's enough Quantity On Hand:

SELECT IF(
    ( SELECT SUM(qty) FROM orders WHERE product_id = 2  ) + 20
    <=
    ( SELECT qty_on_hand FROM products WHERE id = 2)
, 'true', 'false');

然后，如果结果为真，我们运行一个 INSERT 查询.
到目前为止一切顺利.

Then, if it evaluates to true, we run an INSERT query.
So far so good.

但是，并发有问题.
如果 2 个订单在 完全相同的时间进入，他们可能会在其中任何一个输入订单之前读取手头数量.然后他们都会下订单，从而超过qty_on_hand.

However, there's a problem with concurrency.
If 2 orders come in at the exact same time, they might both read the quantity-on-hand before any one of them has entered the order. They'll then both place the order, thus exceeding the qty_on_hand.

那么，回到问题的根源:
是否可以有条件地运行 INSERT 语句，以便我们可以将这两个查询合二为一?

So, back to the root of the question:
Is it possible to run an INSERT statement conditionally, so that we can combine both these queries into one?

我搜索了很多，我能找到的唯一类型的条件 INSERT 语句是 ON DUPLICATE KEY，这显然不适用于这里.

I searched around a lot, and the only type of conditional INSERT statement that I could find was ON DUPLICATE KEY, which obviously does not apply here.

推荐答案

INSERT INTO TABLE
SELECT value_for_column1, value_for_column2, ...
FROM wherever
WHERE your_special_condition

如果 select 没有返回任何行(因为您的特殊条件为 false)，则不会发生插入.

If no rows are returned from the select (because your special condition is false) no insert happens.

使用问题中的架构(假设您的 id 列是 auto_increment):

Using your schema from question (assuming your id column is auto_increment):

insert into orders (product_id, qty)
select 2, 20
where (SELECT qty_on_hand FROM products WHERE id = 2) > 20;

如果手头没有足够的库存，这将不会插入任何行，否则将创建订单行.

This will insert no rows if there's not enough stock on hand, otherwise it will create the order row.

顺便说一句好主意！

相关文章