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. I'm wondering:

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

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? I'm searching MySQL docs and Google, so far no luck.

Thanks for any hints.

Edit: hopefully clarified a bit

Answer 1

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

Answer 2

You could create a temporary table to circumvent side effects of the query:

CREATE TEMPORARY TABLE users SELECT * FROM users;
INSERT INTO users(name) VALUES('UniqueName');
DROP TABLE users;
SELECT * FROM users WHERE name='UniqueName'; -- Should return 0 results

Answer 3

Depending on your MySQL engine and settings, you could start a transaction, try the query, and then do a rollback. Assuming dirty reads are off, that should work.