用子查询删除更有效率
问题描述:
在这里做一点垃圾收集和使用子查询,我知道这不是很有效。任何指针?用子查询删除更有效率
DELETE FROM `carts` WHERE `id` NOT IN (SELECT `cart_id` FROM `sessions`)
基本上它应该从没有在会话表中有相应的记录我的车表中删除所有记录。
答
DELETE FROM `carts` c
left outer join `sessions` s on (s.`cart_id` = c.`id`)
WHERE s.`cart_id` is null
答
http://dev.mysql.com/doc/refman/5.0/en/delete.html
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
所以
DELETE carts
FROM carts
LEFT JOIN sessions ON carts.id=sessions.cart_id
WHERE session.cart_id is null;