如何将MySQL SELECT结果作为MATCH AGAINST自然语言查询的输入

初始SQL的问题 #

你最初编写的SQL存在三个核心问题：

• 语法错误1：MATCH(name)后多写了逗号，MATCH() AGAINST()是固定组合的语法结构，不能拆分插入逗号
• 语法错误2：AGAINST默认只支持传入单个字符串参数，不能直接接返回多行结果的子查询
• 逻辑缺陷：就算语法修正，直接传入多行值也无法实现多关键词联合检索的效果

更新1中1064报错的原因 #

你看到的#1064 - SQL query syntax error near 'tb2错误，由以下几个问题共同导致：

1. 直接报错触发点：AGAINST括号内的子查询后面加了别名tb2、tb4，作为值传递的子查询不需要也不能加别名
2. 派生表语法错误：FROM后面的子查询(SELECT alias_name as name FROM temp_active_ingredients_aliases)没有加别名，MySQL强制要求所有派生表必须指定别名
3. 逻辑错误：内层子查询返回多行name值，AGAINST不支持直接传入多行值作为检索词

可行实现方案 #

前置准备 #

首先要给对应字段创建全文索引，否则无法使用MATCH AGAINST语法：

-- 给有效成分表的name字段建全文索引
ALTER TABLE active_ingredients ADD FULLTEXT INDEX ft_ingredient_name(name);
-- 给别名表的alias_name字段建全文索引
ALTER TABLE temp_active_ingredients_aliases ADD FULLTEXT INDEX ft_alias_name(alias_name);

实现思路 #

你需要先把所有要检索的关键词拼接为一个空格分隔的单个字符串，再传入AGAINST中做检索，有两种常用实现方式：

方式1：使用会话变量暂存检索词（可读性更高）

-- 调整GROUP_CONCAT长度限制，避免检索词过多被截断，可根据实际需求调整大小
SET SESSION group_concat_max_len = 102400;

-- 拼接未精确匹配的name为空格分隔的检索字符串
SET @search_keywords = (
    SELECT GROUP_CONCAT(name SEPARATOR ' ')
    FROM (
        SELECT name FROM active_ingredients
        UNION ALL
        SELECT ai.name
        FROM active_ingredients ai
        INNER JOIN temp_active_ingredients_aliases ta 
            ON ta.alias_name = ai.name
    ) t
    GROUP BY name
    HAVING COUNT(*) = 1
);

-- 执行全文检索
SELECT 
    alias_name AS name,
    MATCH(alias_name) AGAINST(@search_keywords IN NATURAL LANGUAGE MODE) AS score
FROM temp_active_ingredients_aliases
WHERE MATCH(alias_name) AGAINST(@search_keywords IN NATURAL LANGUAGE MODE) > 0
ORDER BY score DESC;

方式2：单句SQL实现（无需会话变量）

SET SESSION group_concat_max_len = 102400;

SELECT 
    ta.alias_name AS name,
    MATCH(ta.alias_name) AGAINST(s.search_str IN NATURAL LANGUAGE MODE) AS score
FROM temp_active_ingredients_aliases ta
CROSS JOIN (
    SELECT GROUP_CONCAT(name SEPARATOR ' ') AS search_str
    FROM (
        SELECT name FROM active_ingredients
        UNION ALL
        SELECT ai.name
        FROM active_ingredients ai
        INNER JOIN temp_active_ingredients_aliases ta 
            ON ta.alias_name = ai.name
    ) t
    GROUP BY name
    HAVING COUNT(*) = 1
) s
WHERE MATCH(ta.alias_name) AGAINST(s.search_str IN NATURAL LANGUAGE MODE) > 0
ORDER BY score DESC;

注意事项 #

• 如果需要使用布尔模式检索，把语法中的IN NATURAL LANGUAGE MODE替换为IN BOOLEAN MODE即可
• InnoDB引擎全文检索默认最小匹配词长为4，如果需要匹配更短的关键词，需要修改配置文件中的innodb_ft_min_token_size参数，重启MySQL后重建全文索引生效
• 如果检索关键词数量很多，可以继续调大group_concat_max_len的取值，避免关键词被截断

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