How can I check the SQL syntax in a .sql file?

Question

How can I check the SQL syntax in a .sql file?

Answer 1

SELECT STATEMENT_DIGEST_TEXT in MySQL 8.0 can be used for MySQL query syntax validation.

8.0.4>SELECT STATEMENT_DIGEST_TEXT('FLUSH TABLES')\G STATEMENT_DIGEST_TEXT('FLUSH TABLES'): FLUSH TABLES   8.0.4>SELECT STATEMENT_DIGEST_TEXT("SET GLOBAL second_cache.key_buffer_size=128*1024;")\G STATEMENT_DIGEST_TEXT("SET GLOBAL second_cache.key_buffer_size=128*1024;"): SET GLOBAL `second_cache` . `key_buffer_size` = ? * ? ;  8.0.4>SELECT STATEMENT_DIGEST_TEXT("create TABLE t1 ( a2 int unsigned not null, b2 int unsigned not null, c2 int unsigned not null, primary key (a2), index b2x (b2), index c2x (c2) ) ENGINE=MEMORY;")\G STATEMENT_DIGEST_TEXT("create TABLE t1 ( a2 int unsigned not null, b2 int unsigned not null, c2 int unsigned not null, primary key (a2), index b2x (b2), index c2x (c2) ) ENGINE=MEMORY;"): CREATE TABLE `t1` ( `a2` INTEGER UNSIGNED NOT NULL , `b2` INTEGER UNSIGNED NOT NULL , `c2` INTEGER UNSIGNED NOT NULL , PRIMARY KEY ( `a2` ) , INDEX `b2x` ( `b2` ) , INDEX `c2x` ( `c2` ) ) ENGINE = MEMORY ;  

If the SQL is not supported, you'll get an error. Like the next one, but there is something special about this response;

8.0.4>SELECT STATEMENT_DIGEST_TEXT('HELP SELECT')\G ERROR 3676 (HY000): Could not parse argument to digest function: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT' at line 1". 

Did you see what was special? It is the fact that 'HELP' is a valid, but client-side only keyword - not a server keyword. In any case, an invalid SQL statement will produce a similar situation; an ERROR.

Thus, you can check based on ERROR vs NO ERROR to know whether the passed SQL syntax is valid or not (excluding the very limited set of client-side-only commands, but those would not be of interest to most people).

Summary; SELECT STATEMENT_DIGEST_TEXT is a comprehensive SQL parser (while that may not be it's direct/intended function) which can be used in all cases to check the validity of statements quickly and without actually executing them. This is huge progress as far as SQL validity validation is concerned.

Note that you need to have a MySQL server up and running for this. You can pass queries using the mysql -e client, or a pipe to mysql etc.