跨多个字段的文本搜索MySQL [英] Text Search across multiple fields MySQL

问题描述

我有一个这样布置的MySQL表:

I have a MySQL table laid out like this:

ID | Artist | Title

其中包含从MP3 ID3标签获得的歌曲信息.

Which contains song information obtained from MP3 ID3 tags.

现在，我希望能够对此进行搜索，但是传入的数据可能具有多种格式.我相信你们都知道ID3标签在几个人握住它们后会如何最终结束.

Now, I would like to be able to run a search on this, however the incoming data could be in a variety of formats. I'm sure you all know how ID3 tags can end up after a couple of people have had hold of them.

我尝试了以下查询，但是现在来看，我完全理解为什么它不带回结果集.我没什么主意.

I have the following query which I have tried, but looking at it now, I understand completely why it doesnt bring back a result set. I'm fresh out of ideas.

SELECT CONCAT(artist, ' ',title) as trackname 
FROM sound_tracklist 
WHERE CONCAT(artist,' ',title) LIKE '%[JunkTags]Artist - Title[More Junk - www.junkwebsite.com]%'

对此我将不胜感激.

谢谢

推荐答案

如果您位于MyISAM类型表中，请尝试使用FULLTEXT索引:

If you're in MyISAM type tables, try using a FULLTEXT index:

ALTER TABLE sound_tracklist ADD FULLTEXT INDEX (artist, title);

那你就可以做

SELECT artist, title FROM sound_tracklist WHERE MATCH (artist, title) AGAINST ('bach brandenburg concerto')

它不会捕获拼写错误("konshertoe"，有人吗?)，但是它将在索引的所有字段中搜索您指定的键盘，并以任意顺序捕获它们.

it wouldn't catch misspellings ("konshertoe", anyone?), but it would search for your specified keyboards across all the fields in the index, and catch them in any order.

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