使用regex查找sql中的所有子查询

7gyucuyw 于 2023-01-14 发布在其他

关注(0)|答案(2)|浏览(152)

下面的regex \(\s*select([^()]*)\)给出了所有子查询，但忽略了子查询中包含的任何sql函数，如max（）、count（）等。regex的工作原理如下

SELECT student.name, teacher.name, sub.subject_name
FROM student student
JOIN teacher teacher on teacher.id=student.teacher_id
JOIN (select subject.name subject_name, subject.id id from subject) sub on sub.id=teacher.subject_id 
where student.id=1

它发现子查询为select subject.name subject_name, subject.id id from subject，而不是

SELECT student.name, teacher.name, sub.subject_name
FROM student student
JOIN teacher teacher on teacher.id=student.teacher_id
JOIN (select group_concat(subject.name) subject_names from subject)
where student.id=1

找不到select group_concat(subject.name) subject_names from subject这样的匹配项，如何才能获得匹配项...？

regex

来源：https://stackoverflow.com/questions/75092218/finding-all-subqueries-in-sql-using-regex

2条答案

按热度按时间

vs91vp4v1#

您可以尝试使用regex /\((?:[^()]|\((?:[^()]|\([^()]*\))*\))*\)/mg;

赞(0）回复(0）举报 2023-01-14

yhived7q2#

接受挑战。看看这是否会导致可行的解决方案：

mysql> explain select (select 'a' from t) x,
                      (select 'b' from t) y,
                      "select 'c'" z
                    from t;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4243 |   100.00 | NULL  |
|  3 | SUBQUERY    | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4243 |   100.00 | NULL  |
|  2 | SUBQUERY    | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4243 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select 
        (/* select#2 */ select 'a' from `covid`.`t`) AS `x`,
        (/* select#3 */ select 'b' from `covid`.`t`) AS `y`,
        'select \'c\'' AS `z`
     from `db`.`t`
1 row in set (0.00 sec)

然后使用/*.. */注解和平衡括号解析EXPLAIN。
（我编辑了空格以达到效果。）

赞(0）回复(0）举报 2023-01-14

我来回答

使用regex查找sql中的所有子查询

2条答案

相关问题

热门标签

最新问答