How can I undo a mysql statement that I just executed?
MysqlRollbackMysql Problem Overview
How can I undo the most recently executed mysql query?
Mysql Solutions
Solution 1 - Mysql
If you define table type as InnoDB, you can use transactions. You will need set AUTOCOMMIT=0, and after you can issue COMMIT or ROLLBACK at the end of query or session to submit or cancel a transaction.
ROLLBACK -- will undo the changes that you have made
Solution 2 - Mysql
You can only do so during a transaction.
BEGIN;
INSERT INTO xxx ...;
DELETE FROM ...;
Then you can either:
COMMIT; -- will confirm your changes
Or
ROLLBACK -- will undo your previous changes
Solution 3 - Mysql
Basically: If you're doing a transaction just do a rollback. Otherwise, you can't "undo" a MySQL query.
Solution 4 - Mysql
For some instrutions, like ALTER TABLE, this is not possible with MySQL, even with transactions (1 and 2).
Solution 5 - Mysql
You can stop a query which is being processed by this
Find the Id of the query process by => show processlist;
Then => kill id;
Solution 6 - Mysql
in case you do not only need to undo your last query (although your question actually only points on that, I know) and therefore if a transaction might not help you out, you need to implement a workaround for this:
copy the original data before commiting your query and write it back on demand based on the unique id that must be the same in both tables; your rollback-table (with the copies of the unchanged data) and your actual table (containing the data that should be "undone" than). for databases having many tables, one single "rollback-table" containing structured dumps/copies of the original data would be better to use then one for each actual table. it would contain the name of the actual table, the unique id of the row, and in a third field the content in any desired format that represents the data structure and values clearly (e.g. XML). based on the first two fields this third one would be parsed and written back to the actual table. a fourth field with a timestamp would help cleaning up this rollback-table.
since there is no real undo in SQL-dialects despite "rollback" in a transaction (please correct me if I'm wrong - maybe there now is one), this is the only way, I guess, and you have to write the code for it on your own.