If you are working on a table managed by a transactional storage engine, such as InnoDB or BDB, you can safely test many types of queries within the context of a transaction. If the queries operate as you expect, the transaction can be committed and made permanent. If the queries do not work as anticipated, you can roll them back and return the database to the state that it was in before the transaction started. Caution Not all types of query can be rolled back, including all of the CREATE and DROP queries. Tip Ensure that you are working with tables that are managed by a transactional storage engine by using the SHOW CREATE TABLE query. If a table is managed by a transaction storage engine, the ENGINE option of the CREATE TABLE statement generated by SHOW CREATE TABLE should be set to InnoDB or BDB. Here is an example of testing commands within the context of a transaction: # Ensure you are dealing with a transactional table # Look for the line ENGINE=InnoDB SHOW CREATE TABLE book; BEGIN; # start your transaction UPDATE book SET author = "Dr. Seuss"; SELECT * FROM book; # examine results and # ... notice that data has been overwritten ROLLBACK; # cancel the transaction SELECT * FROM book; # See? All the data is safe. BEGIN; # start a new transaction UPDATE book SET AUTHOR = "Dr. Seuss" WHERE author = "Theodor Geisel"; SELECT * FROM book; # examine results COMMIT; # make changes permanent |