The other more or less current project I've found doesn't look as mature, but here it is:
Persist SQL Query "The best and most complete query builder/parser for MySQL (PHP)" according to its Github Tagline, but it's not explicitly validating, but it from the source you can see it does throw Exceptions if a query can't be parsed properly.
Just to mention it: PHP-SQL-Parser is non-validating, so it does not qualify for linting.
lint_result=`mysql mysql_test -B -f -e 'select asdf s where x;' 2>&1`; if [ `echo $lint_result | sed -r "s/ERROR ([0-9]*).*/\1/g"` -eq 1064 ]; then echo -e "Syntax error:\n${lint_result}"; fi
Syntax error:
ERROR 1064 (42000) at line 1: 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 'where x' at line 1
如果查询的语法无法被 mysql 解析，它会声明: ERROR 1064 (42000) at line 1：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行 '' 附近使用的正确语法。
只有当语法正确时，它才会尝试执行查询并意识到该表不存在，但这不再有趣了：
ERROR 1146 (42S02) at line 1: Table 'mysql_test.s' doesn't exist
因此错误 1064 是无效语法。您只需要创建一个空的测试数据库，否则只会出现 FROM 部分错误的错误（例如，需要数据库才能获得有效的语法检查结果：'select asdf from s where x and if;）。
据我测试，它工作正常（Mysql 5.5 版本）。
这是一个完整的 bash 脚本版本：
#!/bin/bash
source_dir=${1};
database="mysql_test";
mysql_args="-h127.0.0.1";
mysql $mysql_args -B -e "DROP DATABASE IF EXISTS $database; CREATE DATABASE $database;";
for file in `find $source_dir -name "*.sql"`; do
lint_result=`mysql $mysql_args $database -f -b < $file 2>&1`;
if [ "`echo $lint_result | sed -r \"s/ERROR ([0-9]*).*/\1/g\"`" = "1064" ]; then
echo -e "Syntax error in file ${file}:\n${lint_result}" && exit 1;
fi;
done
After searching for a CLI tool for syntax linting in Mysql to use in Jenkins and didn't find anything quickly (this Stackoverflow question is one of the first results - LOL) I came up with the following solution (OS: Linux, but should be feasible with Windows too):
Something like the follwoing:
lint_result=`mysql mysql_test -B -f -e 'select asdf s where x;' 2>&1`; if [ `echo $lint_result | sed -r "s/ERROR ([0-9]*).*/\1/g"` -eq 1064 ]; then echo -e "Syntax error:\n${lint_result}"; fi
Syntax error:
ERROR 1064 (42000) at line 1: 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 'where x' at line 1
(To check sql files you can use "< filename.sql" instead of -b -e 'statement')
If the syntax of the query can not be parsed by mysql it claims: ERROR 1064 (42000) at line 1: 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 '' at line 1
Only if the syntax is correct it tries to execute the query and realize that the table don't exists but this isn't interesting anymore:
ERROR 1146 (42S02) at line 1: Table 'mysql_test.s' doesn't exist
Therefor Error 1064 is the invalid syntax. You only need to create an empty test database because otherwise only errors with a wrong FROM part would appear (here for example the database is needed in order to get a valid syntax check result: 'select asdf from s where x and if;).
As far as i tested it works fine (Version Mysql 5.5).
Here a complete bash script vesion:
#!/bin/bash
source_dir=${1};
database="mysql_test";
mysql_args="-h127.0.0.1";
mysql $mysql_args -B -e "DROP DATABASE IF EXISTS $database; CREATE DATABASE $database;";
for file in `find $source_dir -name "*.sql"`; do
lint_result=`mysql $mysql_args $database -f -b < $file 2>&1`;
if [ "`echo $lint_result | sed -r \"s/ERROR ([0-9]*).*/\1/g\"`" = "1064" ]; then
echo -e "Syntax error in file ${file}:\n${lint_result}" && exit 1;
fi;
done
The commercial version of MySQL Workbench has a syntax checker for MySQL statements, but of course that would only cover the database aspects. See http://mysql.com/products/workbench/ (though I found the factoid in the help index for the free app).
这是一个 validator，它根据 SQL 92/99/2003 标准进行验证，但是事实上你提到 MySQL 让我相信你在 SQL 查询中使用了 MySQL 特定的语法。
一种选择是采用不可知的方法来处理数据库层，编写不可知的 SQL 代码。显然，您需要与 Mimer 联系，看看是否可以将其脱机并将其集成到您的 CI 环境中。
整个方法中有几个“如果”，这完全取决于您愿意/能够在当前状态下编写不可知的 SQL 代码。
Here is a validator, which validates against SQL 92/99/2003 standards however the fact you mention MySQL leads me to believe you are using MySQL specific syntax in your SQL queries.
One option is move to an agnostic approach in dealing with your database layer, writing agnostic SQL code. You will obviously need to get in contact with Mimer to see if you can take it offline and integrate it within your CI environment.
Couple of "ifs" in the overall approach and this is all contingent on the fact you are willing/able in the current state to write agnostic SQL code.
发布评论
评论（6）
我编写了一个 CLI 工具，使用 phpMyAdmin SQL 解析器对 SQL 文件进行语法检查： php-sqllint 。
I've written an CLI tool to syntax check SQL files using the phpMyAdmin SQL parser: php-sqllint.
免责声明：我还没有尝试过任何这些工具！嗯嗯！
PHPMyAdmin 的验证 SQL 解析器 是一个积极开发的开源项目，将是我目前最好的选择2021 年。
我发现的另一个或多或少当前的项目看起来并不成熟，但它是：
持久 SQL 查询 根据其 Github 标语，“MySQL (PHP) 的最佳、最完整的查询构建器/解析器”，但它没有明确验证，但从源代码中你可以看到它确实抛出异常如果无法正确解析查询。
只是提一下： PHP-SQL-Parser 是非验证的，所以它确实不符合 linting 的条件。
Disclaimer: I have not yet tried out any of these tools! YMMV!
PHPMyAdmin's validating SQL parser is an actively developed open source project and would be my best bet right now in 2021.
The other more or less current project I've found doesn't look as mature, but here it is:
Persist SQL Query "The best and most complete query builder/parser for MySQL (PHP)" according to its Github Tagline, but it's not explicitly validating, but it from the source you can see it does throw Exceptions if a query can't be parsed properly.
Just to mention it: PHP-SQL-Parser is non-validating, so it does not qualify for linting.
我建议尝试 SQLFluff cli 工具“The SQL Linter for Humans”。
它有一个 文档部分，专门介绍如何将其用作预提交钩子。
请在我的其他答案中查看有关使用它的更多信息，此处。
I suggest trying out SQLFluff cli tool, "The SQL Linter for Humans".
It has a documentation section specifically about using it as a pre-commit hook.
Please see more info about using it in my other answer, here.
在搜索用于在 Jenkins 中使用 Mysql 进行语法检查的 CLI 工具后，没有很快找到任何东西（这个 Stackoverflow 问题是第一个结果 - 哈哈），我想出了以下解决方案（操作系统：Linux，但应该是Windows 也可行）：
类似以下内容：（
要检查 sql 文件，您可以使用“< filename.sql”而不是 -b -e 'statement'）
如果查询的语法无法被 mysql 解析，它会声明:
ERROR 1064 (42000) at line 1：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行 '' 附近使用的正确语法。
只有当语法正确时，它才会尝试执行查询并意识到该表不存在，但这不再有趣了：
因此错误 1064 是无效语法。您只需要创建一个空的测试数据库，否则只会出现 FROM 部分错误的错误（例如，需要数据库才能获得有效的语法检查结果：'select asdf from s where x and if;）。
据我测试，它工作正常（Mysql 5.5 版本）。
这是一个完整的 bash 脚本版本：
After searching for a CLI tool for syntax linting in Mysql to use in Jenkins and didn't find anything quickly (this Stackoverflow question is one of the first results - LOL) I came up with the following solution (OS: Linux, but should be feasible with Windows too):
Something like the follwoing:
(To check sql files you can use "< filename.sql" instead of -b -e 'statement')
If the syntax of the query can not be parsed by mysql it claims:
ERROR 1064 (42000) at line 1: 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 '' at line 1
Only if the syntax is correct it tries to execute the query and realize that the table don't exists but this isn't interesting anymore:
Therefor Error 1064 is the invalid syntax. You only need to create an empty test database because otherwise only errors with a wrong FROM part would appear (here for example the database is needed in order to get a valid syntax check result: 'select asdf from s where x and if;).
As far as i tested it works fine (Version Mysql 5.5).
Here a complete bash script vesion:
MySQL Workbench 的商业版本具有针对 MySQL 语句的语法检查器，但当然这仅涵盖数据库方面。请参阅 http://mysql.com/products/workbench/ （尽管我在帮助中找到了事实免费应用程序的索引）。
The commercial version of MySQL Workbench has a syntax checker for MySQL statements, but of course that would only cover the database aspects. See http://mysql.com/products/workbench/ (though I found the factoid in the help index for the free app).
这是一个 validator，它根据 SQL 92/99/2003 标准进行验证，但是事实上你提到 MySQL 让我相信你在 SQL 查询中使用了 MySQL 特定的语法。
一种选择是采用不可知的方法来处理数据库层，编写不可知的 SQL 代码。显然，您需要与 Mimer 联系，看看是否可以将其脱机并将其集成到您的 CI 环境中。
整个方法中有几个“如果”，这完全取决于您愿意/能够在当前状态下编写不可知的 SQL 代码。
Here is a validator, which validates against SQL 92/99/2003 standards however the fact you mention MySQL leads me to believe you are using MySQL specific syntax in your SQL queries.
One option is move to an agnostic approach in dealing with your database layer, writing agnostic SQL code. You will obviously need to get in contact with Mimer to see if you can take it offline and integrate it within your CI environment.
Couple of "ifs" in the overall approach and this is all contingent on the fact you are willing/able in the current state to write agnostic SQL code.