MySQL自动完成排序问题

Question

我的网站上有一个自动完成功能，用户可以在其中输入学校的课程。然后，它提供了一个类的下拉列表，其中包含他们在字段中某处输入的单词。 Classes 表包含字段 Class_ID、Term_ID、Department_Code、Course_Code、Class_Code、Course_Title、Instructor，这些字段均按用户输入的术语进行搜索。假设用户输入单词“international econ”。然后，它将在字段中搜索这两个术语，如下所示：

SELECT Class_ID, Department_Code, Course_Code, Class_Code, Course_Title, Instructor FROM Classes AND ((REPLACE(REPLACE(REPLACE(CONCAT(Department_Code, Course_Code, Class_Code), '-', ''), '(', ''), ')', '') LIKE '%international%' OR Instructor LIKE '%international%' OR Course_Title LIKE '%international%') AND (REPLACE(REPLACE(REPLACE(CONCAT(Department_Code, Course_Code, Class_Code), '-', ''), '(', ''), ')', '') LIKE '%econ%' OR Instructor LIKE '%econ%' OR Course_Title LIKE '%econ%')) LIMIT 10

关于此查询的两个快速注释 - 我执行 REPLACE() 和 CONCAT() 以便用户可以输入部门/课程/部分作为一个单元，例如 ECON-101 (一个部门和一个课程在一起）。另外，我还粘贴了一个大大简化的查询，其中省略了我当前的排序和分组方式。

然而我的问题涉及如何对结果进行排序。我希望与 Class_Code 匹配的条目返回到顶部，然后是 Course_Code，然后是 Dept，然后是 Course_Title，最后是 prof。换句话说，我希望结果按照用户在自动完成中提供的具体程度进行排序。如果他们知道 Class_Code，我希望它位于顶部。

我想不出一种不需要大量查询的方法来进行这种排序。到目前为止，我能想到的唯一方法是将每个术语与每个字段进行一堆 LIKE 比较别名，然后按这些别名进行排序。这相当于用户在自动完成中输入的字数的 5 倍。换句话说，这是一个巨大而丑陋的查询。

有没有一种简单、有效的方法来进行这种排序？如果有像 INSTR() 函数这样需要多个字符串进行搜索的东西，我可以轻松地将自动完成中的所有单词与每个字段进行比较，并始终对由该函数生成的 5 个别名进行排序。

欢迎任何意见，即使它是超出问题范围的一般性建议。然而，我想提前指出，我不愿意在这里使用 FULLTEXT 或 Sphinx 这样的搜索系统，因为我觉得它不适合像这样的非密集、快速查询。另外，我不愿意重组我的数据库。所以我想通过对我已有的内容进行良好的 MySQL 查询来完成此操作。

提前致谢..

Answer 1

您是否考虑过使用 UNION 运算符？您可以将不同的查询分离到它们自己的 SELECT 语句中，然后使用 UNION 将它们连接在一起。这将自动将它们保留在您想要的组中 - 这就是您运行 SELECT 语句的顺序。

它可能会使您的查询更大，但应该是有效的。

Answer 2

ORDER BY 接受多个值，因此只需按照您想要的顺序列出列即可：

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

您可以尝试的另一种可能性是编写 SQL 生成器函数。有时，如果脚本非常丑陋并且必然会发生变化，那么使用函数以模块化格式生成脚本可以更容易地了解发生了什么。

Answer 3

你可以

order by case when code  like '%blabla%' then 0 else 1 end
        ,case when dept  like '%blabla%' then 0 else 1 end
        ,case when title like '%blabla%' then 0 else 1 end

这样做，代码中的所有匹配项都会在部门等的匹配项之前排序。

更新评论
如果我正确理解最后一条评论，您担心的是您将在查询中重复复杂的表达式？在这种情况下，您可以将替换/连接等包装在派生表中（见下文），或者您可以创建一个视图。

select *
  from (select class_code
              ,department_code
              ,department_code
              ,replace(concat(...)) as code
              ,replace(concat(...)) as dept
              ,replace(concat(...)) as title
          from classes
         where class_code      like '%blabla%'
            or department_code like '%blabla%'
            or class_title     like '%blabla%'
       ) as wrapped
 order by case when code  like '%blabla%' then 0 else 1 end
         ,case when dept  like '%blabla%' then 0 else 1 end
         ,case when title like '%blabla%' then 0 else 1 end

只需确保对实际列而不是计算列进行实际搜索，以确保 mysql 可以使用索引。

顺便说一句，mysql 并不关心查询是否又长又复杂，只要过滤器和连接看起来“简单”即可。