mysql if insert,MySQL INSERT IF（自定义if语句）

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!