Piskvor Piskvor - 12 days ago 5
MySQL Question

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

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
SELECT
s, 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

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.