Is there a command to test an SQL query without executing it? ( MySQL or ANSI SQL )

Question

Is there anything like this:
TEST DELETE FROM user WHERE somekey = 45;

That can return any errors, for example that somekey doesn't exist, or some constraint violation or anything, and reporting how many rows would be affected, but not executing the query?
I know you can easily turn any query in a select query that has no write or delete effect in any row, but that can lead to errors and it's not very practical if you want to test and debug many queries.

Answer 1

The only thing I know of is to wrap it in a transaction that is always rolled back:

BEGIN TRANSACTION

DELETE FROM user WHERE somekey = 45;

ROLLBACK TRANSACTION

Make sure you execute the entire block and not just the delete statement. Also, DO NOT run this on any production environment or any system where you cannot afford to lose the data.

Answer 2

As of MySQL 5.6, the EXPLAIN keyword works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

If your query has a syntax error, then it will still fail, however if successful you will only see the results of an EXPLAIN and the query will not make any changes.

This is much simpler than doing schema changes, using temp tables, or aborting transactions as all you need to do is insert "EXPLAIN " in front of your existing query.

More information: https://dev.mysql.com/doc/refman/5.6/en/explain.html

Answer 3

In MySQL use this

START TRANSACTION;
QUERY;

It is important to use ";" because if you don't, it won't work. For example

START TRANSACTION;
UPDATE tableX SET colX = valueA, colY = valueB WHERE id=1

Reference here http://dev.mysql.com/doc/refman/5.0/en/commit.html

Answer 4

ANSI SQL: No.

MySQL: Maybe. The EXPLAIN keyword originally worked only with SELECT, but it might have been extended to UPDATE and DELETE by now.