mysql select update delete_mysql – SELECT,UPDATE,DELETE与一个SQL查询？

简短的回答是,如果不将额外的查询包装在触发器或过程中,则无法实现.

您可以在事务中执行此操作,而无需SELECT,但它将需要3个查询：

START TRANSACTION;

UPDATE cart_items

SET quantity = quantity - 1

WHERE cart_id = {$cart_id}

AND id = {$cart_item_id};

DELETE

FROM cart_items

WHERE quantity = 0

AND cart_id = {$cart_id}

AND id = {$cart_item_id};

DELETE c

FROM cart c

LEFT JOIN cart_items ci

ON ci.cart_id = c.id

WHERE c.id = {$cart_id}

AND ci.cart_id IS NULL;

COMMIT;

最后一个DELETE将cart连接到cart_items,如果没有找到则删除购物车(cart_items字段为NULL).

我已经包含了可用的标识符来加速DELETE,虽然没有它们应该没问题.它只是寻找并拿起任何其他数量0项或空车.