当前位置：文江博客话题详情

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

发布于 2024-08-19 15:09:29 字数 121 浏览 8 评论 0原文

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

我忘记在 WHERE 子句中创建 id 字段。

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

如何恢复呢？

我没有备份....

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

淡笑忘祈一世凡恋 2024-08-26 15:09:30

这里有两个教训值得学习：

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

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

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

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

SET autocommit = 0

显式事务是指语句包含在显式定义的事务代码块中 - 对于 MySQL，即开始事务。它还需要在事务结束时显式创建 COMMIT 或 ROLLBACK 语句。嵌套事务超出了本主题的范围。

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

结论

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

这意味着您应该使用：

SET autocommit = 0;

START TRANSACTION;
  UPDATE ...;

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

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

附录

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

There are two lessons to be learned here:

Backup data
Perform UPDATE/DELETE statements within a transaction, so you can use ROLLBACK if things don't go as planned

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 COMMIT or ROLLBACK statement (per ANSI-92, which sadly did not include syntax for creating/beginning a transaction so it is vendor specific). COMMIT applies the changes (if any) made within the transaction. ROLLBACK disregards whatever actions took place within the transaction - highly desirable when an UPDATE/DELETE statement does something unintended.

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 on by default for InnoDB - MyISAM doesn't support transactions. It can be disabled by using:

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 COMMIT or ROLLBACK statement at the end of the transaction. Nested transactions is beyond the scope of this topic.

Implicit transactions are slightly different from explicit ones. Implicit transactions do not require explicity defining a transaction. However, like explicit transactions they require a COMMIT or ROLLBACK statement to be supplied.

Conclusion

Explicit transactions are the most ideal solution - they require a statement, COMMIT or 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 COMMIT statements should only be specified once results have been tested & thoroughly determined to be valid.

That means you should use:

SET autocommit = 0;

START TRANSACTION;
  UPDATE ...;

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

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 prior to attempting the UPDATE/DELETE statement.

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.

回复收藏 0 原文