错误执行了MySQL更新查询后如何进行恢复？

Question

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

我忘记在WHERE子句中添加id字段。

导致所有行都被更新了。

如何恢复？

我没有备份....

- assaqqaf

---

教训：备份、备份、再备份。 - BalusC

谢谢大家，我将从零开始。 - assaqqaf

Answer 1

这里有两个教训：

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语句。例如：表、索引、存储过程、数据库和视图的创建或更改语句。

- OMG Ponies

Answer 2

抱歉，朋友们，但覆盖MySQL数据库的恢复机会通常接近于零。与删除文件不同，覆盖记录实际上并且在大多数情况下物理上覆盖了现有数据。

为了做好准备，如果出现任何情况，您应该停止MySQL服务器，并复制包含数据库的物理目录，以便进一步覆盖：将数据文件夹简单地复制粘贴到其他位置即可。

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

您可能希望为将来设置频繁的数据库备份。有许多解决方案；其中一个最简单、最可靠且最易于自动化（使用Linux中的at或cron，或Windows中的任务计划程序）是MySQL自己的mysqldump。

- Pekka

Answer 3

抱歉要说，如果没有备份，就无法恢复旧的字段值。

不要攻击传递信息的人...

- Dan Soap

Answer 4

你是否启用了二进制日志？你可以通过访问二进制日志来进行恢复。

- user3299633