如何修改SQL查询语句，无需拆分关键词实现Query2的匹配效果？

需求：给定完整关键词（如Plain-woven Navy），无需手动拆分，即可匹配产品about字段或关联colorName字段的任意部分。当前Query #1因完整匹配关键词无法返回预期结果，Query #2需手动拆分关键词才能生效，需修改Query #1实现无需拆分的等效效果。

核心思路 #

将完整关键词按空格拆分为独立词汇，对每个词汇分别执行LIKE模糊匹配，只要about字段或关联的colorName字段包含任意拆分后的词汇，即命中结果。

MySQL版本实现 #

-- 修改后的Query #1（MySQL）
SET @keyword = 'Plain-woven Navy';

SELECT * 
FROM Product a 
WHERE 
  -- 匹配about字段包含任意关键词片段
  EXISTS (
    SELECT 1 
    FROM (
      SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@keyword, ' ', n), ' ', -1) AS word
      FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) nums
      WHERE n <= LENGTH(@keyword) - LENGTH(REPLACE(@keyword, ' ', '')) + 1
    ) words
    WHERE a.about LIKE CONCAT('%', words.word, '%')
  )
  OR 
  -- 匹配关联颜色名称包含任意关键词片段
  a.productId IN (
    SELECT b.productId 
    FROM productItem b 
    JOIN ProductColor c ON b.productColorId = c.productColorId
    WHERE EXISTS (
      SELECT 1 
      FROM (
        SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@keyword, ' ', n), ' ', -1) AS word
        FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) nums
        WHERE n <= LENGTH(@keyword) - LENGTH(REPLACE(@keyword, ' ', '')) + 1
      ) words
      WHERE c.colorName LIKE CONCAT('%', words.word, '%')
    )
  );

PostgreSQL版本实现 #

-- 修改后的Query #1（PostgreSQL）
WITH words AS (
  SELECT unnest(string_to_array('Plain-woven Navy', ' ')) AS word
)
SELECT * 
FROM Product a 
WHERE 
  EXISTS (SELECT 1 FROM words WHERE a.about LIKE '%' || word || '%')
  OR 
  a.productId IN (
    SELECT b.productId 
    FROM productItem b 
    JOIN ProductColor c ON b.productColorId = c.productColorId
    WHERE EXISTS (SELECT 1 FROM words WHERE c.colorName LIKE '%' || word || '%')
  );

说明 #

• 上述代码无需手动拆分关键词，直接传入完整字符串即可实现与Query #2一致的匹配逻辑
• 不同数据库的字符串分割函数存在差异：MySQL通过SUBSTRING_INDEX结合数字表实现拆分，PostgreSQL使用string_to_array+unnest更简洁
• 数字表中的数量（如示例中的1-4）可根据业务中关键词的最大词数调整，确保覆盖所有可能的拆分情况

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