MySQL搜索多个表

Question

I'm in the midsts of constructing some database tables, but a possible search issue has just come to mind.

The two tables in question are Genres, a 2 column table holding a list of music genres identified by an ID field, i.e. 1 = Dance, 2 = Rock, and so on. And a Music table, a multi column table with Title, Artist, and Genre_ID fields. And yes you've guest it, Genre_ID refers to the ID of the Genre table.

My question is, if I have a search box on the site powered by PHP, and that search box queries the key fields, so Title, Artist, and Genre to yeld the best result, how can I get that to function correctly in a search, when the Genre name itself is in a separate table, and not in the Music table.

An example search would be, "rock music by ACDC".

Answer 1

Create a view where you join both of the tables. Then use SELECT with LIKE in WHERE clause or better use a fulltext search to do the searching job.

The view

create view ViewMusicWithGenre as
select "*"
from Music as m
left join Genre as g on m.genre_id = g.id;

Search option with like

select "*"
from ViewMusicWithGenre
where Title like '%<what_you_search>%'
  or Artist like '%<what_you_search>%'
  or Genre like '%<what_you_search>%';

I wrote the asterisk in "" because I KNOW that you WILL NOT use an asterisk. Left join is there because you want the row even without specified genre (very likely).

The fulltext search

This usually depends on the database you use. This is for instance Microsoft SQL Server 2014:

Fulltext search - http://technet.microsoft.com/en-us/library/ms142571.aspx

Fulltext index - http://technet.microsoft.com/en-us/library/ms187317.aspx

Querying fulltext search - http://technet.microsoft.com/en-us/library/ms142583.aspx

EDIT: for MySQL database

MySQL does not support fulltext indeces on views. So you are left with couple of choices:

• use the LIKE statement - could be ineffective, also more work later on
• create the fulltext index on Music table and omit the genre - not good enough
• create a new table that resembles the join and fill it on say daily basis with a job (or something like that) a do the fulltext search on that table - best solution in long terms, but more work to begin with and includes data duplicity

You also have to bear in mind that fulltext indeces only work on MyISAM storage engine.

The create statement for the joint table

create table fulltextSearchTable (
    Music_ID int not null primary key,
    Music_Title varchar(1024) not null,
    Music_Artist varchar(1024) not null,
    Genre_ID int not null,
    Genre_Title varchar(1024) not null,
    fulltext(Music_Title, Music_Artist, Genre_Title)
) engine=MyISAM;

The select with fulltext search

select "*"
from fulltextSearchTable
where match(Music_Title, Music_Artist, Genre_Title) against ('your_keyword');