How can I check the SQL syntax in a .sql file?
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.
TLDR:
>awk '{print "EXPLAIN " $0}' statements.sql | mysql --force -u user -p database | grep "ERROR"
Strangely, mysql does not have a built-in switch for this, but you can check syntax by adding the EXPLAIN statement in front of your queries.
If you have a statements.sql file with each statement on one line, prepend EXPLAIN in front of all lines with:
>awk '{print "EXPLAIN " $0}' statements.sql > check.sql
You can then run the statements with the mysql command-line tool and use --force to have it continue on error. It will print an error for any statements with incorrect syntax.
>mysql --force -u user -p database < check.sql
Or to only view the lines with errors:
>mysql --force -u user -p database < check.sql | grep "ERROR"
You can do all of this on one line without creating an intermediate file:
>awk '{print "EXPLAIN " $0}' statements.sql | mysql --force -u user -p database | grep "ERROR"
The basic lexer seems to be implemented in sql/sql_lex.cc. You could use/salvage this to build your own test parser. But this would only check for syntax but not any runtime errors.
edit: For MySQL 8.0+ see How can I check the SQL syntax in a .sql file?