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

Background:

$ 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);

Then the table you just created has the ids you want.

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.