跨多个表的MySQL关键字搜索 [英] MySQL Keyword Search Across Multiple Tables

问题描述

我在音乐库应用程式中使用的MySQL资料库中有三个表格：

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

Genre 栏：

• id


• code> title （string）

• id
• title (string)

c $ c>表包含列：

The Album table has columns:

• id


• genre_id （外键 Genre.id ）




• 艺术家（字符串）

• id
• genre_id (foreign key to Genre.id)
• title (string)
• artist (string)

且跟踪表有以下列：

• id


• album_id （外键 Album.id ）


• title / li>
  

• id
• album_id (foreign key to Album.id)
• title (string)

每个相簿可以有任何数量 / code>，每个跟踪有一个相册，每个 code>有一个类型。

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:

• 有标题，


• 位于相簿与 title 或 artist ，


• 与标题的类别 li>


• 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.

结果应按相关性排序。如果每个字段都有相关性的排名，这将是巨大的。例如， Track 的 title 可能比 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.

此外，解决方案应该使用某种形式的部分搜索。搜索橡胶应首先匹配所有轨道与标题 Rubber ，然后用 title 匹配 Tracks * rubber * （ * =通配符），然后移至相册，等等。然而，我不是这样设置这些细节。我只是寻找一个更通用的解决方案，我可以调整，以匹配我的具体需求。

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.

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

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

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

更新： 通过全文搜索，并提出了以下SQL语句。

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

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

推荐答案

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

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.

作为MATCH（）的args指定的列必须是列的数据。您为索引定义，以您在索引中定义的顺序相同。但你不能在MySQL的多个表中定义任何索引（全文或其他）。

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.

所以你不能这样做：

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.

您需要这样做：

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

---

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

这篇关于跨多个表的MySQL关键字搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！