MySQL使用REGEXP跨表过滤数据及Error 1242子查询报错问题

嘿，这个错误我太熟悉了！咱们先搞清楚问题出在哪，再一步步解决它。

错误原因分析 #

你原来的语句里，CONCAT_WS("|^", SELECT partnercode from t1)这部分有问题：CONCAT_WS函数需要的是单个值或一组离散的参数，但SELECT partnercode from t1返回了1859条数据，MySQL不知道怎么把这么多行数据直接塞给CONCAT_WS，所以就抛出了错误代码1242："Subquery returns more than 1 row"。

你的需求是找到t2中username以t1任意partnercode开头的行，这里有两种靠谱的解决方法，咱们一个个说：

方法1：用EXISTS子查询（推荐，无长度限制） #

这种方法逻辑最直观，也不会遇到字符串长度限制的问题，适合数据量大的场景。核心思路是：对t2的每一行，检查是否存在t1中的某个partnercode，能匹配username的开头部分。

查询匹配的行 #

SELECT t2.username 
FROM t2 
WHERE EXISTS (
    SELECT 1 
    FROM t1 
    WHERE t2.username LIKE CONCAT(t1.partnercode, '%')
);

删除匹配的行 #

把上面的SELECT改成DELETE就行：

DELETE FROM t2
WHERE EXISTS (
    SELECT 1 
    FROM t1 
    WHERE t2.username LIKE CONCAT(t1.partnercode, '%')
);

注意点

如果你的partnercode里包含%或者_（LIKE的通配符），需要用ESCAPE子句转义，比如：

DELETE FROM t2
WHERE EXISTS (
    SELECT 1 
    FROM t1 
    WHERE t2.username LIKE CONCAT(REPLACE(REPLACE(t1.partnercode, '%', '\%'), '_', '\_'), '%') ESCAPE '\'
);

方法2：用GROUP_CONCAT构建正则表达式 #

如果你的partnercode数量不多，或者没有正则特殊字符（比如.、*、+等），可以先把所有partnercode合并成一个正则串（格式是^code1|^code2|^code3），再用REGEXP匹配。

步骤1：调整GROUP_CONCAT的长度限制 #

MySQL默认的group_concat_max_len很小（比如1024字节），1859条数据肯定会超过，所以先临时调整会话级别的参数：

SET SESSION group_concat_max_len = 1000000; -- 按需设置，确保能装下所有partnercode的合并串

步骤2：查询匹配的行 #

SELECT t2.username 
FROM t2 
WHERE t2.username REGEXP (
    SELECT GROUP_CONCAT(DISTINCT CONCAT('^', partnercode) SEPARATOR '|') 
    FROM t1
);

步骤3：删除匹配的行 #

DELETE FROM t2
WHERE username REGEXP (
    SELECT GROUP_CONCAT(DISTINCT CONCAT('^', partnercode) SEPARATOR '|') 
    FROM t1
);

注意点

如果partnercode里包含正则元字符（比如.会匹配任意字符），需要先转义这些字符，否则正则会匹配错误。比如可以用REGEXP_REPLACE来转义：

SELECT GROUP_CONCAT(DISTINCT CONCAT('^', REGEXP_REPLACE(partnercode, '([.\\+*?[\\]^$(){}=!<>|:\\-])', '\\$1')) SEPARATOR '|') 
FROM t1;

内容的提问来源于stack exchange，提问作者samueljames3