php - 如何在 SQL 中对多个字段进行排序和过滤搜索

Question

我有一个包含音乐歌曲的 SQL 数据库。每首歌当然都有艺术家、专辑和流派。他们还有一个通用的“人气”计数器，该计数器是从外部来源获得的。但是，我也想为用户提供对歌曲进行投票的机会。最后，搜索结果应该根据这种流行度以及原始查询结果的准确性进行排序。

我目前使用的查询如下:

SELECT *
FROM p2pm_tracks
WHERE
 `artist` LIKE '%$searchquestion%' OR
 `genres` LIKE '%$searchquestion%' OR
 `trackname` LIKE '%$searchquestion%' OR
 `album_name` LIKE '%$searchquestion%'
ORDER BY `popularity` DESC
LIMIT $startingpoint, $resultsperpage

我遇到以下问题:

1. 用户搜索内容。我查看所有领域:歌曲标题、艺术家、专辑 和流派。但是，通常某个搜索查询包含多个(部分)这些轨道。

例如，用户可能会搜索Opening Philip Glass。

在这种情况下，第一个词是歌曲名称，第二个和第三个词是艺术家姓名。

另一个例子:

如果我在空格上拆分查询，则会找到正确的轨道。但是，如果仅匹配其中一个词的另一首轨道具有更高的流行度，则该轨道将在实际准确匹配搜索查询的轨道之前返回。

我仍然希望以一种方式对结果进行排序，即一次匹配查询的较大部分的内容位于顶部。我如何使用 SQL 执行此操作？

1. 我有静态流行度，想创造一个新的。因此，我想使用某个轨道上所有选票的平均值(这些选票存储在另一个表中)，除非还没有选票的情况。 我如何构建执行此操作的 SQL 查询？

我的应用程序是用 PHP 构建的，但我想尽可能多地使用 SQL 来执行此操作，最好使用尽可能少的查询以减少延迟。

如有任何帮助，我们将不胜感激。

Answer 1

您可以为搜索结果中的每一列添加权重。

代码如下:

SELECT *,
    CASE WHEN `artist` LIKE '%$searchquestion%' THEN 1 ELSE 0 END AS artist_match,
    CASE WHEN `genres` LIKE '%$searchquestion%' THEN 1 ELSE 0 END AS genres_match,
    CASE WHEN `trackname` LIKE '%$searchquestion%' THEN 1 ELSE 0 END AS trackname_match,
    CASE WHEN `album_name` LIKE '%$searchquestion%' THEN 1 ELSE 0 END AS album_name_match,
FROM p2pm_tracks
WHERE
 `artist` LIKE '%$searchquestion%' OR
 `genres` LIKE '%$searchquestion%' OR
 `trackname` LIKE '%$searchquestion%' OR
 `album_name` LIKE '%$searchquestion%'
ORDER BY 
 `artist_match` DESC,
 `genres_match` DESC,
 `trackname_match` DESC,
 `album_name_match` DESC,
 `popularity` DESC,
LIMIT $startingpoint, $resultsperpage

此查询将收集与以下相关的结果:

• 首先是艺术家，
• 然后是流派，
• 然后是轨道的标题，
• 然后是专辑名称，
• 然后是歌曲的流行度

要优化此查询，您应该避免使用“LIKE”并改用“FULLTEXT SEARCH”。

优化后的代码将是:

SELECT *,
    CASE WHEN MATCH (artist) AGAINST ('$searchquestion') THEN 1 ELSE 0 END AS artist_match,
    CASE WHEN MATCH (genres) AGAINST ('$searchquestion') THEN 1 ELSE 0 END AS genres_match,
    CASE WHEN MATCH (trackname) AGAINST ('$searchquestion') THEN 1 ELSE 0 END AS trackname_match,
    CASE WHEN MATCH (album_name) AGAINST ('$searchquestion') THEN 1 ELSE 0 END AS album_name_match,
FROM p2pm_tracks
WHERE
 MATCH (artist) AGAINST ('$searchquestion') OR
 MATCH (genres) AGAINST ('$searchquestion') OR
 MATCH (trackname) AGAINST ('$searchquestion') OR
 MATCH (album_name) AGAINST ('$searchquestion')
ORDER BY 
 `artist_match` DESC,
 `genres_match` DESC,
 `trackname_match` DESC,
 `album_name_match` DESC,
 `popularity` DESC,
LIMIT $startingpoint, $resultsperpage

并确保您正在为 MySQL 表使用 MyISAM 引擎，并为要搜索的列创建索引。MySQL 表的代码应如下所示:

CREATE TABLE p2pm_tracks (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    artist VARCHAR(255) NOT NULL,
    trackname VARCHAR(255) NOT NULL,
    ...
    ...
    FULLTEXT (artist,trackname)
) ENGINE=MyISAM;

有关详细信息，请检查以下内容: - http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html - http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

如果您正在寻找更高级的东西，请查看 Solr(基于 Lucene)、Sphinx、ElasticSearch(基于 Lucene)等。