错误的 MySQL 更新查询后恢复？

Question

我在表中进行了错误的更新查询。

我忘了在 WHERE 子句中创建一个 id 字段。

这样就更新了我的所有行。

如何恢复？

我没有备份….

原文由 assaqqaf 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

对不起，恢复被覆盖的 MySQL 数据库的机会通常接近于零。与删除文件不同，在大多数情况下，覆盖记录实际上会覆盖现有数据。

为了做好准备，如果出现任何问题，您应该停止 MySQL 服务器，并制作包含数据库的物理目录的副本，这样就不会进一步覆盖任何内容：只需将数据文件夹复制+粘贴到其他位置即可。

但是不要抱太大希望——我认为真的没有什么可以做的。

您可能希望为将来设置频繁的数据库备份。周围有很多解决方案； MySQL 自己的 mysqldump 是最简单、最可靠和最容易自动化的一种（在 Linux 中使用 at 或 cron 或 Windows 中的任务调度程序）。

原文由 Pekka 发布，翻译遵循 CC BY-SA 2.5 许可协议

Answer 2

这里有两个教训：

1. 备份数据
2. 在事务中执行 UPDATE/DELETE 语句，因此如果事情没有按计划进行，您可以使用 ROLLBACK

了解数据库的事务（自动提交、显式和隐式）处理可以使您不必从备份中恢复数据。

事务控制数据操作语句以确保它们是原子的。 “原子”意味着事务要么发生，要么不发生。向数据库发出事务完成信号的唯一方法是使用 COMMIT 或 ROLLBACK 语句（根据 ANSI-92，遗憾的是它不包括用于创建/开始交易，所以它是特定于供应商的）。 COMMIT 应用在事务中所做的更改（如果有）。 ROLLBACK 忽略事务中发生的任何操作 - 当 UPDATE/DELETE 语句执行意外操作时非常需要。

通常，单个 DML（插入、更新、删除）语句在自动提交事务中执行 - 一旦语句成功完成，它们就会被提交。这意味着在像您这样的情况下，没有机会将数据库回滚到语句运行之前的状态。当出现问题时，唯一可用的恢复选项是从备份中重建数据（假设存在）。在 MySQL 中， 默认情况下 InnoDB 的自动提交是 打开 的——MyISAM 不支持事务。可以使用以下方法禁用它：

 SET autocommit = 0

显式事务是指将语句包装在显式定义的事务代码块中 - 对于 MySQL，即 START TRANSACTION 。它还需要在事务结束时明确作出 COMMIT 或 ROLLBACK 语句。嵌套事务超出了本主题的范围。

隐式事务与显式事务略有不同。隐式事务不需要明确定义事务。但是，与显式事务一样，它们需要提供 COMMIT 或 ROLLBACK 语句。

结论

显式事务是最理想的解决方案 - 它们需要声明 COMMIT 或 ROLLBACK 来完成交易，并且如果有需要，会明确说明正在发生的事情供其他人阅读.如果以交互方式使用数据库，则隐式事务是可以的，但是只有在结果经过测试并彻底确定其有效后才能指定 COMMIT 语句。

这意味着您应该使用：

 SET autocommit = 0;

START TRANSACTION;
  UPDATE ...;

…并且仅在结果正确时使用 COMMIT; 。

也就是说，UPDATE 和 DELETE 语句通常只返回受影响的行数，而不是具体的细节。将此类语句转换为 SELECT 语句并在尝试 UPDATE/DELETE 语句 之前 检查结果以确保正确性。

附录

DDL（数据定义语言）语句是自动提交的——它们不需要 COMMIT 语句。 IE：表、索引、存储过程、数据库和视图创建或更改语句。

原文由 OMG Ponies 发布，翻译遵循 CC BY-SA 2.5 许可协议