MySQL 跨多个表的关键字搜索

Question

我在音乐库应用程序中使用的 MySQL 数据库中有三个表：

Genre 表包含以下列：

• id
• title （字符串）

>Album 表包含以下列：

• id
• genre_id （Genre.id 的外键）
• title ( string)
• artist （字符串）

，Track 表包含以下列：

• id
• album_id （的外键Album.id)
• title (string)

每个Album可以有任意数量的Tracks，每个Track > 有一个专辑，每个专辑有一个流派。

---

我想实现一个关键字搜索，允许用户输入任意数量的关键字并查找以下所有曲目：

• 具有匹配的标题，
• 位于专辑< /code> 具有匹配的标题 或艺术家，
• 或者位于 专辑 上，流派 具有匹配的 <代码>标题。

结果应按相关性排序。如果每个领域都有相关性排名，那就太好了。例如，Track 的 title 可能比 Genre 的 title 更重要。

此外，解决方案应该使用某种形式的部分搜索。搜索 rubber 应首先匹配 title 为 Rubber 的所有 Tracks，然后匹配 Tracks< /code> 与 title 匹配 *rubber* (*=wildcard)，然后转到相册，等等。然而，我对这些细节不太确定。我只是在寻找一个更通用的解决方案，我可以对其进行调整以满足我的特定需求。

我还应该提到，我正在使用 LAMP 堆栈、Linux、Apache、MySQL 和 PHP。

---

实现此关键字搜索的最佳方式是什么？

---

更新：我一直在尝试通过全文搜索来实现此目的，并且已经提出以下 SQL 语句。

CREATE TABLE `Genre` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `Genre` VALUES(1, 'Rock');

CREATE TABLE `Album` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `genre_id` int(11) NOT NULL,
  `title` text NOT NULL,
  `artist` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY (`title`, `artist`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `Album` VALUES(1, 1, 'Rubber Soul', 'The Beatles');

CREATE TABLE `Track` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `album_id` int(11) NOT NULL,
  `title` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `Track` VALUES(1, 1, 'Drive My Car');
INSERT INTO `Track` VALUES(2, 1, 'What Goes On');
INSERT INTO `Track` VALUES(3, 1, 'Run For Your Life');
INSERT INTO `Track` VALUES(4, 1, 'Girl');

Answer 1

我会使用 Apache Solr。使用数据导入处理程序定义将所有表连接在一起的 SQL 查询，创建全文索引来自连接数据的结果。

---

命名为 MATCH() 的 args 的列必须是您为索引定义的列，并且顺序与您在索引中定义的顺序相同。但是您无法在 MySQL 中跨多个表定义任何索引（全文或其他）。

所以你不能这样做：

WHERE MATCH (g.title, a.title, a.artist, t.title) AGAINST ('beatles')

无论你使用的是布尔模式还是自然语言模式都没有关系。

您需要这样做：

WHERE MATCH (g.title) AGAINST ('beatles')
   OR MATCH (a.title, a.artist) AGAINST ('beatles')
   OR MATCH (t.title) AGAINST ('beatles')

---

您可能也对我的演示感兴趣 MySQL 中的实用全文搜索。

Answer 2

在您要搜索的四列上定义全文索引，然后执行以下操作：

SELECT * FROM genre AS g
  LEFT JOIN album AS a ON g.id = a.genre_id
  LEFT JOIN tracks AS t ON a.id = t.album_id
  WHERE MATCH (g.title,  a.title, a.artist, t.title) AGAINST ('searchstring');

结果将按相关性排序。
有关全文搜索的更多详细信息，请参阅此处：
http://dev.mysql.com/doc/ refman/5.0/en/fulltext-natural-language.html

Answer 3

我会使用像 Sphinx 这样的东西，你可以从你的查询中创建一个索引，然后查询它。
这有点难以理解，但结果比 mysql AGAINST 好 10 倍，而且以后的速度不会有问题。