Validate MySql Query

Question

How can I validate a MySql query before executing it?

Answer 1

Run EXPLAIN SELECT …

It will parse your query and show you the execution plan (which algorithms will it use to execute your query, and in which order).

It's a good thing by itself, i. e. always do it, not only for validating.

This will help you to understand what's going on behind the curtains and build more efficient queries.

Answer 2

You could also include the query in a transaction, and then cancel the transaction. This way you can also see the results of the query (even if it is an update or delete query), and then act accordingly, without affecting the database (unless you commit the transaction).

Answer 3

There isn't a good way! Basically, you need to execute the statement; there's no -l flag.

Two common methods are:

1. Preceding a SELECT with EXPLAIN. This only works for SELECT statements, so it's not a general solution
2. Use Transactions. This is only good in 5.x Innodb tables, so it's not a general solution. This also doesn't help for SELECTS which you don't want to take time to execute.

Neither of these work for me. The only decent general solution I've found is to create a test suite that creates temp tables in the likeness of the real ones, and then executes the queries against them:

CREATE TEMPORARY TABLE users_test LIKE users;
CREATE TEMPORARY TABLE auth_test LIKE auth;

You can actually forget about making those temps and keep them around in a '_test' database and just change your DSN when appropriate.

Otherwise, you need to parametrize your queries so you can tell it to use the '_test' tables when necessary.

This is far from ideal, but is the best solution I've found since it executes the queries lightning fast (no data to join/decide on) and doesn't affect the DB.

I would love if someone proved me wrong and pointed to a MySQL Parser that accepts a string and returns either TRUE or an error message.

Answer 4

Add a slave with "blackhole" as default table type.

Now run any query on that slave.