MySQL查询space_id所有id匹配batch_ids表对应记录的方法

原查询抛出Single-row subquery returns more than one row错误，是因为concat()中嵌套的子查询会返回space_id表的全部id（多行结果），MySQL中用于等值、LIKE匹配的单行子查询仅允许返回1条记录，无法直接传入多值完成批量匹配。

方案1：内关联匹配（推荐，性能更优） #

通过两张表关联，对每个space_id匹配符合规则的batch_id，写法如下：

WITH space_ids AS(
  SELECT id FROM space_id
)
SELECT DISTINCT b.batch_id
FROM batch_ids b
INNER JOIN space_ids s
  ON b.request_id LIKE CONCAT('task_', s.id, '_task%');

语句中加DISTINCT是为了避免极端场景下同一个batch_id匹配多个space_id时返回重复结果，最终输出的就是你需要的去重后batch_id集合。

方案2：EXISTS子查询写法 #

不需要额外去重，只要batch_id能匹配到任意一个存在的space_id就会被返回，逻辑和方案1完全一致：

SELECT batch_id
FROM batch_ids b
WHERE EXISTS (
  SELECT 1
  FROM space_id s
  WHERE b.request_id LIKE CONCAT('task_', s.id, '_task%')
);

如果你的MySQL是5.x版本不支持CTE语法，直接去掉WITH部分，在关联/子查询中直接查询space_id表即可。

• 不要直接使用CONCAT(s.id, '%')做匹配，否则当space_id存在前缀重叠场景（比如同时存在spa_1、spa_11两个id）时，会出现匹配错误。拼接匹配串时带上request_id中id前后的固定字符task_和_task，可以保证匹配精准度，不会误捞不属于对应空间的记录。

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