MySQL INSERT IF（自定义 if 语句）

MySQL INSERT IF (custom if statements)

提问by Joseph Silber

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

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

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

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

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:

假设我们有以下 2 个表：

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:

现在，假设有 20 个巫毒娃娃（产品 ID 2）的订单进来。
我们首先检查手头是否有足够的数量：

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 INSERTquery.
So far so good.

然后，如果它评估为真，我们运行一个INSERT查询。
到现在为止还挺好。

---

---

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.

但是，并发性存在问题。
如果2个订单进来的确切的同一时间，他们可能都已经进入了为了它们中的任何一个之前阅读量手头。然后他们都会下订单，从而超过qty_on_hand.

---

---

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

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

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

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

回答by Bohemian

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 idcolumn is auto_increment):

使用问题中的架构（假设您的id列是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!

顺便说一句，好主意！

回答by Shef

Try:

尝试：

INSERT INTO orders(product_id, qty)
SELECT 2, 20 FROM products WHERE id = 2 AND qty_on_hand >= 20

If a product with idequal to 2exists and the qty_on_handis greater or equal to 20for this product, then an insert will occur with the values product_id = 2, and qty = 20. Otherwise, no insert will occur.

如果与产品id等于2存在，并且qty_on_hand是大于或等于20该产品，然后插入将与值出现product_id = 2，和qty = 20。否则，不会发生插入。

Note: If your product ids are note unique, you might want to add a LIMITclause at the end of the SELECTstatement.

注意：如果您的产品 ID 是唯一的，您可能需要LIMIT在SELECT语句的末尾添加一个子句。

回答by My Other Me

Not sure about concurrency, you'll need to read up on locking in mysql, but this will let you be sure that you only take 20 items if 20 items are available:

不确定并发性，您需要阅读 mysql 中的锁定，但这将让您确保如果 20 个项目可用，您只需要 20 个项目：

update products 
set qty_on_hand = qty_on_hand - 20 
where qty_on_hand >= 20
and id=2

You can then check how many rows were affected. If none were affected, you did not have enough stock. If 1 row was affected, you have effectively consumed the stock.

然后您可以检查有多少行受到影响。如果没有受到影响，则说明您的库存不足。如果 1 行受到影响，则您已经有效地消耗了库存。

回答by Konerak

You're probably solving the problem the wrong way.

您可能正在以错误的方式解决问题。

If you're afraid two read-operations will occur at the same time and thus one will work with stale data, the solution is to use locks or transactions.

如果您担心两个读取操作会同时发生，因此一个会处理陈旧数据，则解决方案是使用锁或事务。

Have the query do this:

让查询执行以下操作：

• lock table for read
• read table
• update table
• release lock

• 锁定表以供读取
• 读表
• 更新表
• 释放锁

回答by Andres Paul

I wanted to insert into a table using values so I found this solution to insert the values using the IF condition

我想使用值插入到表中，所以我找到了这个使用 IF 条件插入值的解决方案

DELIMITER $$

CREATE PROCEDURE insertIssue()
BEGIN
    IF (1 NOT IN (select I.issue_number from issue as I where I.series_id = 1)) THEN
        INSERT IGNORE INTO issue ( issue_number, month_published, year_published, series_id, mcs_issue_id) VALUES (1, 1, 1990, 1, 1);
    END IF;
END$$

DELIMITER ;

If you later on want to call the procedure it's as simple as

如果您稍后想要调用该过程，它就像

CALL insertIssue()

You can find more information about PROCEDURES and if conditions in this site

您可以在此站点中找到有关程序和条件的更多信息