У меня есть три таблицы в базе данных MySQL, используемой в музыкальном приложении библиотеки:
Genre
таблица имеет столбцы:
id
title
(строка) Album
таблица имеет столбцы:
id
genre_id
(внешний ключ к Genre.id
)title
(строка)artist
(строка)и Track
таблица имеет столбцы:
id
album_id
(внешний ключ к Album.id
)title
(строка)Каждый Album
может иметь любое количество Tracks
, каждый Track
имеет тот Album
, и каждый Album
имеет тот Genre
.
Я хочу реализовать поиск по ключевым словам, который позволяет пользователю вводить любое количество ключевых слов и находить все Tracks
это:
title
,Album
с соответствием title
или artist
,Album
с a Genre
с соответствием title
.Результаты должны быть отсортированы по уместности. Было бы замечательно, если бы каждое поле имело рейтинг для уместности. Например, title
из a Track
могло бы быть более важным, чем title
из Genre
.
Кроме того, решение должно использовать некоторую форму частичного поиска. Поиск rubber
должен сначала соответствовать всем Tracks
с a title
из Rubber
, затем соответствие Tracks
с a title
соответствие *rubber*
(*
=wildcard), затем идите дальше к Albums
, и так далее. Однако я не так установлен в этих деталях. Я просто ищу более общее решение, которое я могу настроить для соответствия моим определенным потребностям.
Я должен также упомянуть, что использую стек 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');
Я бы использовал Apache Solr . Используйте обработчик импорта данных , чтобы определить SQL-запрос, который объединяет все ваши таблицы вместе, создайте полнотекстовый индекс на основе результата объединенных данных.
Столбцы, названные как args для MATCH (), должны быть столбцами, которые вы определили для индекса, в том же порядке, который вы определили в индексе. Но вы не можете определить какой-либо индекс (полнотекстовый или другой) для нескольких таблиц в 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 .
Определите полнотекстовый индекс для четырех столбцов, которые вы хотите искать, а затем выполните:
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
Я бы использовал что-то вроде Sphinx, вы можете сделать индекс из вашего запроса, а затем запросить его. Это немного сложно понять, но результаты в 10 раз лучше, чем mysql AGAINST, и у вас не будет проблем со скоростью позже.