How to check if MySQL query is valid without executing it?

Question

I'm making a simple tool that will get a string of MySQL commands and run it (on several DB servers sequentially). I trust the users to be sensible, but mistakes happen, and I'm looking for a way to prevent basic typos:

Is there a way to validate, at runtime, (relatively simple) MySQL queries to see if they're syntactically correct?

I'm not looking for semantic correctness, e.g. table names or join applicability; just something like a spellcheck for SQL queries.

In other words,

SELECT * FROM x;

or

INSERT INTO x SET id=1,bar="foo";

would be marked valid, whereas any of those would not:

SELECT FROM x;
SECLET * RFOM x;
ISNETR INTO x SET id=1;
HJBGYGCRYTCY;

For SELECTs, I could bend EXPLAIN to my needs - run EXPLAIN SELECT (...) and watch for errors, but is there a way to check for other commands as well?

Answer 1

To verify a query i use the EXPLAIN command. You can take any SQL query and add EXPLAIN before it and execute. If query is wrong, error will be returned.

Examples:

explain select * from users;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

And wrong query

explain select * from users2;
ERROR 1146 (42S02): Table 'test.users2' doesn't exist

P.S. Explain works for insert, update, delete too. Not only select

Answer 2

Not without knowledge of the schema (for example, is 'x' a table?) and writing a SQL parser. Your MySQL query tool should be able to do that kind of validation (intellisense if you like) but I know from first hand experience, most of the (free) MySQL tools are abysmal.

'Preparing' the query would do what you want, but is a runtime check, not a compile time check - you seem to be looking for a compile time/offline solution.