在数据库表中标记已删除的记录
有时您想将数据库表记录标记为已删除，而不是永久删除它，对吗？
你怎么做到的？
到目前为止，我一直在使用布尔“已删除”字段，但我不确定这是否是一个好的方法。
Sometimes you want to mark a DB table record as deleted instead of deleting it permanently, right?
How do you do that?
So far I've been using a boolean "deleted" field but I'm not sure if it's a good apprach.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
就是这样 - 一个布尔字段，指示记录已被删除。有几次我使用它，我将该字段称为
IsDeleted。这通常称为逻辑删除。
您可以自行决定是否尊重报告中的该字段 - 这意味着排除所有带有
IsDeleted = true的记录。如果您有很多表和关系，这些查询可能会变得有点复杂。此外，如果表上有唯一约束，您可能会遇到一些问题。例如，如果在用户表中用户具有
IsDeleted = true并且电子邮件列是唯一的，我将无法添加具有相同电子邮件地址的新用户。有一些 ORM 会考虑这些字段 - 例如，如果存在名为“Deleted”或“IsDeleted”的列，SubSonic 2.2 将不会删除记录，而是会将此字段设置为 true。
一些相关资源：
作为替代方案，您可以添加审核表。
That's about it - a boolean field to indicate that the record is deleted. The few times I used that, I called that field
IsDeleted.This is often referred to as Logical Delete.
It's up to you to respect that field in your reports - which means excluding all the records with
IsDeleted = true. Those queries can get a little complicated if you have a lot of tables and relations.Also, you may encounter some problems if you have unique constraints on a table. For example, if in a user table a user has
IsDeleted = trueand the email column is unique, i would not be possible to add a new user with same email address.There are some ORM which take those fields into consideration - for example, SubSonic 2.2 will not delete a record if there is a column named 'Deleted' or 'IsDeleted', instead it will set this field to true.
Some related resources:
As an alternative to this you could add auditing tables.
我通常使用IsDeleted。
如果有多个状态（例如正常、存档、删除），我可能会使用 Enum（或 Int，如果不可用）来表示状态。在这种情况下，通常我会将字段命名为“状态”或“状态”。
I usually use IsDeleted.
If there are multiple states (e.g. normal, archive, deleted) I might use an Enum (or Int if not available) to represent the state. Usually I'd name the field Status or State in this case.
我认为这是一个不错的解决方案。另一种方法是将数据移动到另一个表（某种历史表），因此在包含活动数据的表中搜索数据会更快。但这取决于你的情况。
I think it's an okay solution. Another approach is to move the data to another table, some kind of history table, so it will be faster to search for data in the table containing the active data. But it depends on your situation.
在银行业，存储所有修改（不仅仅是删除）被认为是一种良好的做法。通常它是在“日志表”中完成的，其 DDL 与原始 DDL 几乎相同，加上一些标志来指示操作类型、日期和时间。时间、用户等。但是（非常重要）日志表的定义没有唯一键！
In the banking industry, it is considered a good practice to store all the modifications (no just deletions). Usually it is done in "log tables" with the almost the same DDL than the original one plus a few flags to indicate operation type, date& time, user, etc. BUT (VERY important) the log tables are defined WITHOUT unique keys!
我会使用 datetime，null 表示存活，timstamp 表示“删除日期”。
它非常适合，
因为它默认为 null。
I would use datetime, null for alive, timstamp for "deleted on".
It' perfect for
because it defaults to null.