如何在SQL的locate函数中用子查询匹配多值？有无替代方案？

你最初的写法无法运行，是因为locate()函数的第一个参数要求是单个字符串，而你写的子查询会返回多值结果集，不符合标量参数要求。你当前用的inner join ... on 1=1的写法不是唯一实现方案，而且这种写法本质是先构造两表笛卡尔积再过滤，当主表或子查询结果数据量较大时，中间结果集会极速膨胀，性能隐患非常明显，同时如果主表某行的field2匹配了子查询中多个field1值，还会出现重复返回的问题，需要额外加去重逻辑。

1. EXISTS关联匹配（通用性最强，常规场景优先选） #

该写法不会生成全量笛卡尔积，遍历主表行时只要在子查询结果中匹配到第一个符合条件的值，就会终止当前行的判断，执行效率远高于笛卡尔积连接，同时不会返回重复结果，不需要额外去重。
写法示例：

with cte_subselect as (select distinct field1 from subquery)
select * from table t
where exists (
    select 1 from cte_subselect s
    where locate(s.field1, t.field2) > 0
)

2. 正则聚合匹配（适合子查询结果量小的场景） #

如果所用数据库支持字符串聚合函数（MySQL用group_concat、PostgreSQL/SQL Server用string_agg），可以把子查询返回的所有目标值拼接成正则匹配规则，不需要关联表就能完成一次性匹配，写法更简洁。
以MySQL为例：

select * from table
where field2 regexp (
    select group_concat(distinct field1 separator '|')
    from subquery
)

使用该方案需要注意两个问题：

• 子查询返回值过多时，拼接出的字符串可能超过数据库聚合函数的长度限制，导致匹配不全
• 如果待匹配的field1值中包含正则特殊字符（比如.、*、|、+等），需要提前做转义处理，否则匹配逻辑会出错

3. 全文索引匹配（适合大数据量文本场景） #

如果主表数据量在十万级以上、field2为大文本字段，不管是连接匹配还是逐行字符串匹配的性能都会很差，此时可以给field2字段建立全文索引，把子查询的关键词拼成全文检索规则做匹配，性能比逐行locate高几个量级。不同数据库的全文检索语法有差异：MySQL用match() against()语法，PostgreSQL用to_tsvector搭配to_tsquery实现。

• 常规业务数据量场景优先选EXISTS写法，坑最少、通用性最强，性能优于你当前的内连接写法
• 子查询返回值数量少、关键词无特殊字符的简单场景，可以用正则聚合写法简化代码
• 大数据量长文本匹配场景，优先用全文索引方案，不要靠字符串函数做全表扫描

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