软删除是个好主意吗？
软删除是好主意还是坏主意？
您无需实际删除数据库中的记录，只需将其标记为 IsDeleted = true，并且在恢复记录后，您只需将其标记为 False。
这是个好主意吗？
物理删除记录，然后将其移动到存档数据库，如果用户想要回记录，那么软件将在存档中查找该记录并重新创建它，这是一个更好的主意吗？
Are soft deletes a good idea or a bad idea?
Instead of actually deleting a record in your database, you would just flag it as IsDeleted = true, and upon recovery of the record you could just flag it as False.
Is this a good idea?
Is it a better idea to physically delete the record, then move it to an archive database, and if the user wants the record back, then software will look for the record in the archive and recreate it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（15）
在oracle中，如果将主键添加到自己制作的recycle_bin表中，然后添加行级安全策略，则可以在该行位于回收站时抑制所有查询中的值，从回收站中删除pk将自动恢复所有数据。无需更改其他查询来适应逻辑。
in oracle, if you add the primary key to a recycle_bin table you make up, then add a row level security policy, you can suppress the values from all queries when the row is in the recycle bin, removing the pk from the recycle bin will automatically restore all data. no need to change your other queries to accomodate the logic.
不过，它是有代价的，因为您需要更新查询和索引才能排除已删除的行。
也许不要切换标志，而是将其移动到另一个“垃圾箱”表。
另外，人们可以说这只是部分解决方案，因为它只涵盖删除，但是当您更新一行时，您仍然会覆盖旧值。
一般来说，我想说除非确实必要，否则永远不要删除任何内容。如今磁盘空间很便宜。当然，存在限制，有些数据您在法律上有义务删除，有些数据实际上并不那么重要，也许您不需要将旧数据保持在线并保存在同一个表中（某处的存档）也会起作用）。
It comes with a cost, though, because you need to update your queries and indexes to be able to exclude the deleted rows.
Maybe instead of toggling a flag, move it to another "trash can" table.
Also, one could say that is only a partial solution, because it covers only deletes, but when you update a row, you are still overwriting the old value.
In general, I'd say never delete anything unless you really have to. Disk space is cheap these days. Of course, there are limits, there is data that you are legally bound to erase, there is data that is really not all that important, and maybe you do not need to keep the old data online and in the same table (an archive somewhere would also work).
只是添加一分钱。我总是软删除；虽然它确实会降低性能，但非常轻微。考虑一下成本，当您的客户抱怨您的软件在她执行了某些连她自己都不记得的操作后停止运行时。好吧，这可能是一个很胖的例子，但你永远不会知道出了什么问题，谁做了什么，之前发生了什么以及之后插入了什么。在这种情况下，这会派上用场。此功能对于审计目的非常方便，并且许多客户要求提供此类审计报告。
此外，在大多数基于工作流的应用程序中，客户对在工作项上执行的“操作”感兴趣是作为软件功能/要求出现的；分配了什么值以及谁处理它等等。
Just to add a cent. I always soft-delete; though it does cost the performance, but very slightly. Think about the cost, when your customer complains regarding your software that stopped functioning after she performed certain actions that even she can't remember. Well, this may be a fat example, but you would never know what went wrong, who did what, what was before and what was inserted afterwards. In that case this would come handy. This functionality comes handy for auditing purpose, and many a customer requests for auditing reports of this sort.
Also, in most workflow based applications, it comes as a software feature/requirement that customer is interested in the "actions" performed on a work item; what values were assigned and who processed it, etc.
我是软删除的粉丝。主要是为了防止级联删除。但是，它需要额外的代码，因此如果您选择子对象，它会连接到父对象（以及所有父对象！）以确保它们都不会被删除。或者，您可以级联软删除，但如果您想稍后恢复它们，您可能不知道哪些子项已被删除以及哪些子项因级联而被删除。
此外，我在每个对象上保留修订日期时间和修订用户名，以便我知道谁最后修改（或软删除）它。然后，为了进行审计跟踪，我创建一个 *History（如 CustomerHistory）表，该表在每次更新原始表后插入。这样，在修改或软删除对象后，我就可以记录谁执行了该操作以及该对象的最后已知状态。
I am a fan of soft-deletes. Primarily to prevent cascading deletes. However, it takes additional code so that if you are SELECTing a child object, it joins to the parent (and all parent!) objects to make sure none of them are deleted. Alternatively you can cascade the soft-delete, but if you want to restore them later you may not know which children had already been deleted and which were deleted due to the cascade.
Additionally, I keep a revision date time and revision username on each object, so that I know who modified (or soft-deleted) it last. Then for an audit trail, I create a *History (like CustomerHistory) table that is inserted after every UPDATE to the original table. This way after an object is modified or soft-deleted, I have a record of who performed the action as well as the last known state of the object.
我在以下广泛场景中遇到了软删除：
案例 1：删除用户/代码可见的记录，但在数据库级别保留该记录，因为业务有兴趣知道它拥有该记录。
这些要求主要是由业务和需求驱动的。通常，核心可能是法律要求（例如@joshperry和@armandino场景），以在数据库中保留先前的记录并保存之前的记录。为每一次更改创建一个新记录。此时，我将查看案例 2 和案例 2。在设置 IsDeleted 标志之前评估它是否满足要求
案例 2：用于跟踪记录演变的审计跟踪 - 网上有大量不错的文章用于在数据库
HTH 中保留记录的审计跟踪。
I encountered soft-deletes for the following broad scenarios:
CASE 1: remove the record from being user/code visible, but have the record at the DB level since the business is interested in knowing it had that records.
These requirements are mostly driven by the business & usually at the core is perhaps a legal requirement (like @joshperry & @armandino scenarios) to have the previous record in the database & create a new record for every change made. At this point, I would look at CASE 2 & evaluate if it satifys the requirements before having an IsDeleted flag
CASE 2: audit trails to keep track of the evolution of a record - there are tons of decent articles online for keeping audit trails of records in a database
HTH.
我认为一般来说这是一个坏主意（也许有一些例外）。
首先，您的数据库应该定期备份，因此您永远不应该处于因 DELETE 而永久丢失数据的情况（当然，除非是删除刚刚添加的数据）。
其次，像这样的软删除意味着您现在必须在此表的每个查询中包含
WHERE IsDeleted = false子句（如果您联接这些表，情况会更糟）。一旦用户或测试人员注意到已删除的记录再次出现，就会发现这里的错误，这可能需要一些时间。此外，开发人员很容易在 COUNT(*) 查询中省略 WHERE 子句，这可能需要更长的时间才能发现（我在一个项目上工作，这种情况已经发生多年；没有多少记录被“删除”） ，因此总数接近预期，但没有人注意到）。最后，软删除适用于具有人工键的表，但可能不适用于具有自然主键的表（例如，您从以社会安全号码为键的表中“删除”某人 - 当您需要将他添加回来吗？请不要说“在复合主键中包含 IsDeleted”。）。
在设计审查中，我希望开发人员能够表现出对成本和收益的认识，并提出以这种方式进行软删除的极好的理由。 “为什么不这样做？”这不是一个很好的理由。
I say it's a bad idea, generally (with some exceptions, perhaps).
First, your database should be backed up regularly, so you should never be in a situation where you would lose data permanently because of a DELETE (unless it's a deletion of just-added data, of course).
Second, a soft delete like this means you now have to include a
WHERE IsDeleted = falseclause in every query on this table (and so much worse if you're JOINing these tables). A mistake here would be caught as soon as a user or tester noticed a deleted record showing up again, which might take some time. Also, it would be easy for a developer to omit the WHERE clause from COUNT(*) queries, which might take even longer to discover (I worked on one project where this had been happening for years; not many records were ever "deleted", so the totals were close to what was expected and no one noticed).Finally, a soft delete will work on a table with artificial keys, but potentially won't work on a table with a natural primary key (e.g. you "delete" someone from a table keyed by Social Security Number - what do you do when you need to add him back? Please don't say "include IsDeleted in a compound primary key".).
In a design review, I would expect the developer to demonstrate an awareness of the costs and benefits and to present an excellent reason for doing soft deletes in this manner. "Why not do it?" is not an excellent reason.
避免潜在的数据丢失永远不是一个坏主意。
我总是软删除。在数据库需要清除一条或多条记录的情况下，我通常采用软删除然后清空记录“回收站”的两步过程，或者采用文档管理式方法，其中文档记录可以老化，然后在硬删除之前经过批准流程。
It's never a bad idea to avoid potential data loss.
I always soft-delete. In cases where the database needs to be scrubbed of one or more records, I generally employ either a two-step process of soft deletion and then emptying a "recycle bin" of records, or a document-management-style approach where document records can be aged away, and then go through an approval process prior to hard deletion.
这取决于具体情况。我可以看到法律要求您真正删除某些内容的情况。也许有人要求将他们的社会安全号码从您的系统中永久删除。或者您可能有一条重复记录，想要将其合并为一条记录。将重复项与已删除的标志一起保留可能没有什么好处。
还有一个技术缺点：您无法执行级联删除，级联删除会自动清除对已删除数据的任何引用以防止外键违规。这不一定是一个大问题，但需要记住。
否则，我认为这是个好主意。
It depends on the circumstances. I could see situations where you are legally required to truly delete something. Maybe someone has requested that their social security number be permanently removed from your system. Or maybe you have a duplicate record that you want to consolidate into a single record. Keeping the duplicate hanging around with a deleted flag might not be advantageous.
There is also one technical disadvantage: You can't do cascading deletions, which automatically clear out any references to the deleted data to prevent foreign key violations. This isn't necessarily a big issue, but it's something to keep in mind.
Otherwise, I think it's a good idea.
如果您要使用软删除，最好使用deleted_date 字段，而不是is_deleted 字段。您将获得一段不错的额外数据，而不仅仅是位字段。
If you're going to use soft deletion, it's a good idea to have a deleted_date field, instead of an is_deleted field. You get a nice piece of extra data instead of just the bit field.
软删除的主要问题之一是那些不需要的数据可能会影响数据库性能。几年前，我的一位客户要求我对所有数据库项目进行软删除，我的解决方案是将所有“已删除”项目移动到备份表中，而不是将其保留到当前运行的表中。
One of the major problem for soft delete is those unwanted data will potentially affects the db performance. Several years ago one of my Client requested me to do soft delete on all database items, my solution to that is to move all "deleted" items to a backup table, instead of leaving it to the current running tables.
当无效删除绝对是灾难性的并且恢复应该很简单时，这是一个好主意。如果您想跟踪曾经存在过的所有内容，并且“删除”实际上只意味着“隐藏”，那么这也是一个好主意。意思是，要看情况。
It's a good idea when and if an invalid delete is absolutely catastrophic and recovery should be simple. It's also a good idea if you want to keep track of everything that has ever been and "delete" really only means "hide." Meaning, it's up to the situation.
我不会试图“政治正确”。如果您提倡软删除，那么您需要进行大脑检查。
1）
首先，通过不删除表中的行到底能实现什么目的？事实上，将来的某个时候您可以访问这些行，对吗？那么为什么不直接创建一个存档表并将行移到那里呢？这有什么问题吗？
2）
使用软删除，您将在 is_active 上创建不必要的查询或在某些时间戳列上创建不必要的查询。当您编写更简单的查询时，这只是浪费。是的，它可以与视图一起使用，但是视图不是额外的附属物吗？每个视图都是一个额外的 SQL，额外的性能成本，在任何商业 RDBMS 中，一切都只是一个表。除了您不知道如何在表之上编写查询这一事实之外，视图没有什么神奇之处。
3) 是的，它可以与 View 或 MV 一起使用。但后来我看到生产中执行 FTS 的查询，一切仍然有效！现代硬件和可靠软件的奇迹。但这也并不意味着它就是正确的。因此，按照同样的逻辑，仅仅因为它有效并不意味着它是正确
4）软删除的复杂性永远不会停留在简单的选择上。
A) 假设您有一个 UNIQUE 约束。现在您软删除了一行，但具有 UNIQUE 约束的列仍然存在。当您想要重新添加相同的数据时，如果没有额外的“技巧”，您就无法做到这一点。
B) 您可能具有从表 A 到表 B 的关联，并且当您从表 A 中软删除某些内容时，您需要确保表 B 上的独立查询能够处理该事实。假设一个典型的详细信息页面正在处理某些detail_id。
现在，master_id 已被软删除，但您仍然到处都有该 master_id 的详细信息 id 的永久链接。当您对 master_id 进行硬删除时，这些详细信息根本不存在。现在，通过软删除，它们仍然存在，并且必须意识到它们的 master_id 处于软删除模式。
它不会停止在简单的 Table_A.is_active = 0 或 1 阶段。
5) 进行硬删除既简单又正确。
A）没有人需要在任何地方添加任何额外的东西或担心任何事情。
只需归档数据+相关部分，您应该会很好。
I will not try to be "politically correct about it". If you are advocating soft-delete then you need to go for a brain checkup.
1)
First, what exactly are you achieving by not deleting the rows in table? Just the fact that sometime in future you can access those rows, right? So why not just create an archive Table and move the rows there? what is wrong with that?
2)
With soft-delete you are creating unnecessary query on is_active or query on some time-stamp column. That is just waste when you would be writing simpler queries. Yes, it will work with a view but are views not an extra appendage? Every view is an extra SQL, extra performance cost, down under in any commercial RDBMS everything is a table only. There is nothing magical about views apart from the fact that you do not know how to write queries on top of tables.
3) Yes, it will work with a View or MV. But then I have seen queries in production doing FTS and everything still works! The wonders of modern hardware and solid software. But then that does not make it right either. So by same logic, just because it works does not mean it is RIGHT
4) The complexities of soft delete never ever stops at a simple select.
A) Suppose you had a UNIQUE constraint. Now you soft-delete a row but the column with UNIQUE constraint is still there. When you want to add the same data back in, you cannot do that without additional "tricks".
B) You may have associations going from Table A to Table B and when you soft delete something from Table A, you need to ensure that independent queries on Table B take care of that fact. Suppose a typical detail page was working on some detail_id.
Now a master_id is soft deleted but you still have permalinks with detail_id of that master_id everywhere. When you do hard delete on master_id, those details simply do not exist. Now with soft delete they still exists and they have to be aware of the fact that their master_id is in soft-delete mode.
it will not stop at a simple Table_A.is_active = 0 or 1 stage.
5) Doing hard deletes is simple and right.
A) No one has to add anything extra or worried about anything anywhere.
Just archive the data + related pieces and you should be good.
软删除还允许您撤销
DELETE< /code> 来自应用程序使用的数据库帐户的权限。Soft deletes would also allow you to revoke
DELETEprivileges from the database account used by the application.有时软删除是必要的。例如，假设您有一个引用“产品”表的“发票”表。一旦您创建了包含特定产品的发票，您就永远无法删除该产品（如果您的 RI 设置正确，则不会删除该产品）。
此特定场景假设您永远不想删除发票，在真实的公司中您可能不想删除历史财务数据。
尽管还有许多其他情况，您将无法删除某些数据，这是由于业务或其他原因无法删除链上依赖项的副作用。
Some times soft deletes are necessary. For example, say you have an Invoice table that references a Products table. Once you have created an Invoice with a specific Product you could then never delete that Product (and if your RI is set up correctly it won't let you).
This specific scenario assumes that you'll never want to delete the Invoices, which in a real company you probably wouldn't want to delete historical financial data.
Though there are many other cases where you would not be able to delete some data as a side effect of a dependency up the chain not being deletable for reasons business or other.
这取决于数据。由于法律/审计要求，某些数据无法删除。
另一方面，社交网站应该提供删除帐户及其所有关联数据（包括联系信息、照片、消息等）的选项。如果不这样做，那就太麻烦了，例如 Facebook。
It depends on the data. Some data cannot be deleted due to legal/audit requirements.
Social networking sites on the other hand should provide an option to delete an account with all associated data, including contact info, photos, messages, etc. It's a real annoyance if they don't, e.g. Facebook.