mysql union 查询的结果排序如何按照 union 的顺序返回？

Question

我有一个应用，实现的是关键词搜索，在搜索之前会对词语进行拆词，然后通过组合的形式拆分成多个查询sql，希望的最多词语的匹配结果排在前面，之前用的是 mysql 5.6 版本的，没有问题，符合我的预期，最近换了 mysql 8.0 了，发现结果顺序和以前不一样了，基本上是乱序的。

比如我搜：mysql导入导出

拼成的sql语句最终为：

SELECT 
  * 
FROM 
  (
    SELECT 
      * 
    FROM 
      (
        SELECT 
          `id`, 
          `class_id`, 
          `active`, 
          `add_time`, 
          `show_time`, 
          `url_prefix`, 
          `subject` 
        FROM 
          `glfr_article` 
        WHERE 
          1 = 1 
          AND `subject` LIKE '%MySQL导入导出%' 
        ORDER BY 
          `point_num` DESC, 
          `id` DESC
      ) AS t1 
    UNION 
    SELECT 
      * 
    FROM 
      (
        SELECT 
          `id`, 
          `class_id`, 
          `active`, 
          `add_time`, 
          `show_time`, 
          `url_prefix`, 
          `subject` 
        FROM 
          `glfr_article` 
        WHERE 
          1 = 1 
          AND `subject` LIKE '%MySQL%' 
          AND `subject` LIKE '%导入%' 
          AND `subject` LIKE '%导出%' 
        ORDER BY 
          `point_num` DESC, 
          `id` DESC
      ) AS t2 
    UNION 
    SELECT 
      * 
    FROM 
      (
        SELECT 
          `id`, 
          `class_id`, 
          `active`, 
          `add_time`, 
          `show_time`, 
          `url_prefix`, 
          `subject` 
        FROM 
          `glfr_article` 
        WHERE 
          1 = 1 
          AND `subject` LIKE '%MySQL%' 
          AND `subject` LIKE '%导入%' 
        ORDER BY 
          `point_num` DESC, 
          `id` DESC
      ) AS t3 
    UNION 
    SELECT 
      * 
    FROM 
      (
        SELECT 
          `id`, 
          `class_id`, 
          `active`, 
          `add_time`, 
          `show_time`, 
          `url_prefix`, 
          `subject` 
        FROM 
          `glfr_article` 
        WHERE 
          1 = 1 
          AND `subject` LIKE '%MySQL%' 
          AND `subject` LIKE '%导出%' 
        ORDER BY 
          `point_num` DESC, 
          `id` DESC
      ) AS t4 
    UNION 
    SELECT 
      * 
    FROM 
      (
        SELECT 
          `id`, 
          `class_id`, 
          `active`, 
          `add_time`, 
          `show_time`, 
          `url_prefix`, 
          `subject` 
        FROM 
          `glfr_article` 
        WHERE 
          1 = 1 
          AND `subject` LIKE '%导入%' 
          AND `subject` LIKE '%导出%' 
        ORDER BY 
          `point_num` DESC, 
          `id` DESC
      ) AS t5 
    UNION 
    SELECT 
      * 
    FROM 
      (
        SELECT 
          `id`, 
          `class_id`, 
          `active`, 
          `add_time`, 
          `show_time`, 
          `url_prefix`, 
          `subject` 
        FROM 
          `glfr_article` 
        WHERE 
          1 = 1 
          AND (
            `subject` LIKE '%MySQL%' 
            OR `subject` LIKE '%导入%' 
            OR `subject` LIKE '%导出%'
          ) 
        ORDER BY 
          `point_num` DESC, 
          `id` DESC
      ) AS t6
  ) AS A 
WHERE 
  `active` = '1' 
  AND `show_time` < 1655278257;

哪位大神知道请告知一下，谢谢。

Answer 1

你这个太复杂了，其实可以简写成这样：

SELECT  
`id`
,`class_id`
,`active`
,`add_time`
,`show_time`
,`url_prefix`
,`subject`
FROM `glfr_article`
WHERE
`active` = '1' 
AND `show_time` < 1655278257
AND ( `subject` LIKE '%MySQL%' OR `subject` LIKE '%导入%' OR `subject` LIKE '%导出%' )
ORDER BY 
case subject like '%MySQL导入导出%' WHEN true THEN 0 else 1 end, 
case subject like '%MySQL%' WHEN true THEN 0 else 1 end, 
case subject like '%导入%' WHEN true THEN 0 else 1 end, 
case subject like '%导出%' WHEN true THEN 0 else 1 end
;

原理就是匹配上第几个关键词，第几排序就为0，否则为1，然后把完整匹配放在最前排序即可。
希望能帮助到你。

---

按照补充的需求描述，根据匹配词的个数降序排，可以这么写：

SELECT  
`id`
,`class_id`
,`active`
,`add_time`
,`show_time`
,`url_prefix`
,`subject`
,(length(replace(replace(replace(subject, 'MySQL', 'MySQL_'), '导入', '导入_'), '导出', '导出_')) - length(subject)) as 'point'
FROM `glfr_article`
WHERE
`active` = '1' 
AND `show_time` < 1655278257
AND ( `subject` LIKE '%MySQL%' OR `subject` LIKE '%导入%' OR `subject` LIKE '%导出%' )
ORDER BY 
length(replace(replace(replace(subject, 'MySQL', 'MySQL_'), '导入', '导入_'), '导出', '导出_')) - length(subject) desc

这里的原理是：把匹配的词给替换一下加个下划线，然后拿新字符串的长度减去原字符串的长度，谁的下划线多就说明匹配的词多。