mysql+两个表匹配,MYSQL匹配查询两个表

Is it possible to do a match against a query with two tables using a join? The tricky part might be the index on the table but maybe there is a way.. sql is not my strong suit. Many thanks. I imagine it might be something like the following:

SELECT * FROM 'pages' p

LEFT JOIN `tags` t

ON p.id = u.pageid

WHERE MATCH(p.shdescript,t.tag) AGAINST ('romance, relationship')

Many thanks

解决方案

It's possible, but you need to have text indexes.

mysql> alter table pages add fulltext index_text(shdescript);

mysql> alter table tags add fulltext index_text(tag);

SELECT * FROM 'pages' p

LEFT JOIN `tags` t

ON p.id = u.pageid

WHERE MATCH(p.shdescript,t.tag) AGAINST ('romance relationship')

I guess that's enough to work.

EDIT:

As of MySQL 5.6 the above fulltext search can be done on the MyISAM & InnoDB storage engines. On earlier MySQL versions only MyISAM tables supported fulltext indexes.