1

What is the right method to testing mysql query?

Example: i want to testing running update query

update `transaction` set date="2013-01-01" where id=1;

after the query update, i see the database is change correctly. but then i want the database revert back to the state before the query update execute, because that's query purpose is for testing only.

Is there any tools to easily create virtual database to testing or maybe mysql have functions to revert back the state?

GusDeCooL
  • 5,639
  • 17
  • 68
  • 102
  • 1
    Start a transaction. Update. Select. Rollback. – juergen d Nov 18 '13 at 15:52
  • @juergend do you have link documentation for how to do rollback? – GusDeCooL Nov 18 '13 at 15:55
  • 2
    http://dev.mysql.com/doc/refman/5.1/en/commit.html – juergen d Nov 18 '13 at 15:56
  • 1
    Use a test database instead of testing on your live data. – Barmar Nov 18 '13 at 15:56
  • @Barmar Yeah, first i though of that. but if make mistake on test database. I would need able to revert back the data, if not i will always need replicate database every fails query which it time consuming. – GusDeCooL Nov 18 '13 at 15:59
  • Kinda duplicate http://stackoverflow.com/questions/11011384/how-to-test-an-sql-update-statement-before-running-it – cen Nov 18 '13 at 15:59
  • 1
    not sure why the question was down voted. The question is perfectly valid and in sync with a lot of tools if not databases providing similar functionality. The same can be achieved with transactions but the DBMS or DBMS tools could also provide a similar functionality with --dry-run prefix so that the transactions are rolled back after the execution. – Divick Jul 01 '14 at 09:38

1 Answers1

2

As Barmar suggested. Use a test database. However if you're making a scary change to a live database and have tested it on a test database it can set your mind at ease to use transactions to confirm the live changes are as you expect. For this you can use transactions.

START TRANSACTION;
UPDATE foo SET baz = "bar";
SELECT baz FROM foo;-- Shows bar
ROLLBACK; -- Alternatively 'COMMIT'
SELECT baz FROM foo;-- Shows whatever was there previously.

Note that changes to schema are not transactional (i.e. altering tables cannot be rolled back).

Jim
  • 22,354
  • 6
  • 52
  • 80