跨多个表进行MySQL关键字搜索

I have three tables in a MySQL database used in a music library application:

我在一个MySQL数据库中有三个表用于音乐库应用程序:

The Genre table has columns:

体裁表有列:

id
id
title (string)
标题(字符串)

The Album table has columns:

相册表有列:

id
id
genre_id (foreign key to Genre.id)
genre_id (Genre.id的外键)
title (string)
标题(字符串)
artist (string)
艺术家(字符串)

and the Track table has columns:

跟踪表有列:

id
id
album_id (foreign key to Album.id)
清蛋白(清蛋白外键)
title (string)
标题(字符串)

Each Album can have any number of Tracks, each Track has one Album, and each Album has one Genre.

每个专辑可以有任意数量的曲目，每个曲目有一个专辑，每个专辑有一个流派。

I want to implement a keyword search that allows the user to input any number of keywords and find all Tracks that:

我想实现一个关键字搜索，允许用户输入任意数量的关键字，并找到所有的轨迹:

have a matching title,
有一个匹配的标题,
are on an Album with a matching title or artist,
是在一个有匹配的标题或艺术家的相册，
or are on an Album with a Genre with a matching title.
或者是在一张同名的专辑中。

Results should be sorted by relevancy. It would be great if each field had a ranking for relevancy. For example, the title of a Track might be more important than the title of the Genre.

结果应该根据相关性进行排序。如果每个领域都有相关性排名，那就太好了。例如，一首歌曲的标题可能比流派的标题更重要。

Also, the solution should use some form of partial searching. A search of rubber should first match all Tracks with a title of Rubber, then match Tracks with a title matching *rubber* (*=wildcard), then move on to Albums, and so on. However, I'm not so set on these details. I'm just looking for a more general solution that I can tweak to match my specific needs.

此外，解决方案应该使用某种形式的部分搜索。搜索橡胶应该首先匹配所有带有橡胶标题的歌曲，然后匹配带有名称匹配*rubber*(*=通配符)的歌曲，然后继续到专辑，等等。然而，我对这些细节不是很确定。我只是在寻找一种更通用的解决方案，我可以根据自己的具体需求进行调整。

I should also mention that I'm using a LAMP stack, Linux, Apache, MySQL, and PHP.

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

What is the best way to implement this keyword search?

实现这个关键字搜索的最佳方式是什么?

Update: I've been trying to implement this via a full text search, and have come up with the following SQL statements.

更新:我一直在尝试通过全文搜索实现这一点，并提出了以下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');

3 个解决方案

#1

I would use Apache Solr. Use the Data Import Handler to define an SQL query that joins all your tables together, create a fulltext index from the result of joined data.

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

The columns named as args to MATCH() must be the column(s) you defined for the index, in the same order you defined in the index. But you can't define any index (fulltext or otherwise) across multiple tables in MySQL.

为匹配()而命名为args的列必须是为索引定义的列，其顺序与在索引中定义的顺序相同。但是在MySQL中，不能在多个表中定义任何索引(全文或其他)。

So you can't do this:

所以你不能这样做:

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

It doesn't matter whether you're using boolean mode or natural language mode.

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

You need to do this:

你需要这样做:

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

You may also be interested in my presentation Practical Full-Text Search in MySQL.

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

#2

Define a fulltext index on the four columns you like to search and then do:

在你喜欢搜索的四列中定义一个全文索引，然后做:

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');

The resullt will be sorted by relevancy. See here for more details on fulltext search: http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

结果将根据相关性进行排序。详情请参阅全文搜索:http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

#3

I would use something like Sphinx, u can make an index out of your query and then query that. It's a little difficult to get your head around but the results are 10 times better than mysql AGAINST and you won't have problems later on with speed.

我会用Sphinx之类的东西，你可以用你的查询创建一个索引，然后再查询。这有点难理解，但是结果比mysql好10倍，以后的速度也不会有问题。

#1