使用正则表达式从查询中获取表名

I'm looking into finding a Pattern in order to get the table name for this type of SQL query;

我正在寻找一个Pattern来获取这种类型的SQL查询的表名;

INSERT INTO table(uuid,type) VALUES (?,?)

Here I want to get "table"

在这里,我想得到“表”

I have a pattern but it's working only if there is no brackets like INSERT INTO table VALUES (?,?)

我有一个模式,但它只有在没有像INSERT INTO表VALUES(?,?)这样的括号时才有效

[from|into|update]\s+(?:\w+.)(\w+)(\s$|\s+(WHERE))

Thanks

NB : it's not the same pattern than in get table name from query

注意:它与查询中获取表名的模式不同

3 个解决方案

#1

This will never be a very fool proof way of extracting table names from SQL. But here is a solution to the problem using regex like you need. You've specified the WHERE part in the provided expression but is it really necessary if you just need the name?

这绝不是从SQL中提取表名的非常简单的方法。但是这里是使用你需要的正则表达式的问题的解决方案。您已在提供的表达式中指定了WHERE部分,但如果您只需要名称,是否真的有必要?

(?is)\b(?:from|into|update)\s+(\w+)

Note that there are many ways a SQL statement can be formatted and it's very unlikely that anyone can come up with an expression that can parse SQL in the way you need.

请注意,SQL语句可以通过多种方式进行格式化,并且任何人都不可能想出一个能够以您需要的方式解析SQL的表达式。

Demo

#2

As people have already mentioned, Regular Expressions are not full proof solution for extracting table names from SQL queries... As tons of things has to be considered, which would be trickier to express in RegX, and would break out in one or other cases....

正如人们已经提到的那样,正则表达式不是用于从SQL查询中提取表名的完全证明解决方案......因为必须考虑大量的事情,这在RegX中表达起来会比较棘手,并且会在一个或其他情况下爆发....

Then what?? Full proof sql parsers like JSQL parser?

那又怎样?完整的证明sql解析器,如JSQL解析器?

Well if you just need to extract table names from SQLs, full blown sql parsers would be over kill, further most of the parser does not support all the dialects, You may end up modifying the grammer files for you need, just to extract table names.

好吧,如果你只需要从SQL中提取表名,那么完整的sql解析器就会被淘汰,大部分解析器都不支持所有的方言,你可能最终修改你需要的语法文件,只是为了提取表名。

For this purpose I have written a small library for parsing table names from SQLs, with hardly 80 physical lines of code, it work with almost any SQL. Refer to unit test cases for more detail.

为此,我编写了一个用于从SQL解析表名的小型库,几乎没有80行物理代码,几乎可以与任何SQL一起使用。有关详细信息,请参阅单元测试用例。

#3

Definitely not fool proof but may be good start: (?i)(SELECT|JOIN)(?=((\s+)(\`?)(?P<entity_name>[A-Za-z0-9_$""]*)(\`?)(\s)))

绝对不是万无一失但可能是一个好的开始:(?i)(SELECT | JOIN)(?=((\ s +)(\`?)(?P [A-Za-z0-9_ $“”] *)(\'?)(\ S)))

#1

(?is)\b(?:from|into|update)\s+(\w+)