软删除最佳实践（PHP/MySQL）
问题
在处理产品和订单的 Web 应用程序中，我想维护前员工（用户）与他们处理的订单之间的信息和关系。我想维护过时产品和包含这些产品的订单之间的信息和关系。
不过，我希望员工能够整理管理界面，例如删除前员工、过时的产品、过时的产品组等。
我正在考虑实施软删除。那么，人们通常如何做到这一点呢？
我的直接想法
我的第一个想法是在每个应该软删除的对象表中添加“flag_softdeleted TINYINT NOT NULL DEFAULT 0”列。或者也许使用时间戳代替？
然后，我在每个相关 GUI 中提供“显示已删除”或“取消删除”按钮。单击此按钮，您将在结果中包含软删除的记录。每条已删除的记录都有一个“恢复”按钮。这有道理吗？
你的想法？
另外，如果有相关资源的链接，我将不胜感激。
Problem
In a web application dealing with products and orders, I want to maintain information and relationships between former employees (users) and the orders they handled. I want to maintain information and relationships between obsolete products and orders which include these products.
However I want employees to be able to de-clutter the administration interfaces, such as removing former employees, obsolete products, obsolete product groups etc.
I'm thinking of implementing soft-deletion. So, how does one usually do this?
My immediate thoughts
My first thought is to stick a "flag_softdeleted TINYINT NOT NULL DEFAULT 0" column in every table of objects that should be soft deletable. Or maybe use a timestamp instead?
Then, I provide a "Show deleted" or "Undelete" button in each relevant GUI. Clicking this button you will include soft-deleted records in the result. Each deleted record has a "Restore" button. Does this make sense?
Your thoughts?
Also, I'd appreciate any links to relevant resources.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我就是这么做的。我有一个
is_deleted字段，默认为 0。然后查询只需检查WHERE is_deleted = 0。我尝试尽可能远离任何硬删除。有时它们是必要的，但我将其设为仅限管理员使用的功能。这样我们就可以硬删除，但用户不能...
编辑：事实上，您可以使用它在您的应用程序中拥有多个软删除“层”。所以每个都可以是一个代码：
0->未删除1->软删除，显示在管理用户的已删除项目列表中2->软删除，除管理员用户外不会显示任何用户3->仅向开发人员显示。如果删除的列表太长，拥有其他 2 个级别仍然允许经理和管理员清理已删除的列表。而且由于前端代码只是检查
is_deleted = 0，所以它对前端是透明的......That's how I do it. I have a
is_deletedfield which defaults to 0. Then queries just checkWHERE is_deleted = 0.I try to stay away from any hard-deletes as much as possible. They are necessary sometimes, but I make that an admin-only feature. That way we can hard-delete, but users can't...
Edit: In fact, you could use this to have multiple "layers" of soft-deletion in your app. So each could be a code:
0-> Not Deleted1-> Soft Deleted, shows up in lists of deleted items for management users2-> Soft Deleted, does not show up for any user except admin users3-> Only shows up for developers.Having the other 2 levels will still allow managers and admins to clean up the deleted lists if they get too long. And since the front-end code just checks for
is_deleted = 0, it's transparent to the frontend...使用软删除是一种常见的实现方法，它们对于很多事情都非常有用，例如：
我想指出一件事，几乎每个人都错过了，而且它总是会回来咬你的后片。您的应用程序的用户对删除的理解与您不同。
存在不同程度的删除。典型的用户会在犯了错误
问题是，如果您不记录意图在删除过程中，您的应用程序无法区分错误数据（不应创建）和历史上正确的数据。
看一下以下数据：
某些用户不想显示商品 B 的价格，因为他们不再销售该商品。所以他删除了它。另一位用户错误地为商品 A 创建了价格，因此他将其删除并按预期创建了商品 C 的价格。现在，您能给我一份所有产品的价格清单吗？不可以，因为您要么必须显示潜在错误的数据 (A)，要么必须排除除当前价格之外的所有数据 (C)。
当然，可以通过多种方式处理上述问题。我的观点是，您需要非常清楚您所说的删除是什么意思，并确保用户不会误解它。一种方法是强制用户做出选择（隐藏/删除）。
Using soft-deletes is a common thing to implement, and they are dead useful for lots of things, like:
There is one thing I want to point out that almost everyone miss, and it always comes back to bite you in the rear piece. The users of your application does not have the same understanding of a delete as you have.
There are different degrees of deletions. The typical user deletes stuff when (s)he
The problem is that if you don't record the intention of the delete, your application cannot distinguish between erronous data (that should never have been created) and historically correct data.
Have a look at the following data:
Some user doesn't want to show the price of item B, since they don't sell that item anymore. So he deletes it. Another user created a price for item A by misstake, so he deleted it and created the price for item C, as intended. Now, can you show me a list of the prices for all products? No, because either you have to display potentially erronous data (A), or you have to exclude all but current prices (C).
Of course the above can be dealt with in any number of ways. My point is that YOU need to be very clear with what YOU mean by a delete, and make sure that there is no way for the users to missunderstand it. One way would be to force the user to make a choice (hide/delete).
如果我有命中该表的现有代码，我将添加该列并更改表的名称。然后，我将创建一个与当前表同名的视图，该视图仅选择活动记录。这样现有的代码就不会被破坏，并且您可以拥有软删除列。如果您想查看已删除的记录，请从基表中进行选择，否则请使用视图。
If I had existing code that hits that table, I would add the column and change the name of the table. Then I would create a view with the same name as the current table which selects only the active records. That way none of the existing code woudl break and you could have the soft delete column. If you want to see the deleted record, you select from the base table, otherwise you use the view.
正如您提到的，我一直只使用已删除列。确实没有比这更多的事情了。只需将 deleted 字段设置为 true，而不是删除记录。
我构建的一些组件允许用户查看所有已删除的记录并恢复它们，其他组件仅显示 已删除 = 0 的所有记录
I've always just used a deleted column as you mentioned. There's really not much more to it than that. Instead of deleting the record, just set the deleted field to true.
Some components I build allow the user to view all deleted records and restore them, others just display all records where deleted = 0
您的想法确实有意义并且在生产中经常使用，但是要实现它，您将需要更新相当多的代码以适应新领域。另一种选择是将“软删除”记录存档（移动）到单独的表或数据库中。这也经常发生，并且使问题成为维护问题而不是（重新）编程问题。 （您可以让表触发器对删除做出反应，以归档已删除的记录。）
我会进行归档以避免对生产代码进行重大更新。但是，如果您想使用已删除标志字段，请将其用作时间戳，以便为您提供布尔值之外的其他有用信息。 （Null = 未删除。）您可能还需要添加DeletedBy 字段来跟踪负责删除记录的用户。使用两个字段可以为您提供大量信息，告诉您谁删除了什么以及何时删除。 （这两个额外字段的解决方案也可以在归档表/数据库中完成。）
Your idea does make sense and is used frequently in production but, to implement it you will need to update quite a bit of code to account for the new field. Another option could be to archive (move) the "soft-deleted" records to a separate table or database. This is done frequently as well and makes the issue one of maintenance rather than (re)programming. (You could have a table trigger react to the delete to archive the deleted record.)
I would do the archiving to avoid a major update to production code. But if you want to use deleted-flag field, use it as a timestamp to give you additional useful info beyond a boolean. (Null = not deleted.) You might also want to add a DeletedBy field to track the user responsible for deleting the record. Using two fields gives you a lot of info tells you who deleted what and when. (The two extra field solution is also something that can be done in an archive table/database.)
我遇到的最常见的情况就是您所描述的，一个
tinyint甚至bit表示IsActive或IsDeleted 状态。根据这是否被视为“业务”或“持久性”数据，它可以尽可能透明地烘焙到应用程序/域逻辑中，例如直接在存储过程中并且不为应用程序代码所知。但这听起来像是满足您需求的合法业务信息，因此需要在整个代码中了解这些信息。 （因此用户可以按照您的建议查看已删除的记录。）我见过的另一种方法是使用两个时间戳的组合来显示给定记录的活动“窗口”。维护它需要更多的代码，但好处是可以安排某些内容在预定时间软删除自身。例如，限时产品可以在创建时这样设置。 （要使记录无限期地处于活动状态，可以使用最大值（或只是一些荒谬的遥远的未来日期），或者如果您同意的话，则将结束日期设置为
null。）当然还有进一步考虑不时删除/取消删除的内容并对此进行某种审计。标志方法只知道当前状态，时间戳方法只知道最近的窗口。但像审计追踪这样复杂的事情绝对应该与相关记录分开存储。
The most common scenario I've come across is what you describe, a
tinyintor evenbitrepresenting a status ofIsActiveorIsDeleted. Depending on whether this is considered "business" or "persistence" data it may be baked into the application/domain logic as transparently as possible, such as directly in stored procedures and not known to the application code. But it sounds like this is legitimate business information for your needs so would need to be known throughout the code. (So users can view deleted records, as you suggest.)Another approach I've seen is to use a combination of two timestamps to show a "window" of activity for a given record. It's a little more code to maintain it, but the benefit is that something can be scheduled to soft-delete itself at a pre-determined time. Limited-time products can be set that way when they're created, for example. (To make a record active indefinitely one could use a max value (or just some absurdly distant future date) or just have the end date be
nullif you're ok with that.)Then of course there's further consideration of things being deleted/undeleted from time to time and tracking some kind of audit for that. The flag approach knows only the current status, the timestamp approach knows only the most recent window. But anything as complex as an audit trail should definitely be stored separately than the records in question.
相反，我会使用 bin 表来移动从其他表中删除的所有记录。删除标志的主要问题是，对于链接表，在尝试插入新记录时肯定会遇到双键错误。
bin 表可以具有如下结构：
其中
此方法不仅可以使您免于在每个查询时检查删除标志（想象一下具有许多连接的查询），而且可以让您只拥有真正必要的表中的数据，方便使用 SQL 客户端程序进行搜索和更正
Instead I would use a bin table in which to move all the records deleted from the other tables. The main problem with the delete flag is that with linked tables you will definitely run into a double key error when trying to insert a new record.
The bin table could have a structure like this:
Where
this method will not only save you from checking the delete flag at each query (immagine the ones with many joins), but will allow you to have only the really necessary data in the tables, facilitating any searches and corrections using SQL client programs