php - MySQL 中的复杂搜索

Question

我需要在 MySQL 数据库中进行复杂的搜索。我想通过查询来解决它，以避免 PHP 代码中的大部分更改。

它应该是这样工作的:

1. 用户在搜索字段中输入文本
2. 系统搜索关键词
3. 系统搜索姓名
4. 系统搜索文本
5. 系统按类别搜索。

如果系统在关键字中找到匹配项:1 分，如果它也在名称中:1 分，如果它也在文本中:1 分/提及，如果它也在类别中:1 分。

积分最多的用户为第一名，积分第二多的用户为第二名，以此类推

这个查询是否可以使用 IF、ELSE、CASE、WHEN 来实现？我从来没有做过这种复杂的搜索。问题要大一些，我在看两个表:

TABLE USERS:
id    |    name    |    lastname    |    category    |    keywords    |    text    |
TABLE PAGES:
id    |    user_id    |

我需要列出“用户”表，但如果用户有自己的页面，则首先显示拥有“页面”的用户，然后列出其他没有“页面”的用户。

Answer 1

您可以使用简单的order by 来做到这一点。下面是一个使用 like 的示例，仅用于搜索词:

select u.*
from users u
order by ((keywords like '%@SEARCHTERM%') +
          (name like '%@SEARCHTERM%') +
          (text like '%@SEARCHTERM%') +
          (category like '%@SEARCHTERM%')
         ) desc;

要获取有关页面的信息，请加入:

select u.*
from users u left outer join
     (select user_id, count(*) as numpages
      from pages p
      group by user_id
     ) p
     on u.id = p.user_id
order by (p.numages > 0) desc,
         ((keywords like '%@SEARCHTERM%') +
          (name like '%@SEARCHTERM%') +
          (text like '%@SEARCHTERM%') +
          (category like '%@SEARCHTERM%')
         ) desc;

order by 中的条件使用了 MySQL 将 bool 值视为数字的事实，1 为真，0 为假。您可以将它们相加以获得匹配项总数。

这个例子使用了like。您可以使用 match 做类似的事情。 . .如果您更喜欢全文搜索，则反对。

编辑:

如果您担心列可能包含 NULL，请使用 coalesce():

order by (p.numages > 0) desc,
         ((coalesce(keywords, '') like '%@SEARCHTERM%') +
          (coalesce(name, '') like '%@SEARCHTERM%') +
          (coalesce(text, '') like '%@SEARCHTERM%') +
          (coalesce(category, '') like '%@SEARCHTERM%')
         ) desc;