SQL中多列多值组合LIKE查询的优化方案

针对需要批量查询多组厂商-产品名称对应的product_id且避免交叉匹配的场景，推荐以下两种简洁易维护的方案，替代重复的UNION ALL拼接：

方法1：用VALUES构造匹配条件集（推荐，适配现代数据库） #

通过VALUES子句一次性定义所有待匹配的厂商和产品名称组合，再与原表关联查询。只需维护匹配列表部分，无需重复编写相同过滤逻辑。

示例代码（支持MySQL 8.0+/PostgreSQL/SQL Server等）：

SELECT p.product_id
FROM `product` p
JOIN (
  VALUES
    ('%acme%', '%anvil%'),
    ('%democo%', '%dynamite%')
    -- 新增组合直接在此追加行即可，例如 ('%foo%', '%bar%')
) AS matches(manu_pattern, prod_pattern)
ON p.product_id <> ''
   AND LOWER(p.manufacturer) LIKE matches.manu_pattern
   AND LOWER(p.product_name) LIKE matches.prod_pattern;

该方式严格保证每组厂商与产品的对应关系，不会出现交叉匹配，20+组的批量场景下维护成本极低。

方法2：子查询构造匹配列表（兼容旧版数据库） #

若数据库不支持VALUES语法，可改用子查询构造匹配条件集，逻辑与方法1一致：

SELECT p.product_id
FROM `product` p
JOIN (
  SELECT '%acme%' AS manu_pattern, '%anvil%' AS prod_pattern
  UNION ALL
  SELECT '%democo%' AS manu_pattern, '%dynamite%' AS prod_pattern
  -- 继续追加更多组合即可
) AS matches
ON p.product_id <> ''
   AND LOWER(p.manufacturer) LIKE matches.manu_pattern
   AND LOWER(p.product_name) LIKE matches.prod_pattern;

补充说明 #

• 两种方案仅扫描product表一次（UNION ALL会多次扫描表），性能更优；
• 若需区分结果所属的匹配组，可在匹配集中额外添加标识字段，例如：

  VALUES
    ('ACME Anvil', '%acme%', '%anvil%'),
    ('DemoCO Dynamite', '%democo%', '%dynamite%')
  

  随后在SELECT语句中返回该标识字段即可。

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