如何跟踪MySQL上表中已删除的行？

I have foreign key constraints set on my MySQL tables that cascade on delete. I need to keep track of all the other tables and rows affected when I delete a user because there are physical files involved that must also be deleted. Is there a way to log this?

我在我的MySQL表上设置了外键约束,它们在删除时级联。我需要跟踪删除用户时受影响的所有其他表和行,因为涉及的物理文件也必须删除。有没有办法记录这个?

2 个解决方案

#1

Well, by adding a trigger on each table that you'ld like to track you could easily:

好吧,通过在每个表上添加一个触发器,您可以轻松地跟踪:

feed an audit table

提供审计表

run a background job to delete the corresponding files

运行后台作业以删除相应的文件

Like Mitch said, it also depends on you, what you're wanting to do exactly.

像米奇说的那样,这也取决于你,你想要做的事情。

For instance you could also want to keep the rows for history purpose. In this case you might implement your own rows "suppression" by using a "deleted" flag in the tables, so you would put it to 1 whenever you want to remove a row. [...]

例如,您可能还希望保留行以用于历史记录。在这种情况下,您可以通过在表中使用“已删除”标志来实现自己的行“抑制”,因此,只要您想要删除行,就可以将其设置为1。 [...]

#2

I think no. But you can do a little trick. For example, before you delete a record in table A, select all record in other tables which is related to the deleted record.

我想不是。但你可以做一点小动作。例如,在删除表A中的记录之前,请选择与已删除记录相关的其他表中的所有记录。

   $sql = "SELECT Id FROM b WHERE b.a_id ='$deleted_A_Id'";
   //delete physical file and any other things
   $sql = "DELETE FROM A where A.id = '$deleted_A_Id'";
   //just exec sql the normal way

#1