MySQL中如何在AGAINST命令传多参数并结合CASE语句？

我有一个带两个输入参数的存储过程：p_first_name（VARCHAR(15)类型）、p_last_name（VARCHAR(15)类型）。原存储过程使用如下查询：

SELECT first_name, last_name
FROM student_names
WHERE CASE WHEN p_first_name IS NULL THEN 1 ELSE first_name LIKE CONCAT('%',p_first_name,'%') END
AND CASE WHEN p_last_name IS NULL THEN 1 ELSE last_name LIKE CONCAT('%',p_last_name,'%') END;

该查询可正常执行，但LIKE运算符性能过慢，我希望替换为MATCH和AGAINST命令，尝试语句如下：

SELECT first_name, last_name
FROM student_names
WHERE CASE WHEN p_first_name IS NULL THEN 1 ELSE MATCH(first_name) AGAINST(p_first_name) END
AND CASE WHEN p_last_name IS NULL THEN 1 ELSE MATCH(last_name) AGAINST(p_last_name) END;

执行时出现错误。根据MySQL文档，查询格式应为：

SELECT first_name, last_name
FROM student_names
WHERE MATCH(first_name,last_name) AGAINST(p_first_name,p_last_name);

但我需要结合参数为空的处理逻辑，且已创建全文索引：

CREATE FULLTEXT INDEX idx_first_last_name ON student_names(first_name,last_name);

请问该如何实现？

核心原因 #

报错是因为MySQL全文索引要求MATCH后指定的列必须和索引定义的列集合完全一致——你创建的是包含first_name和last_name的联合全文索引，不能单独用MATCH(first_name)或MATCH(last_name)来匹配。下面提供两种可行方案：

方案一：动态SQL拼接（存储过程中首选） #

在存储过程内根据参数是否为空，动态构建全文搜索条件，既能充分利用索引性能，又能灵活处理参数为空的场景：

DELIMITER //
CREATE PROCEDURE get_student_names(IN p_first_name VARCHAR(15), IN p_last_name VARCHAR(15))
BEGIN
    SET @sql = 'SELECT first_name, last_name FROM student_names WHERE 1=1';
    
    -- 处理first_name参数
    IF p_first_name IS NOT NULL THEN
        SET @sql = CONCAT(@sql, ' AND MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE)');
        SET @first_term = CONCAT('+', p_first_name, '*'); -- 前缀匹配，近似LIKE '%xxx%'效果
    END IF;
    
    -- 处理last_name参数
    IF p_last_name IS NOT NULL THEN
        SET @sql = CONCAT(@sql, ' AND MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE)');
        SET @last_term = CONCAT('+', p_last_name, '*');
    END IF;
    
    -- 执行动态SQL
    PREPARE stmt FROM @sql;
    CASE
        WHEN p_first_name IS NOT NULL AND p_last_name IS NOT NULL THEN
            EXECUTE stmt USING @first_term, @last_term;
        WHEN p_first_name IS NOT NULL THEN
            EXECUTE stmt USING @first_term;
        WHEN p_last_name IS NOT NULL THEN
            EXECUTE stmt USING @last_term;
        ELSE
            EXECUTE stmt; -- 双参数为空时返回所有数据
    END CASE;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

说明：

• 使用IN BOOLEAN MODE支持灵活的匹配规则：+表示必须包含该词，*表示前缀匹配，效果接近原LIKE '%xxx%'。
• 动态拼接SQL确保只有参数非空时才加入对应搜索条件，避免无效的全文匹配开销。

方案二：静态SQL兼容参数为空（无需动态SQL） #

如果不想使用动态SQL，可以通过条件判断构建兼容的静态查询，MySQL优化器依然能识别并利用全文索引：

SELECT first_name, last_name
FROM student_names
WHERE 
    (p_first_name IS NULL OR MATCH(first_name, last_name) AGAINST(CONCAT('+', p_first_name, '*') IN BOOLEAN MODE))
    AND 
    (p_last_name IS NULL OR MATCH(first_name, last_name) AGAINST(CONCAT('+', p_last_name, '*') IN BOOLEAN MODE));

说明：

• 逻辑清晰：参数为空时该条件直接成立，否则执行全文匹配。
• 对比动态SQL，会有少量额外的条件判断开销，但实现更简单。

注意事项 #

1. 全文索引最小词长限制：MySQL默认不索引长度小于4的词，若你的参数存在短于4字符的情况，需修改my.cnf（或my.ini）中的ft_min_word_len参数，然后重建全文索引。
2. 匹配模式调整：如果需要精确匹配而非前缀模糊匹配，可以去掉*符号；若要使用自然语言模式，可移除IN BOOLEAN MODE。

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