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

debugcn 发表于 Dev

我有一个带有音乐歌曲的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

我在以下方面感到挣扎：

用户搜索内容。我关注所有领域：歌曲标题，艺术家，专辑和流派。但是，通常某个搜索查询包含这些曲目的多个（部分）。

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

在这种情况下，第一个单词是歌曲的名称，第二个和第三个单词是歌手的名称。

另一个例子：

如果我在空格上拆分查询，则会找到正确的轨道。但是，如果另一条仅与这些单词中的一个匹配的曲目具有较高的知名度，则会在实际与搜索查询准确匹配的曲目之前返回该曲目。

我仍然希望对结果进行排序，以使一次与查询的较大部分匹配的事物位于顶部。如何使用SQL做到这一点？

我的静态人气很高，想创建一个新的人气。因此，我想使用某个轨道上所有选票的平均值（这些选票存储在另一个表中），除非还没有选票。我该如何构造一个执行此操作的SQL查询？

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

任何帮助，将不胜感激。

威萨姆·埃尔基克

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

这是代码：

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;