mysql - 如何对子查询的结果使用正则表达式？

Question

我有两个表。

用户其中有 id 和 phone number

---

id phone_no

1 ---- 9912678

2 ---- 9912323

3 ---- 9912366

---

Admission Table ，其中有id phone number

id phone_no

6 --- 991267823

7 --- 991236621

8 --- 435443455

9 --- 243344333

我想找到与用户表和更新具有相同模式的Admission的表的所有电话号码> 它在用户表中。

所以我正在尝试这个

select phone_no  from admission where phone_no REGEXP (SELECT phone_no
FROM  `users` AS user
WHERE user.phone_no REGEXP  '^(99)+[0-9]{8}')

但我收到此错误子查询返回多于 1 行

寻求帮助。

Answer 1

尝试以下查询之一:

SELECT a.phone_no
FROM admission a
JOIN users u on a.phone_no LIKE concat(u.phone_no, '__')
WHERE u.phone_no REGEXP  '^(99)+[0-9]+$'

或

SELECT a.phone_no
FROM admission a
JOIN users u on a.phone_no REGEXP concat('^', u.phone_no, '[0-9]{2}$')
WHERE u.phone_no REGEXP  '^(99)+[0-9]+$'

如果“尾数”的个数不固定，也可以用:

LIKE concat(u.phone_no, '%')

或

REGEXP concat('^', u.phone_no, '[0-9]*$')

但在这种情况下，如果 users.phone_no 可能是其他 users 的子序列，则您可能需要使用 SELECT DISTICT a.phone_no .phone_no(例如 99123 和 991234)。

更新

在对 users 表的 10K 行和 admission 表的 100K 行运行一些测试后，我得到了以下查询:

SELECT a.phone_no
FROM admission a
JOIN users u 
    ON  a.phone_no >= u.phone_no
    AND a.phone_no < CONCAT(u.phone_no, 'z')
    AND a.phone_no LIKE CONCAT(u.phone_no, '%')
    AND a.phone_no REGEXP CONCAT('^', u.phone_no, '[0-9]*$')
WHERE   u.phone_no LIKE  '99%'
    AND u.phone_no REGEXP  '^(99)+[0-9]*$'
UNION SELECT 0 FROM (SELECT 0) dummy WHERE 0

fiddle

这样你就可以使用 REGEXP 并且仍然有很好的性能。此查询在我的测试用例中几乎立即执行。

从逻辑上讲，您只需要 REGEXP 条件。但是在更大的表上，查询可能会超时。使用 LIKE 条件将在 REGEXP 检查之前过滤结果集。但即使使用 LIKE 查询也不会执行得很好。出于某种原因，MySQL 不对连接使用范围检查。所以我添加了一个明确的范围检查:

    ON  a.phone_no >= u.phone_no
    AND a.phone_no < CONCAT(u.phone_no, 'z')

通过此检查，您可以从 JOIN 部分删除 LIKE 条件。

UNION 部分是 DISTICT 的替代品。 MySQL 似乎将 DISTINCT 翻译成 GROUP BY 语句，但效果不佳。将 UNION 与空结果集一起使用，我强制 MySQL 在 SELECT 之后删除重复项。如果您使用固定数量的尾随数字，则可以删除该行。

您可以根据需要调整 REGEXP 模式:

...
    AND a.phone_no REGEXP CONCAT('^', u.phone_no, '[0-9]{2}$')
...
    AND u.phone_no REGEXP  '^(99)+[0-9]{8}$'
...

如果您只需要 REGEXP 来检查 phone_no 的长度，您还可以使用带“_”占位符的 LIKE 条件。

    AND a.phone_no LIKE CONCAT(u.phone_no, '__')
...
    AND u.phone_no LIKE '99________$'

或将 LIKE 条件与 STR_LENGTH 检查相结合。