MySQL INSERT IF（自定义if语句） [英] MySQL INSERT IF (custom if statements)

问题描述

首先，以下是问题的简明摘要：

是否可以有条件地运行 INSERT ？
类似这样的东西：

  IF（expression）INSERT ... 
  

现在，我知道我可以用一个存储过程做到这一点。
我的问题是：我可以在我的查询中这样做吗？

---

我们假设我们有以下2个表：

  products：id，qty_on_hand 
 orders：id，product_id，qty 
  

订购20只巫毒娃娃（产品编号2）。

我们首先检查是否有足够的数量：

  SELECT IF（
（SELECT SUM（qty）FROM orders WHERE product_id = 2）+ 20 
< = 
（SELECT qty_on_hand FROM products WHERE id = 2）
，'true'，'false'）; 
  

然后，如果计算结果为true，我们运行 INSERT 查询。

到目前为止很好。

---

如果两个订单在完全相同的时间进入，他们可能在任何一个人输入订单之前都读取手上的数量。
然后他们都将下订单，因此超过 qty_on_hand 。

---

所以，回到问题的根：

可以有条件地运行 INSERT 语句，以便我们可以将这两个查询合并成一个。

我搜索了很多，只有条件 INSERT 语句，我可以找到 ON DUPLICATE KEY ，这显然不适用这里。

解决方案

  INSERT INTO TABLE 
 SELECT value_for_column1，value_for_column2，... 
 FROM where 
 WHERE your_special_condition 
  

如果没有从select返回任何行（因为您的特殊条件为false）。

使用您的问题模式（假设您的 id 列为 auto_increment ）：

 插入订单（product_id，qty）
 select 2，20 
其中（SELECT qty_on_hand FROM products WHERE id = 2） > 20。 
  

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

好主意btw！

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

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?

Let's assume we have the following 2 tables:

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

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');

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

---

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.

---

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?

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

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

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.

Nice idea btw!

这篇关于MySQL INSERT IF（自定义if语句）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！