Testing MySQL Query

Question

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?

@juergend do you have link documentation for how to do rollback? — GusDeCooL
– GusDeCooL, Commented Nov 18, 2013 at 15:55
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
– Divick, Commented Jul 1, 2014 at 9:38

Jim · Accepted Answer · 2013-11-18 16:00:47Z

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).

answered Nov 18, 2013 at 16:00

Jim

22.7k6 gold badges55 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Testing MySQL Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related