database 数据库：删除或不删除记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/502501/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Database: To delete or not to delete records
提问by jerbersoft
I don't think I am the only person wondering about this. What do you usually practice about database behavior? Do you prefer to delete a record from the database physically? Or is it better to just flag the record with a "deleted" flag or a boolean column to denote the record is active or inactive?
我不认为我是唯一对此感到疑惑的人。您通常对数据库行为进行哪些练习？您是否更喜欢从数据库中物理删除记录？还是只用“已删除”标志或布尔列来标记记录来表示记录是活动的还是非活动的更好？
采纳答案by falstro
It definitely depends on the actual content of your database. If you're using it to store session information, then by all means wipe it immediately when the session expires (or is closed), you don't want that garbage lying around. As it cannot really be used again for any practical purposes.
这绝对取决于数据库的实际内容。如果您使用它来存储会话信息，那么当会话过期（或关闭）时，一定要立即擦除它，您不希望这些垃圾散落。因为它不能真正再次用于任何实际目的。
Basically, what you need to ask yourself, might I need to restore this information? Like deleted questions on SO, they should definitely just be marked 'deleted', as we're actively allowing an undelete. We also have the option to display it to select users as well, without much extra work.
基本上，您需要问自己什么，我是否需要恢复这些信息？就像关于 SO 的已删除问题一样，它们绝对应该被标记为“已删除”，因为我们正在积极允许取消删除。我们还可以选择显示它以选择用户，而无需太多额外工作。
If you're not actively seeking to fully restore the data, but you'd still like to keep it around for monitoring (or similar) purposes. I would suggest that you figure out (to the extent possible of course) an aggregation scheme, and shove that off to another table. This will keep your primary table clean of 'deleted' data, as well as keep your secondary table optimized for monitoring purposes (or whatever you had in mind).
如果您不积极寻求完全恢复数据，但您仍希望保留它以用于监视（或类似）目的。我建议您找出（当然在可能的范围内）一个聚合方案，并将其推到另一个表中。这将使您的主表保持“已删除”数据的清洁，并保持您的辅助表针对监控目的（或您想到的任何内容）进行优化。
For temporal data, see: http://talentedmonkeys.wordpress.com/2010/05/15/temporal-data-in-a-relational-database/
有关时间数据，请参阅：http: //talentedmonkeys.wordpress.com/2010/05/15/temporal-data-in-a-relational-database/
回答by WW.
Pros of using a delete flag:
使用删除标志的优点：
- You can get the data back later if you need it,
- Delete operation (updating the flag) is probably quicker than really deleting it
- 如果需要，您可以稍后取回数据，
- 删除操作（更新标志）可能比真正删除它更快
Cons of using a delete flag:
使用删除标志的缺点：
- It is very easy to miss
AND DeletedFlag = 'N'somewhere in your SQL - Slower for the database to find the rows that you are interested in amongst all the crap
- Eventually, you'll probably want to really delete it anyway (assuming your system is successful. What about when that record is 10 years old and it was "deleted" 4 minutes after originally created)
- It can make it impossible to use a natural key. You may have one or more deleted rows with the natural key and a real row wanting to use that same natural key.
- There may be legal/compliance reasons why you are meant to actually delete data.
AND DeletedFlag = 'N'在 SQL 中的某个地方很容易遗漏- 数据库在所有废话中找到您感兴趣的行的速度较慢
- 最终，您可能无论如何都想真正删除它（假设您的系统是成功的。如果该记录有 10 年历史并且在最初创建后 4 分钟被“删除”呢？）
- 这可能会导致无法使用自然键。您可能有一个或多个带有自然键的已删除行和一个想要使用相同自然键的真实行。
- 可能有法律/合规性原因导致您要实际删除数据。
回答by Adeel Ansari
As a complement to all posts...
作为对所有帖子的补充......
However, if you plan to mark the record, its good to consider making a view, for active records. This would save you from writing or forgetting the flag in your SQL query. You might consider a view for non-active records too, if you think that also serve a purpose.
但是，如果您打算标记记录，最好考虑为活动记录创建视图。这将使您免于在 SQL 查询中写入或忘记标志。您也可以考虑非活动记录的视图，如果您认为这也有作用的话。
回答by user67403
I am glad to have found this thread. I too was wondering what people thought about this issue. I have implemented the 'marked as deleted' for about 15 years on many systems. Whenever a user would call to say something was accidentally deleted it was certainly a lot easier to mark it un-deleted than recreate it or restore from a backup.
我很高兴找到了这个线程。我也想知道人们对这个问题的看法。我已经在许多系统上实施了“标记为已删除”大约 15 年。每当用户打电话说某些内容被意外删除时，将其标记为未删除肯定比重新创建它或从备份中恢复要容易得多。
We are using postgresql and Ruby on rails it looks like we could do this in 1 of two ways, modify rails or add an ondelete trigger and does instead a pl/pgsql function to mark as deleted. I am leaning toward the latter.
我们正在使用 postgresql 和 Ruby on rails 看起来我们可以通过两种方式之一来做到这一点，修改 rails 或添加一个 ondelete 触发器，而不是使用 pl/pgsql 函数来标记为已删除。我倾向于后者。
As for performance hits, it will be interesting to see the results of EXPLAIN-ANALYZE on large tables to few deleted items as well as many deleted items.
至于性能命中，在大表上看到 EXPLAIN-ANALYZE 的结果会很有趣，包括很少的已删除项目以及许多已删除的项目。
In systems used over time I have found, new users tend to do silly things like delete things accidentally. So when people are new in a position they have all the access rights of the person previously in that position except with zero experience. Accidentally deleting something and being able to quickly recover gets everyone back to work quickly.
在我发现随着时间的推移使用的系统中，新用户往往会做一些愚蠢的事情，比如意外删除东西。因此，当人们刚开始担任某个职位时，他们拥有之前担任该职位的人的所有访问权限，但经验为零。不小心删除了一些东西并能够快速恢复让每个人都能快速恢复工作。
But as someone said, sometimes you may need that particular key back for some reason, at that point you would need to really delete it, then re-create the records (on undelete it and modify the record).
但正如有人所说，有时您可能出于某种原因需要该特定密钥，此时您需要真正删除它，然后重新创建记录（取消删除并修改记录）。
回答by Jeremy French
There are also legal issues either way if personal data is involved. I think it greatly depends on where you are (or where the database is), and what the terms of use are.
如果涉及个人数据，无论哪种方式也存在法律问题。我认为这在很大程度上取决于你在哪里（或数据库在哪里），以及使用条款是什么。
In some cases people can ask to be removed from your system, in which case a hard delete is needed (or at least clearing out all of the personal information).
在某些情况下，人们可能会要求从您的系统中删除，在这种情况下，需要进行硬删除（或至少清除所有个人信息）。
I would check with your legal department before you adopt a strategy either way if personal information is involved.
如果涉及个人信息，我会在您采用任何一种策略之前咨询您的法律部门。
回答by Robert Gould
I mark them as deleted, and don't really delete. However every once in a while I sweep out all the junk and archive it, so it doesn't kill performance.
我将它们标记为已删除，并没有真正删除。然而，每隔一段时间我就会清除所有垃圾并将其存档，因此它不会影响性能。
回答by cruizer
If you are concerned about "dormant" records slowing down your database access, you may want to move those rows into another table acting as an "archive" table.
如果您担心“休眠”记录会减慢您的数据库访问速度，您可能希望将这些行移动到另一个充当“存档”表的表中。
回答by Abie
For user-entered/managed data I've used the flag method you describe and given the user an "empty the trash" interface to actually delete items if they choose to.
对于用户输入/管理的数据，我使用了您描述的标志方法，并为用户提供了一个“清空垃圾箱”界面，以便在他们选择时实际删除项目。
