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

Recovery after wrong MySQL update query?

提问by assaqqaf

I made a wrong update query in my table.

我在我的表中做了一个错误的更新查询。

I forgot to make an id field in the WHEREclause.

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

So that updated all my rows.

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

How to recover that?

怎么恢复呢？

I didn't have a backup....

我没有备份....

采纳答案by Pekka

Sorry man, but the chances of restoring an overwritten MySQLdatabase are usually close to zero. Different from deleting a file, overwriting a record actually and physically overwrites the existing data in most cases.

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

To be prepared if anything comes up here, you should stop your MySQL server, and make a copy of the physical directory containing the database so nothing can get overwritten further: A simple copy+paste of the data folder to a different location should do.

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

But don't get your hopes up - I think there's nothing that can be done really.

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

You may want to set up a frequent database backup for the future. There are many solutions around; one of the simplest, most reliable and easiest to automate (using ator cronin Linux, or the task scheduler in Windows) is MySQL's own mysqldump.

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

回答by OMG Ponies

There are two lessons to be learned here:

这里有两个教训需要吸取：

1. Backup data
2. Perform UPDATE/DELETE statements within a transaction, so you can use ROLLBACKif things don't go as planned

1. 资料备份
2. 在事务中执行 UPDATE/DELETE 语句，以便ROLLBACK在事情没有按计划进行时使用

Being aware of the transaction (autocommit, explicit and implicit) handling for your database can save you from having to restore data from a backup.

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

Transactions control data manipulation statement(s) to ensure they are atomic. Being "atomic" means the transaction either occurs, or it does not. The only way to signal the completion of the transaction to database is by using either a COMMITor ROLLBACKstatement (per ANSI-92, which sadly did not include syntax for creating/beginning a transaction so it is vendor specific). COMMITapplies the changes (if any) made within the transaction. ROLLBACKdisregards whatever actions took place within the transaction - highly desirable when an UPDATE/DELETE statement does something unintended.

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

Typically individual DML (Insert, Update, Delete) statements are performed in an autocommit transaction - they are committed as soon as the statement successfully completes. Which means there's no opportunity to roll back the database to the state prior to the statement having been run in cases like yours. When something goes wrong, the only restoration option available is to reconstruct the data from a backup (providing one exists). In MySQL, autocommit is onby default for InnoDB- MyISAM doesn't support transactions. It can be disabled by using:

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

SET autocommit = 0

An explicit transaction is when statement(s) are wrapped within an explicitly defined transaction code block - for MySQL, that's START TRANSACTION. It also requires an explicitly made COMMITor ROLLBACKstatement at the end of the transaction. Nested transactions is beyond the scope of this topic.

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

Implicit transactions are slightly different from explicit ones. Implicit transactions do not require explicity defining a transaction. However, like explicit transactions they require a COMMITor ROLLBACKstatement to be supplied.

隐式交易与显式交易略有不同。隐式事务不需要显式定义事务。但是，与显式事务一样，它们需要提供COMMITorROLLBACK语句。

Conclusion

结论

Explicit transactions are the most ideal solution - they require a statement, COMMITor ROLLBACK, to finalize the transaction, and what is happening is clearly stated for others to read should there be a need. Implicit transactions are OK if working with the database interactively, but COMMITstatements should only be specified once results have been tested & thoroughly determined to be valid.

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

That means you should use:

这意味着你应该使用：

SET autocommit = 0;

START TRANSACTION;
  UPDATE ...;

...and only use COMMIT;when the results are correct.

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

That said, UPDATE and DELETE statements typically only return the number of rows affected, not specific details. Convert such statements into SELECT statements & review the results to ensure correctness priorto attempting the UPDATE/DELETE statement.

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

Addendum

附录

DDL (Data Definition Language) statements are automatically committed - they do not require a COMMIT statement. IE: Table, index, stored procedure, database, and view creation or alteration statements.

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

回答by Dan Soap

Sorry to say that, but there is no way to restore the old field values without a backup.

很抱歉这么说，但没有备份就无法恢复旧的字段值。

Don't shoot the messenger...

不要射信使...

回答by user3299633

Do you have binlogs enabled? You can recover by accessing the binlogs.

你有启用二进制日志吗？您可以通过访问二进制日志来恢复。