首先,以下是问题的简要总结:
可以有条件地运行INSERT
语句吗?
IF(expression) INSERT...
现在,我知道我可以用存储过程来实现这一点.
我为什么要这么做?
假设我们有以下两个表:
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?
我搜索了很多,我能找到的唯一一种条件INSERT
语句是ON DUPLICATE KEY
,这显然不适用于这里.