撤消 mysql UPDATE 命令
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10922171/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Undo a mysql UPDATE command
提问by Naveen
I was trying to do an update on the MySQL server and accidentally forgot to add an additional WHERE clause which was supposed to edit one row.
我试图在 MySQL 服务器上进行更新，但不小心忘记添加一个额外的 WHERE 子句，该子句应该编辑一行。
I now have 3500+ rows edited due to my error.
由于我的错误，我现在编辑了 3500 多行。
I may have a back up but I did a ton of work since the last backup and I just dont want to waste it all because of 1 bad query.
我可能有备份，但自上次备份以来我做了大量的工作，我只是不想因为 1 个错误的查询而浪费它。
Please tell me there is something i can do to fix this.
请告诉我我可以做些什么来解决这个问题。
采纳答案by Jeremy Holovacs
If you committed your transaction, it's time to dust off that backup, sorry. But that's what backups are for. I did something like that once myself... once.
如果你提交了你的交易，是时候清理备份了，抱歉。但这就是备份的用途。我自己也做过类似的事情……一次。
回答by user158017
Just an idea - could you restore your backup to a NEW database and then do a cross database query to update that column based on the data it used to be?
只是一个想法 - 您能否将备份恢复到新数据库，然后进行跨数据库查询以根据过去的数据更新该列？
回答by Michael Durrant
Nothing.
没有。
Despite this you can be glad that you've got that learning experience under your belt and be proud of how you'll now change your habits to greatly reduce the chance of it happening again. You'll be the 'master' now that can teach the young pups and quote from actual battle-tested experience.
尽管如此，您会很高兴自己拥有了这种学习经验，并为您现在如何改变习惯以大大减少再次发生的机会感到自豪。您现在将成为“大师”，可以教导幼崽并引用经过实际战斗考验的经验。
回答by UsamaAmjad
There is only one thing that you can fixnow is your habit, it will help you in future. I know its an old question and OP must have learned the lesson. I am just posting it for others because I also learned the lesson today.
只有一件事你现在可以改正，那就是你的习惯，它会在未来帮助你。我知道这是一个老问题，OP 一定已经吸取了教训。我只是为其他人发布它，因为我今天也吸取了教训。
So I was supposed to run a query which was supposed to update some fifty records and then MySQLreturned THIS 48500 row(s) affectedwhich gave me a little heart attack due to one silly mistake in WHEREcondition :D
所以我应该运行一个查询，它应该更新大约 50 条记录，然后MySQL返回 THIS 48500 row(s) affected，由于WHERE条件中的一个愚蠢的错误，这让我有点心脏病发作：D
So here are the learnings:
所以这里是学习：
- Always check your
querytwice before running it but sometimes it won't help because you can still make that silly mistake. - Since Step 1 is not always helpful its better that you always take backup of your
DBbefore running any query that will affect the data. - If you are lazy to create a backup (as I was when running that silly query) because the DB is large and it will take time then at least use
TRANSACTIONand I think this is a good effortlessway to beat the disaster. From now on this is what I do with every query that affects data:
query在运行它之前总是检查两次，但有时它无济于事，因为你仍然可能犯那个愚蠢的错误。- 由于步骤 1 并不总是有帮助，因此
DB在运行任何会影响数据的查询之前始终备份您的数据会更好。 - 如果您懒得创建备份（就像我运行那个愚蠢的查询时一样），因为数据库很大并且需要时间，那么至少使用
TRANSACTION，我认为这是一种轻松战胜灾难的好方法。从现在开始，这就是我对影响数据的每个查询所做的：
I start the Transaction, run the Query, and then check the results if they are OK or Not. If the results are OK then I simply COMMITthe changes otherwise to overcome the disaster I simply ROLLBACK
我启动Transaction，运行Query，然后检查结果是否正常。如果结果没问题，那么我只是COMMIT改变，否则我只是为了克服灾难ROLLBACK
START TRANSACTION;
UPDATE myTable
SET name = 'Sam'
WHERE recordingTime BETWEEN '2018-04-01 00:00:59' AND '2019-04-12 23:59:59';
ROLLBACK;
-- COMMIT; -- I keep it commented so that I dont run it mistakenly and only uncomment when I really want to COMMIT the changes
