如何在MySQL中优先搜索多个列? [英] How to search multiple columns with priority in MySQL?
问题描述
我目前有一个PHP函数(codeigniter),该函数接受一个查询字符串(来自帖子),并返回相关的MySQL行,如下所示:
I currently have a PHP function (codeigniter) which takes a query string (from post), and returns relevant MySQL rows, like this:
$term = $this->input->post("query");
$rows = $this->db->query("SELECT id, title as 'desc' , abbreviation , etc FROM shows WHERE abbreviation LIKE '%".$term."%' OR title LIKE '%".$term."%' OR etc LIKE '%".$term."%' LIMIT 10; ")->result_array();
这可行,但是可以说我的表是这样的:
this works, but lets say my table is like this :
id title abbreviation
-- ---------------------- ------------
1 The Big Bang Theory TBBT
2 How I Met Your Mother HMYM
3 a show called tbbt hmym ASCTM
现在,当用户搜索 tbbt
时,它将首先返回第三行。但是我希望缩写是主要参数。
Now, when the user searches for tbbt
, it returns 3rd row first. But I want the abbreviation to be the "main" parameter.
因此,如果缩写匹配,请先返回它,然后查找标题列。
So if abbreviation matches, return it first, then look for the title column.
我想我可以用PHP做到这一点,
I think I can do it with PHP, like this:
// pseudo-code :
$abbreviation_matches = result("bla bla WHERE abbreviation LIKE term AND NOT title like term");
$title_matches = result("bla bla WHERE NOT abbreviation LIKE term AND title LIKE term");
$result = append($abbreviation_matches , $title_matches);
但是我担心这不会那么有效,因为它涉及2个独立的SQL查询。那么,有没有更有效的方法可以做到这一点?最好在一个查询中?
But I'm afraid this will not be so efficient, since it involves 2 separate SQL queries. So is there a more efficient way to do this? Preferrably in one query ?
实际情况是有4列,但是只有一列具有优先级,因此其他列的顺序并不重要。
And the real case is with 4 columns, but only one column has priority, so the other ones are not important in order.
谢谢您的帮助!
推荐答案
这可能是解决方案之一:
It could be one of the solutions:
SELECT id, desc, abbreviation, etc FROM
(
SELECT 1 AS rnk, id, title as 'desc' , abbreviation , etc FROM shows
WHERE abbreviation LIKE '%".$term."%'
UNION
SELECT 2 AS rnk, id, title as 'desc' , abbreviation , etc FROM shows
WHERE title LIKE '%".$term."%'
) tab
ORDER BY rnk
LIMIT 10;
或者您可以通过以下方式实现相同目的:
Or you can achieve the same by the following:
SELECT id, desc, abbreviation, etc FROM
(
SELECT CASE WHEN abbreviation LIKE '%".$term."%' THEN 1 ELSE 2 END As rnk,
id, title AS desc, abbreviation, etc
FROM shows
WHERE abbreviation LIKE '%".$term."%'
OR title LIKE '%".$term."%'
) tab
ORDER BY rnk
LIMIT 10;
这篇关于如何在MySQL中优先搜索多个列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!