blocks|key|184548|text|正则表达式只能匹配语言，只有有限的状态自动机才能解析，这是非常有限的，而SQL是语法。可以证明您不能用regex.sql来验证。因此，可以停止尝试。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|184549|entityMap^0|17|V|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Regular expressions can match languages only a finite state automaton can parse, which is very limited, whereas SQL is a syntax. It can be demonstrated you can't validate SQL with a regex. So, you can stop trying.

blocks|key|854868|text|SQL是一个类型-2语法，它太强大了，不能用正则表达式来描述。这就像您决定生成C#代码，然后在不调用编译器的情况下验证它一样。一般来说，数据库引擎过于复杂，不易存根。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|854869|尽管如此，你可以试试ANTLR的SQL语法。|854870|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Chomsky_hierarchy|1|https://github.com/antlr/grammars-v4/wiki^0|6|6|0|0|A|B|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]]]

SQL is a <a href="http://en.wikipedia.org/wiki/Chomsky_hierarchy" rel="nofollow noreferrer">type-2 grammar</a>, it is too powerful to be described by regular expressions. It's the same as if you decided to generate C# code and then validate it without invoking a compiler. Database engine in general is too complex to be easily stubbed. 

That said, you may try <a href="https://github.com/antlr/grammars-v4/wiki" rel="nofollow noreferrer">ANTLR's SQL grammars</a>.

blocks|key|854849|text|据我所知，这是超越正则表达式和接近黑暗艺术的BnF和编译器。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|854850|http://savage.net.au/SQL/|offset|length|854851|同样的事情发生在那些想要做正确的语法突出显示的人身上。你开始把东西塞进regex，然后你写了一个编译器.|854852|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|P|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

As far as I know this is beyond regex and your getting close to the dark arts of BnF and compilers.

<a href="http://savage.net.au/SQL/" rel="nofollow noreferrer">http://savage.net.au/SQL/</a> 

Same things happens to people who want to do correct syntax highlighting. You start cramming things into regex and then you end up writing a compiler...

blocks|key|854899|text|我也遇到了同样的问题--一种适用于所有标准sql语句的方法是拆分内存中的Sqlite数据库并针对它发出查询，如果您返回了一个“表不存在”错误，那么您的查询就被正确地解析了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|854900|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I had the same problem - an approach that would work for all the more standard sql statements would be to spin up an in-memory Sqlite database and issue the query against it, if you get back a "table does not exist" error, then your query parsed properly.

blocks|key|858989|text|你不能把生成的SQL传递给一个数据库，并在数据库上使用“解释”，捕捉任何表示格式错误的SQL的异常吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|858990|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Off the top of my head: Couldn't you pass the generated SQL to a database and use EXPLAIN on them and catch any exceptions which would indicate poorly formed SQL?

blocks|key|184435|text|你试过懒惰的选择器了吗。而不是尽可能匹配，他们匹配尽可能少，这可能是你需要的引号。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|184436|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Have you tried the lazy selectors. Rather than match as much as possible, they match as little as possible which is probably what you need for quotes.

blocks|key|184508|text|要验证这些查询，只需使用将NOEXEC设置为运行它们，这就是当您解析一个查询而不执行它时，Entreprise是这样做的。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|184509|此外，如果您使用regex来验证sql查询，您几乎可以确定您将遗漏一些角情况，或者由于其他原因该查询无效，即使它在语法上是正确的。|184510|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms188394.aspx^0|C|A|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

To validate the queries, just run them with <a href="http://msdn.microsoft.com/en-us/library/ms188394.aspx" rel="nofollow noreferrer">SET NOEXEC ON</a>, that is how Entreprise Manager does it when you parse a query without executing it.

Besides if you are using regex to validate sql queries, you can be almost certain that you will miss some corner cases, or that the query is not valid from other reasons, even if it's syntactically correct.

blocks|key|1183460|text|我建议使用相同的模式创建数据库，可能使用嵌入式sql引擎，并将sql传递给它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1183461|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I suggest creating a database with the same schema, possibly using an embedded sql engine, and passing the sql to that.

blocks|key|184595|text|我认为您甚至不需要创建模式来验证语句，因为在成功解析语句之前，系统不会尝试解析object_name等。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|184596|以Oracle为例，如果您这样做，您肯定会得到一个错误：|184597|select+*+from+non_existant_table;|code-block|syntax|javascript|184598|在这种情况下，"ORA-00942:表或视图不存在“。|184599|但是，如果执行：|184600|select+*+frm+non_existant_table;|184601|然后，您将得到一个语法错误，"ORA-00923:+FROM关键字未找到预期的位置“。|184602|应该可以将错误分类为语法分析错误，这些错误表示不正确的语法以及与表名和权限等有关的错误。|184603|此外，还有不同的RDBMS，甚至不同的版本，允许不同的语法，我认为您确实需要使用db引擎来完成这个任务。|184604|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|11|8|@]|9|@]|A|$G|H]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

I don't think that you even need to have the schema created to be able to validate the statement, because the system will not try to resolve object_name etc until it has successfully parsed the statement.

With Oracle as an example, you would certainly get an error if you did:

<pre><code>select * from non_existant_table;
</code></pre>

In this case, "ORA-00942: table or view does not exist".

However if you execute:

<pre><code>select * frm non_existant_table;
</code></pre>

Then you'll get a syntax error, "ORA-00923: FROM keyword not found where expected".

It ought to be possible to classify errors into syntax parsing errors that indicate incorrect syntax and errors relating to tables name and permissions etc..

Add to that the problem of different RDBMSs and even different versions allowing different syntaxes and I think you really have to go to the db engine for this task.

blocks|key|1183582|text|有反文法来解析SQL。使用内存数据库或非常轻量级的数据库(如方石岩+)确实是一个更好的主意。从解析的角度测试SQL是否有效，对我来说似乎是浪费的，而检查表和列名以及查询的细节则更有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1183583|entityMap|0|LINK|mutability|MUTABLE|url|http://www.antlr.org/grammar/list|1|http://www.oracle.com/technology/products/timesten/index.html|2|http://www.sqlite.org/^0|1|3|0|D|5|1|U|3|2|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]|$A|T|B|U|1|V]|$A|W|B|X|1|Y]]|C|$]]|$1|D|3|-4|5|6|7|Z|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]|N|$5|G|H|I|C|$J|O]]]]

There are <a href="http://www.antlr.org/grammar/list" rel="nofollow noreferrer">ANTLR grammars</a> to parse SQL. It's really a better idea to use an <a href="http://www.oracle.com/technology/products/timesten/index.html" rel="nofollow noreferrer">in memory database</a> or a very lightweight database such as <a href="http://www.sqlite.org/" rel="nofollow noreferrer">sqlite</a>. It seems wasteful to me to test whether the SQL is valid from a parsing standpoint, and much more useful to check the table and column names and the specifics of your query.

blocks|key|854915|text|最好的方法是验证用于创建查询的参数，而不是查询本身。接收变量的函数可以检查字符串的长度、有效的数字、有效的电子邮件等等。您可以使用正则表达式来执行此验证。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|854916|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The best way is to validate the parameters used to create the query, rather than the query itself. A function that receives the variables can check the length of the strings, valid numbers, valid emails or whatever. You can use regular expressions to do this validations.

blocks|key|184444|text|我假设您做了一些类似于.\*的尝试，而不是[%5E"]*，这将使您不会吃光整条线。在字符串中有\的情况下，它仍然会产生假阳性。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|184445|entityMap^0|B|3|L|5|19|1|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|O|8|@]|D|@]|E|$]]]|G|$]]

I am assuming you did something like <code>.\*</code> try instead <code>[^"]*</code> that will keep you from eating the whole line. It still will give false positives on cases where you have <code>\</code> inside your strings.

blocks|key|184717|text|public+bool+IsValid(string+sql)
{
string+pattern+=+@"SELECT\s.*FROM\s.*WHERE\s.*";
Regex+rgx+=+new+Regex(pattern,+RegexOptions.IgnoreCase);
return+rgx.IsMatch(sql);
}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|184718|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>public bool IsValid(string sql)
{
string pattern = @"SELECT\s.*FROM\s.*WHERE\s.*";
Regex rgx = new Regex(pattern, RegexOptions.IgnoreCase);
return rgx.IsMatch(sql);
}
</code></pre>

I was writing some Unit tests last week for a piece of code that generated some SQL statements.

I was trying to figure out a regex to match <code>SELECT</code>, <code>INSERT</code> and <code>UPDATE</code> syntax so I could verify that my methods were generating valid SQL, and after 3-4 hours of searching and messing around with various regex editors I gave up.

I managed to get partial matches but because a section in quotes can contain any characters it quickly expands to match the whole statement.

Any help would be appreciated, I'm not very good with regular expressions but I'd like to learn more about them.

By the way it's C# RegEx that I'm after.

Clarification

I don't want to need access to a database as this is part of a Unit test and I don't wan't to have to maintain a database to test my code. which may live longer than the project.

Regular expression to match common SQL syntax?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

上周，我正在为生成一些SQL语句的代码编写一些Unit测试。我试图找出一个正则表达式来匹配SELECT、INSERT和UPDATE语法，这样我就可以验证我的方法是否生成了有效的SQL，经过3-4个小时的搜索和处理各种regex编辑器之后，我放弃了。我设法得到了部分匹配，但是由于引号中的节可以包含任何字符，所以它会迅速展开以匹配整个语句。任何帮助都会很感激，我不太擅长正则表达式，但我想了解更多关于正

问与普通SQL语法匹配的正则表达式？
EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问与普通SQL语法匹配的正则表达式？EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问与普通SQL语法匹配的正则表达式？
EN