MySQL关联表全文搜索如何实现精确匹配结果优先排序

问题场景 #

用户搜索关键词Happy Song时，当前返回结果中精确匹配的内容没有排在首位，顺序如下：

• Happy Children
• Happy Days
• Happy People
• Happy Song
• Happy Station
• ...

初始实现代码 #

最开始使用的查询逻辑如下：

SELECT  `artist`, `song`, `genre`, `pass`, `id`
    FROM  `songs`
    JOIN  `artists` USING (`pass`)
    WHERE  MATCH(`artist`) AGAINST("happy song")
      OR  MATCH(`song`, `genre`) AGAINST("happy song")
    ORDER BY  `song` ASC
    LIMIT  0,30
') or die(mysql_error($_connect));

无效尝试 #

2022年7月3日曾尝试调整排序规则，修改后代码如下：

$query = mysqli_query($_connect, '
    SELECT  `artist`, `song`, `genre`, `pass`, `id`
        FROM  `songs`
        JOIN  `artists` USING (`pass`)
        WHERE  MATCH(`artist`) AGAINST("happy song")
          OR  MATCH(`song`, `genre`) AGAINST("happy song")
        ORDER BY  `song` = "happy song" DESC
') or die(mysql_error($_connect));

修改后未达到预期，搜索happy song时，仅包含单个关键词"song"的结果依然排在更靠前的位置。

相关表结构 #

artists表 #

CREATE TABLE `artists` (
  `artist` varchar(40) NOT NULL,
  `pass` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PACK_KEYS=1;

ALTER TABLE `artists`
  ADD PRIMARY KEY (`pass`),
  ADD UNIQUE KEY `uni_que` (`artist`) USING BTREE;
ALTER TABLE `artists` ADD FULLTEXT KEY `full_txt` (`artist`);
COMMIT;

songs表 #

CREATE TABLE `songs` (
  `song` varchar(80) NOT NULL,
  `tags` varchar(40) NOT NULL,
  `style` varchar(20) NOT NULL,
  `pass` bigint(20) UNSIGNED NOT NULL,
  `id` bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PACK_KEYS=1;

ALTER TABLE `songs`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `songs` ADD FULLTEXT KEY `song` (`song`,`tags`,`style`);
COMMIT;

解决方案 #

之前写的ORDER BY song = "happy song" DESC不生效，核心原因有两个：

1. 等值匹配是大小写敏感的，数据库里存的歌名是首字母大写的Happy Song，匹配小写的happy song会判定为不相等
2. 排序规则没有做分层，除了精确匹配的行之外，剩下的结果没有明确的排序依据，MySQL会按默认扫描顺序返回，自然会乱序。另外你代码里混用了mysql_和mysqli_系列函数，报错时也拿不到正确提示。

直接按下面的逻辑改即可，按匹配精度从高到低设置多级排序权重：

1. 最高权重：歌名不区分大小写完全等于搜索词
2. 次高权重：歌名以搜索词开头
3. 第三权重：按全文搜索的相关度得分排序（MATCH...AGAINST本身会返回0-1的分数，匹配关键词越多、词距越近分数越高）
4. 兜底规则：同权重下按歌名字母正序排列

修改后的可直接运行代码如下：

$keyword = "happy song";
// 注意：生产环境请改用mysqli预处理绑定参数，不要直接拼接用户输入，避免SQL注入
$query = mysqli_query($_connect, "
    SELECT  `artist`, `song`, `genre`, `pass`, `id`,
        -- 计算两个全文匹配字段的总相关度
        MATCH(`artist`) AGAINST('$keyword') + MATCH(`song`, `genre`) AGAINST('$keyword') AS relevance
    FROM  `songs`
    JOIN  `artists` USING (`pass`)
    WHERE  MATCH(`artist`) AGAINST('$keyword')
      OR  MATCH(`song`, `genre`) AGAINST('$keyword')
    ORDER BY
        LOWER(`song`) = LOWER('$keyword') DESC,
        LOWER(`song`) LIKE LOWER('$keyword%') DESC,
        relevance DESC,
        `song` ASC
    LIMIT  0,30
") or die(mysqli_error($_connect));

额外注意点 #

• 你给出的songs表结构里没有genre字段，只有tags和style，如果运行报字段不存在，把查询里的genre换成你实际要匹配的字段名即可
• 如果需要把「歌名中包含完整搜索短语」也设为高优（不要求整个字段完全等于搜索词），就把第一行排序规则换成LOWER(\song`) LIKE LOWER('%$keyword%') DESC`
• 如果需要进一步提升精确短语的全文匹配权重，可以改用布尔全文搜索，给完整短语加更高权重，比如把AGAINST部分改成AGAINST('\"$keyword\" $keyword' IN BOOLEAN MODE)，包含完整短语的结果相关度分会更高。

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