在mysql中,删除后可以返回删除的行吗? [英] In mysql can I return deleted rows after a deletion?
本文介绍了在mysql中,删除后可以返回删除的行吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在mysql中返回以下查询的已删除记录?
How to return deleted records of following query in mysql?
DELETE t1
FROM t1
LEFT JOIN t2 on (t1.t2_id = t2.id)
WHERE t2.id IS NULL OR t2.is_valid = false
背景:
$ mysql --version
mysql Ver 14.14 Distrib 5.6.23, for osx10.8 (x86_64) using EditLine wrapper
推荐答案
MySQL doesn't have the equivalent of the output
or returning
clauses provided by other databases. Your best bet is a temporary table:
CREATE TABLE TheDeletedIds as
SELECT t1.id
FROM t1 LEFT JOIN
t2
ON t1.t2_id = t2.id
WHERE t2.id IS NULL OR t2.is_valid = false;
DELETE t1
FROM t1
WHERE t1.id IN (SELECT id FROM TheDeletedIds);
然后,您刚刚创建的表具有所需的ID.
Then the table you just created has the ids you want.
注意:使用新创建的表进行删除很重要.否则,另一个线程/进程可能会在捕获ID的时间和删除ID的时间之间更改数据.
Note: It is important to use the newly-created table for the deletion. Otherwise, another thread/process could change the data between the time you capture the ids and the time you delete them.
这篇关于在mysql中,删除后可以返回删除的行吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文