SQL 软删除是个好主意吗？

Are soft deletes a good idea?

提问by 001

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.

您无需实际删除数据库中的记录，而只需将其标记为IsDeleted = true，并且在恢复记录时您只需将其标记为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?

物理删除记录，然后将其移动到存档数据库，如果用户想要恢复记录，那么软件将在存档中查找记录并重新创建它是否更好？

回答by MusiGenesis

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).

首先，您的数据库应该定期备份，因此您永远不应该因为 DELETE 而永久丢失数据（当然，除非是删除刚刚添加的数据）。

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).

其次，像这样的软删除意味着你现在必须WHERE IsDeleted = false在这个表的每个查询中包含一个子句（如果你加入这些表，情况会更糟）。一旦用户或测试人员注意到已删除的记录再次出现，就会发现这里的错误，这可能需要一些时间。此外，开发人员很容易从 COUNT(*) 查询中省略 WHERE 子句，这可能需要更长的时间才能发现（我在一个项目中工作了多年；没有多少记录被“删除” ，所以总数接近预期，没有人注意到）。

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".).

最后，软删除适用于带有人工键的表，但可能不适用于具有自然主键的表（例如，您从以社会安全号码为键的表中“删除”某人 - 当您需要把他加回来吗？请不要说“在复合主键中包含 IsDeleted”。）。

In a design review, I would expect the developer to demonstrate an awareness of the costs and benefits and to present an excellentreason for doing soft deletes in this manner. "Why notdo it?" is not an excellent reason.

在设计中，我希望开发人员表现出对成本和收益的认识，并提出以这种方式进行软删除的绝佳理由。“为什么不去做呢？” 不是一个很好的理由。

回答by Robert Harvey

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.

我总是软删除。在需要清理数据库中的一条或多条记录的情况下，我通常采用软删除然后清空记录“回收站”的两步过程，或者采用文档管理风格的方法，其中文档记录可以老化，然后在硬删除之前通过审批流程。

回答by devuxer

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.

否则，我认为这是一个好主意。

回答by Josh Smeaton

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.

如果您要使用软删除，最好使用deleted_date 字段而不是is_deleted 字段。你会得到一个很好的额外数据，而不仅仅是位域。

回答by xandy

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.

软删除的主要问题之一是那些不需要的数据可能会影响数据库性能。几年前，我的一位客户要求我对所有数据库项目进行软删除，我的解决方案是将所有“已删除”项目移动到备份表中，而不是将其保留到当前运行的表中。

回答by Anthony Pegram

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.

当无效删除绝对是灾难性的并且恢复应该很简单时，这是一个好主意。如果您想跟踪曾经存在的所有内容并且“删除”实际上仅意味着“隐藏”，这也是一个好主意。这意味着，这取决于情况。

回答by rjha94

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?

1）首先，您通过不删除表中的行来实现什么？只是将来某个时候您可以访问这些行，对吗？那么为什么不创建一个归档表并将行移动到那里呢？这有什么问题？

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.

2) 使用软删除，您正在对 is_active 或某个时间戳列的查询创建不必要的查询。当您编写更简单的查询时，这只是浪费。是的，它可以与视图一起使用，但视图不是额外的附属物吗？每个视图都是一个额外的 SQL，额外的性能成本，在任何商业 RDBMS 下，一切都只是一个表。除了您不知道如何在表上编写查询之外，视图并没有什么神奇之处。

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

3) 是的，它适用于视图或 MV。但是后来我看到生产中的查询做 FTS 并且一切仍然有效！现代硬件和可靠软件的奇迹。但这也不能使它正确。所以按照同样的逻辑，仅仅因为它有效并不意味着它是正确的

4) The complexities of soft delete never ever stops at a simple select.

4) 软删除的复杂性永远不会停留在简单的选择上。

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".

A) 假设您有一个 UNIQUE 约束。现在您软删除了一行，但具有 UNIQUE 约束的列仍然存在。当您想重新添加相同的数据时，如果没有额外的“技巧”，您将无法做到这一点。

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.

B) 您可能有从表 A 到表 B 的关联，当您从表 A 中软删除某些内容时，您需要确保对表 B 的独立查询处理该事实。假设一个典型的详细信息页面正在处理某个 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.

现在一个 master_id 被软删除了，但你仍然有该 master_id 的 detail_id 无处不在的永久链接。当您对 master_id 进行硬删除时，这些详细信息根本不存在。现在使用软删除它们仍然存在，并且它们必须意识到它们的 master_id 处于软删除模式的事实。

it will not stop at a simple Table_A.is_active = 0 or 1 stage.

它不会停留在简单的 Table_A.is_active = 0 或 1 阶段。

5) Doing hard deletes is simple and right.

5) 进行硬删除既简单又正确。

A) No one has to add anything extra or worried about anything anywhere.

A) 没有人必须在任何地方添加任何额外的东西或担心任何事情。

1. Your application logic is simpler
2. Your database is smaller
3. Your queries are faster

1. 您的应用程序逻辑更简单
2. 你的数据库更小
3. 您的查询速度更快

Just archive the data + related pieces and you should be good.

只需归档数据 + 相关部分，你应该会很好。

回答by Daniel Vassallo

Soft deletes would also allow you to revokeDELETEprivileges from the database account used by the application.

软删除还允许您撤销DELETE应用程序使用的数据库帐户的权限。

回答by joshperry

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).

有时软删除是必要的。例如，假设您有一个引用 Products 表的 Invoice 表。一旦您使用特定产品创建了发票，您就永远无法删除该产品（如果您的 RI 设置正确，它不会让您删除）。

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.

尽管在许多其他情况下，您将无法删除某些数据，这是由于业务或其他原因无法删除链上的依赖项的副作用。

回答by Randy

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.

在oracle中，如果将主键添加到自己组成的recycle_bin表中，然后添加行级安全策略，则可以在该行在回收站中时抑制所有查询的值，从回收站中删除pk将自动恢复所有数据。无需更改其他查询以适应逻辑。