sql - detect mysql errors without executing it

Question

Suppose I make a query "UPDATE table SET etc etc"

Then I "Execute" this query

if the query is fine, it executes and if the query has errors, it will return mysql errors

My question is...is there a way to do the following:

if the query has errors, it will return mysql errors. However, if the query is fine, DON'T execute and instead just say that the query is fine

In other words, is there a way to check that mysql will accept my query WITHOUT actually modifying the entries in the database in the event that there is no error?

Answer 1

EXPLAIN does the trick if you're running MySQL 5.6 or greater.

explain update whatever;

If the query is ok, it shows the execution plan. Else, it returns the syntax error.

---

If you're running a lesser version of MySQL, I see a few options:

1. The recommended option: Have a test database ready that mirrors your production database at least in structure. Ideally have it populated with test data to verify the query is not only syntactically correct; but that it works as expected.
2. Run the query in the scope of a TRANSACTION that is immediately rolled back.
3. Run a version of the query that is slightly modified to match NO ROWS.

For instance:

update table set col1 = @val1 where col2 = @val2;

Becomes:

update table set col1 = @val1 where (col2 = @val2) and 1=0;

---

So, if you're running 5.6 or greater, the EXPLAIN trick is neat. If not, options 2 and 3 from list are also neat(ish) tricks. But, you should generally be hitting a development server with your in-development queries anyway.