每个人都意外地忘记了DELETE查询上的WHERE子句，并且一次或者两次清除了一些未备份的数据。我正在考虑这个问题，并且想知道我提出的解决方案是否可行。

如果应用程序和维护脚本代替了实际的DELETE查询，该怎么办？

UPDATE foo SET to_be_deleted=1 WHERE blah = 50;

然后设置了一个cron作业，以完成并实际上删除带有该标志的所有内容？不利之处在于，几乎所有其他查询都需要添加" WHERE to_be_deleted！= 1"，但是不利之处在于，我们永远不会再错误地丢失数据。我们可能会看到"受影响的2,349,325行"，然后说："嗯，好像我忘记了WHERE子句"，然后重置了这些标志。我们甚至可以将to_be_deleted字段设置为DATE列，因此cron作业将检查行的时间是否到了。

另外，我们可以从生产数据库用户中删除DELETE权限，因此，即使有人设法将某些SQL注入到站点中，他们也将无法删除任何内容。

因此，我的问题是：这是一个好主意，还是我没有看到的陷阱？

解决方案

我们可以在该表上设置一个视图，该视图选择WHERE to_be_deleted！= 1，并且所有常规选择都在该视图上完成，避免了将WHERE放在所有查询中。

陷阱是不必要的复杂，有人会无意间忘记在查询中检查该标志。还有一个问题是可能需要立即删除某些内容，而不是等待调度的作业运行。

为了避免to_be_deleted WHERE子句，我们可以在delete命令触发之前创建触发器，以将已删除的行插入到单独的表中。当我们确定确实需要删除该表中的所有内容时，可以清除该表，或者可以出于存档目的保留该表。

如果我们想这样做，那很好，但似乎需要大量工作。有多少人正在手动更改数据库？它应该很少，特别是如果用户有一个可以使用的应用程序。

当我在生产数据库上工作时，我将所有事情都放在事务中，所以如果我搞砸了，我可以回滚。像我这样的标准练习对我有所帮助。

我看不到有什么真正的错，尽管除了每个应用程序中的单点数据操作之外，还必须了解此功能，而不仅仅是其所需的数据。

我们还可以获得"软删除"功能，因此我们可以赋予(某些)最终用户"撤消"的能力，这样才能消除软删除的好处。

只要应用程序不需要立即删除数据，这就可以，因为我们必须等待下一个cron作业间隔。

我认为更好的解决方案和更常见的做法是使用开发服务器和生产服务器。如果开发数据库崩溃了，只需重新加载它即可。没有伤害。如果要在生产数据库上测试代码，那么我们应该承担的任何不良后果。

很多人都有删除标志或者行状态标志。但是，如果有人在后端进行更改(并且他们会这样做，因为通常人们需要完成批量更改而前端无法完成)，而他们犯错了，他们仍然会经常删除。最终，这不能替代在将脚本应用于生产环境之前对其进行测试。

另外...如果执行以下查询" UPDATE foo SET to_be_deleted = 1"会发生什么，因为它们忽略了where子句。除非审计列带有时间戳，否则我们如何知道哪些列被删除以及哪些列被错误地处理？但是，即使审计列带有时间戳，如果审计是通过存储过程或者程序员约定完成的，那么这些后端查询也可能不会提供信息，让我们知道它们刚刚被应用。

太复杂。标准的方法是在事务内完成所有工作，因此，如果我们搞砸了却忘记了WHERE子句，那么当看到"受影响的2,349,325行"结果时，只需回滚即可。

每个其他查询上的" WHERE to_be_deleted <> 1"是一个巨大的查询。另一个是，一旦我们运行了意外的恶意查询，我们将如何确定先前在2,349,325个中将哪个标记为已删除？

我认为实际的解决方案是定期备份，否则，可能是删除触发器，它捕获了要删除的元组。

为删除的行创建并行表可能会更容易。原始表上的DELETE触发器(如果我们还想撤消更改，也要``UPDATE`)可以将受影响的行复制到并行表中。在并行表中添加datetime列以记录更改的日期和时间，将使我们能够使用cron作业永久删除超过一定期限的行。

这样，我们将在原始表上使用普通的DELETE语句，因此我们将不会忘记运行特殊的"DELETE"语句。我们还回避了" to_be_deleted！= 1"表达式，这只是当有人不可避免地忘记时等待发生的错误。

另一个选择是在每个表上创建一个删除触发器。删除任何内容后，它将把"要删除"记录插入到另一个表中，该表最好命名为TABLENAME_deleted。

缺点是数据库将有两倍多的表。

我一般不建议使用触发器，但这可能正是我们要寻找的。

这就是为什么每当我们手动编辑数据时，都应该开始TRAN，编辑数据，检查数据看起来是否不错(例如，删除的数据没有超出预期)，然后结束END TRAN。如果我们使用的是Postgres，那么我们还想创建很多保存点，以免打错字消除中间工作。

但是，在许多应用程序中，使软件标记记录为无效而不是删除它们确实是有意义的。添加一个自动更新的last_modified日期，我们都准备好将增量更新设置到数据仓库中。即使我们现在没有数据仓库，当准备工作很便宜时，为将来做准备也不会受到伤害。另外，如果发生人为错误，我们仍然拥有数据，并且可以找到所有因错误而被"删除"的记录并进行修复。 (尽管如此，我们仍然应该使用事务。)

我们似乎在这里描述了三种情况。

• 情况1-维护脚本。通过在开发环境以外的环境中进行开发和测试，可以将风险降到最低。为了快速维护，请在单个事务中进行维护，并在提交前检查所有内容。如果输入有误，请发出回滚命令。对于我们不必等待或者进行单个事务的更严重的维护，请考虑在运行维护作业之前直接进行备份，以便在遇到脚本时始终可以恢复到运行脚本之前的状态。严重的问题。
• 情况2-SQL注入。这是一个体系结构问题。应用程序不应将SQL传递到数据库中，应通过程序包/存储过程/函数控制访问，并且应使用绑定变量而不是通过绑定变量来应用将要从UI中获得并在DDL语句中使用的值。通过将字符串添加在一起来创建动态SQL。
• 情况3-常规批处理作业。这些应该在部署到生产之前已经过测试。如果删除过多，则说明存在错误，将不得不依靠备份策略。

Everyone has accidentally forgotten
  the WHERE clause on a DELETE query and
  blasted some un-backed up data once or
  twice.

不。我始终将我的DELETE原型化为SELECT，并且仅当后者给出要删除的结果时，才将WHERE之前的语句更改为DELETE。这让我在执行任何操作之前仔细检查了想要影响的行。