我想得到SQL查询的SELECT块,我想得到的是单词SELECT和FROM之间的文本。我应该用什么REGEX?
是否可以以相同的方式提取FROM、WHERE和JOIN块?
更新
我不能使用http://code.google.com/p/php-sql-parser/因为它是为MySQL构建的,而我的MSSQL查询破坏了它。我已经搜索了很多MSSQL解析器,但找不到现成的解决方案,所以我能想到的唯一方法是使用regex自己解析SQL,并遵循select块位于select和FROM关键字之间的原则。
示例
对于查询
SELECT STRAIGHT_JOIN a,b,c
FROM some_table an_alias
WHERE d > 5;
我想退回这样的东西:
$result['SELECT'] => 'STRAIGHT_JOIN a,b,c';
$result['FROM'] => 'some_table an_alias';
$result['WHERE'] => 'd>5';
您可以使用SQL解析器库来完成这项工作。
签出此项目http://code.google.com/p/php-sql-parser/
样本输入:
SELECT STRAIGHT_JOIN a,b,c
from some_table an_alias
WHERE d > 5;
样本输出:
Array
(
[OPTIONS] => Array
(
[0] => STRAIGHT_JOIN
)
[SELECT] => Array
(
[0] => Array
(
[expr_type] => colref
[base_expr] => a
[sub_tree] =>
[alias] => `a`
)
[1] => Array
(
[expr_type] => colref
[base_expr] => b
[sub_tree] =>
[alias] => `b`
)
[2] => Array
(
[expr_type] => colref
[base_expr] => c
[sub_tree] =>
[alias] => `c`
)
)
[FROM] => Array
(
[0] => Array
(
[table] => some_table
[alias] => an_alias
[join_type] => JOIN
[ref_type] =>
[ref_clause] =>
[base_expr] =>
[sub_tree] =>
)
)
[WHERE] => Array
(
[0] => Array
(
[expr_type] => colref
[base_expr] => d
[sub_tree] =>
)
[1] => Array
(
[expr_type] => operator
[base_expr] => >
[sub_tree] =>
)
[2] => Array
(
[expr_type] => const
[base_expr] => 5
[sub_tree] =>
)
)
)
看看这个链接:
http://code.google.com/p/php-sql-parser/
Example Output
Example Query
SELECT STRAIGHT_JOIN a,b,c
from some_table an_alias
WHERE d > 5;
Example Output (via print_r)
Array
(
[OPTIONS] => Array
(
[0] => STRAIGHT_JOIN
)
[SELECT] => Array
(
[0] => Array
(
[expr_type] => colref
[base_expr] => a
[sub_tree] =>
[alias] => `a`
)
[1] => Array
(
[expr_type] => colref
[base_expr] => b
[sub_tree] =>
[alias] => `b`
)
[2] => Array
(
[expr_type] => colref
[base_expr] => c
[sub_tree] =>
[alias] => `c`
)
)
[FROM] => Array
(
[0] => Array
(
[table] => some_table
[alias] => an_alias
[join_type] => JOIN
[ref_type] =>
[ref_clause] =>
[base_expr] =>
[sub_tree] =>
)
)
[WHERE] => Array
(
[0] => Array
(
[expr_type] => colref
[base_expr] => d
[sub_tree] =>
)
[1] => Array
(
[expr_type] => operator
[base_expr] => >
[sub_tree] =>
)
[2] => Array
(
[expr_type] => const
[base_expr] => 5
[sub_tree] =>
)
)
)
$matches = array();
$sql = 'SELECT STRAIGHT_JOIN a,b,c FROM some_table an_alias WHERE d > 5;';
preg_match_all('/SELECT(.+?)FROM(.+?)(?:JOIN(.+?))*WHERE(.+)/gis', $sql, $matches);
意志倾向:
[0] => Array
(
[0] => SELECT STRAIGHT_JOIN a,b,c FROM some_table an_alias WHERE d > 5;
)
[1] => Array
(
[0] => STRAIGHT_JOIN a,b,c
)
[2] => Array
(
[0] => some_table an_alias
)
[3] => Array
(
[0] =>
)
[4] => Array
(
[0] => d > 5;
)