MySQL 如何检查 .sql 文件中的 SQL 语法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/956507/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I check the SQL syntax in a .sql file?
提问by maxHymanie
How can I check the SQL syntax in a .sql file?
如何检查 .sql 文件中的 SQL 语法？
采纳答案by Hardwareguy
You could paste it into a query browser like the MySQL Query Browser (part of the GUI Tools package) and visually inspect how the keywords and string literals are colored to more easily see if you've made any syntax errors.
您可以将其粘贴到 MySQL 查询浏览器（GUI 工具包的一部分）等查询浏览器中，并直观地检查关键字和字符串文字的颜色，以便更轻松地查看您是否犯了任何语法错误。
回答by VolkerK
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.
基本词法分析器似乎是在 sql/sql_lex.cc 中实现的。您可以使用/挽救它来构建您自己的测试解析器。但这只会检查语法，而不会检查任何运行时错误。
edit: For MySQL 8.0+ see How can I check the SQL syntax in a .sql file?
编辑：对于 MySQL 8.0+，请参阅如何检查 .sql 文件中的 SQL 语法？
回答by Roel Van de Paar
SELECT STATEMENT_DIGEST_TEXTin MySQL 8.0 can be used for MySQL query syntax validation.
SELECT STATEMENT_DIGEST_TEXT在 MySQL 8.0 中可用于 MySQL 查询语法验证。
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;
如果 SQL 不受支持，则会出现错误。就像下一个一样，但是这个响应有一些特别之处；
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.
你看到有什么特别之处了吗？事实上，'HELP' 是一个有效的，但仅限客户端的关键字 - 而不是服务器关键字。无论如何，无效的 SQL 语句都会产生类似的情况；一个错误。
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).
因此，您可以根据 ERROR 与 NO ERROR 进行检查，以了解传递的 SQL 语法是否有效（不包括非常有限的仅客户端命令集，但大多数人不会对这些命令感兴趣）。
Summary; SELECT STATEMENT_DIGEST_TEXTis 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.
概括; SELECT STATEMENT_DIGEST_TEXT是一个全面的 SQL 解析器（虽然这可能不是它的直接/预期功能），它可以在所有情况下用于快速检查语句的有效性，而无需实际执行它们。就 SQL 有效性验证而言，这是一个巨大的进步。
Note that you need to have a MySQL server up and running for this. You can pass queries using the mysql -eclient, or a pipe to mysqletc.
请注意，您需要为此启动并运行 MySQL 服务器。您可以使用mysql -e客户端或管道mysql等传递查询。
回答by culix
TLDR:
域名注册地址：
>awk '{print "EXPLAIN " >awk '{print "EXPLAIN " >mysql --force -u user -p database < check.sql
}' statements.sql > check.sql
}' 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 EXPLAINstatement in front of your queries.
奇怪的是，mysql 没有为此内置开关，但是您可以通过EXPLAIN在查询前添加语句来检查语法。
If you have a statements.sqlfile with each statement on one line, prepend EXPLAINin front of all lines with:
如果您有一个statements.sql文件，每个语句都在一行中，请EXPLAIN在所有行前添加：
>mysql --force -u user -p database < check.sql | grep "ERROR"
You can then run the statements with the mysqlcommand-line tool and use --forceto have it continue on error. It will print an error for any statements with incorrect syntax.
然后，您可以使用mysql命令行工具运行这些语句，并使用--force它在出错时继续运行。对于任何语法不正确的语句，它都会打印错误。
>awk '{print "EXPLAIN " ##代码##}' statements.sql | mysql --force -u user -p database | grep "ERROR"
Or to only view the lines with errors:
或者只查看有错误的行：
##代码##You can do all of this on one line without creating an intermediate file:
您可以在一行中完成所有这些操作，而无需创建中间文件：
##代码##回答by Christopher Klein
There are a few free/try-ware products out there that will allow you to connect to a MySQL database or just paste in the script to validate it. Google is your friend here. Mimer will check ANSI-Standard syntax validationbut probably not handle any MySQL specifics.
有一些免费/试用软件产品可以让您连接到 MySQL 数据库或只需粘贴脚本来验证它。谷歌是你的朋友。 Mimer 将检查 ANSI 标准语法验证，但可能不处理任何 MySQL 细节。
回答by Travis Beale
There are a couple of possiblities. If you are using InnoDB tables that support transactions, you can simply execute a start transaction;at the beginning of your .sql file and a rollback;at the end. MySQL will output any syntax errors.
有几种可能性。如果您正在使用支持事务的 InnoDB 表，您只需start transaction;在 .sql 文件的开头执行 a并rollback;在结尾执行 a 。MySQL 将输出任何语法错误。
If you are testiing UPDATEor DELETEstatements, you could add LIMIT 0to the end to prevent these queries from making any database changes, and still have MySQL check the syntax.
如果您正在测试UPDATE或DELETE声明，您可以添加LIMIT 0到末尾以防止这些查询进行任何数据库更改，并且仍然让 MySQL 检查语法。
回答by KM.
just run it....
运行它....
begin transaction
开始交易
run it
运行
rollback
回滚
