物理对逻辑/软删除数据库记录？ [英] Physical vs. logical / soft delete of database record?

问题描述

进行记录的逻辑/软删除（即设置标志，说明记录已删除），而不是实际或物理删除记录的优点是什么？

What is the advantage of doing a logical/soft delete of a record (i.e. setting a flag stating that the record is deleted) as opposed to actually or physically deleting the record?

这是常见的做法吗？

这是安全的吗？

推荐答案

优点是保留历史记录（有利于审计），您不必担心通过引用要删除的行的数据库中的其他各个表来级联删除。缺点是，你必须编写任何报告/显示方法来考虑标志。

Advantages are that you keep the history (good for auditing) and you don't have to worry about cascading a delete through various other tables in the database that reference the row you are deleting. Disadvantage is that you have to code any reporting/display methods to take the flag into account.

至于这是一个常见的做法 - 我会说是，但

As far as if it is a common practice - I would say yes, but as with anything whether you use it depends on your business needs.

编辑：思考另一个不利条件 - 如果表上有唯一索引，删除的记录仍然会占用一个记录，所以你必须编写这种可能性（例如，一个用户表，在用户名上有唯一的索引;被删除的记录仍然会阻止删除的用户的用户名新记录。在GUID到删除的用户名列，但它是一个非常简单的解决方法，我不会推荐。可能在这种情况下，最好只有一个规则，一旦用户名被使用，它永远不会被替换。）

Thought of another disadvantange - If you have unique indexes on the table, deleted records will still take up the "one" record, so you have to code around that possibility too (for example, a User table that has a unique index on username; A deleted record would still block the deleted users username for new records. Working around this you could tack on a GUID to the deleted username column, but it's a very hacky workaround that I wouldn't recommend. Probably in that circumstance it would be better to just have a rule that once a username is used, it can never be replaced.)

这篇关于物理对逻辑/软删除数据库记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！