mysql - 如何搜索两列并按优先级对结果进行排序？

Question

我有一个这样的表:

-- users
+----+------------+-------------+
| id | first_name |  last_name  |
+----+------------+-------------+
| 1  | John       | Kooper      |
| 2  | Martin     | Koop        |
| 3  | Koop       | Sth         |
| 4  | Jack       | Linoff      |
+----+------------+-------------+

这是一个包含我网站用户的表。现在我想在其中寻找。您的查询建议是什么？

<小时/>

这是我当前的查询:

SELECT u.*
FROM users 
WHERE first_name LIKE :input%
   OR last_name LIKE :input%
   OR CONCAT(first_name, last_name) LIKE :input%

如您所见，我的查询只是搜索，没有其他内容。没有任何 ORDER BY 子句，因此没有定义优先级。你知道，我想做的就是实现这样的搜索引擎:

无论如何，有人知道我如何实现查询的优先级吗？例如，如果您搜索 Koop，结果的第一行应该是表中的第二行，因为 last_name 列应该具有更大的优先级(比first_name 列)，koop 是表格第二行 last_name 的精确值。

这是 :input = 'koop' 的预期结果:

+----+------------+-------------+
| 2  | Martin     | Koop        |
| 1  | John       | Kooper      |
| 3  | Koop       | Sth         |
+----+------------+-------------+

我怎样才能做到这一点？

Answer 1

考虑使用全文搜索。

SELECT *,
    MATCH (`first_name`) AGAINST (':input*' IN BOOLEAN MODE) AS `relevance_first_name`,
    MATCH (`last_name`) AGAINST (':input*' IN BOOLEAN MODE) AS `relevance_last_name`
FROM `users`
WHERE MATCH (`first_name`, `last_name`) AGAINST (':input*' IN BOOLEAN MODE)
ORDER BY (`relevance_first_name`)+(`relevance_last_name`*4) DESC