MariaDB中MATCH() AGAINST()查询如何获取匹配列的名称或别名

这问题我之前也碰到过，其实利用MATCH() AGAINST()在自然语言模式下返回的相关性分数就能轻松实现——当列匹配到搜索词时，分数会是正数；没匹配到则为0。基于这个特性，我们可以用CASE语句来判断并输出匹配的列信息，下面给你几个实用方案：

方案1：显示所有匹配的列名（拼接成单个字段） #

如果希望把所有匹配的列名合并到一个字段里，用逗号分隔，可以用GROUP_CONCAT来拼接（它会自动忽略NULL值）：

SELECT 
  DAT1, 
  DAT2, 
  DAT3,
  GROUP_CONCAT(
    CASE 
      WHEN MATCH(COL1) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) > 0 THEN 'COL1' 
      END,
    CASE 
      WHEN MATCH(COL2) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) > 0 THEN 'COL2' 
      END,
    CASE 
      WHEN MATCH(COL3) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) > 0 THEN 'COL3' 
      END
    SEPARATOR ', '
  ) AS matched_columns
FROM VIEW1 
WHERE 
  MATCH (COL1) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) 
  OR MATCH (COL2) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) 
  OR MATCH (COL3) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE);

比如某行的COL1和COL3都匹配到了，matched_columns就会显示COL1, COL3。

方案2：单独显示每个列的匹配状态 #

如果需要清晰看到每个列是否匹配，可以给每个列单独加一个判断字段：

SELECT 
  DAT1, 
  DAT2, 
  DAT3,
  CASE WHEN MATCH(COL1) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) > 0 THEN 'Yes' ELSE 'No' END AS COL1_matched,
  CASE WHEN MATCH(COL2) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) > 0 THEN 'Yes' ELSE 'No' END AS COL2_matched,
  CASE WHEN MATCH(COL3) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) > 0 THEN 'Yes' ELSE 'No' END AS COL3_matched
FROM VIEW1 
WHERE 
  MATCH (COL1) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) 
  OR MATCH (COL2) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) 
  OR MATCH (COL3) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE);

这样结果里会多出COL1_matched、COL2_matched三个字段，分别显示Yes或No。

方案3：优化性能（避免重复计算） #

上面的方案里，每个MATCH()会被计算多次（WHERE子句和SELECT子句各一次），如果数据量较大，建议用CTE（公共表表达式）提前计算匹配分数，再复用结果：

WITH search_scores AS (
  SELECT 
    DAT1, 
    DAT2, 
    DAT3,
    MATCH(COL1) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) AS col1_score,
    MATCH(COL2) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) AS col2_score,
    MATCH(COL3) AGAINST ('lisa mona aachen' IN NATURAL LANGUAGE MODE) AS col3_score
  FROM VIEW1
)
SELECT 
  DAT1, 
  DAT2, 
  DAT3,
  GROUP_CONCAT(
    CASE WHEN col1_score > 0 THEN 'COL1' END,
    CASE WHEN col2_score > 0 THEN 'COL2' END,
    CASE WHEN col3_score > 0 THEN 'COL3' END
    SEPARATOR ', '
  ) AS matched_columns,
  CASE WHEN col1_score > 0 THEN 'Yes' ELSE 'No' END AS COL1_matched,
  CASE WHEN col2_score > 0 THEN 'Yes' ELSE 'No' END AS COL2_matched,
  CASE WHEN col3_score > 0 THEN 'Yes' ELSE 'No' END AS COL3_matched
FROM search_scores
WHERE col1_score > 0 OR col2_score > 0 OR col3_score > 0;

这个方案只计算一次每个列的匹配分数，性能会更优。

小提示 #

如果需要显示自定义的列别名（而不是原列名COL1/COL2/COL3），只需要把CASE语句里的'COL1'改成你想要的名称即可，比如'用户姓名'、'地址'之类的。

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