一对多表上的mysql搜索查询

Question

我有两个这样的表（_video，_video_tag）。

我如何构建最佳搜索查询？

我想在这个字段中搜索（标题、描述、标签）

mysql> describe _video;
+-------------+---------------+------+-----+-------------------+----------------+
| Field       | Type          | Null | Key | Default           | Extra          |
+-------------+---------------+------+-----+-------------------+----------------+
| id          | int(11)       | NO   | PRI | NULL              | auto_increment |
| user_id     | int(11)       | NO   | MUL | NULL              |                |
| image       | varchar(255)  | NO   |     | NULL              |                |
| source      | varchar(255)  | YES  |     | NULL              |                |
| duration    | int(11)       | NO   |     | NULL              |                |
| title       | varchar(255)  | NO   |     | NULL              |                |
| slug        | varchar(255)  | NO   |     | NULL              |                |
| description | text          | NO   |     | NULL              |                |
| order       | int(11)       | NO   |     | 0                 |                |
| hit         | int(11)       | NO   |     | 1                 |                |
| status      | enum('0','1') | NO   |     | 1                 |                |
| date_add    | timestamp     | NO   |     | CURRENT_TIMESTAMP |                |
+-------------+---------------+------+-----+-------------------+----------------+

mysql> select * from _video_tag limit 5;
+----------+------------------------------------+
| video_id | tag                                |
+----------+------------------------------------+
|    17748 | cevahir almanca dersi              |
|    17748 | genis aile                         |
|    17748 | ulvi                               |
|    17748 | cevahir                            |
|    17749 | oyle bir geçer zamanki
+----------+------------------------------------+

Answer 1

如果您的表是 MyISAM 最好的可扩展方法是在 _video 表中的标题和描述上创建一个 FULLTEXT 索引：

CREATE FULLTEXT INDEX title_desc ON _video( title, description )

并在 _video_tag 表中的标签列上创建一个 FULLTEXT 索引，

CREATE FULLTEXT INDEX tag ON _video_tag( tag )

然后像这样查询：

( SELECT *,
  MATCH(title, description) AGAINT('some query string') AS relevance
  FROM _video
  WHERE MATCH(title, description) AGAINT('some query string') )
UNION
( SELECT v.*
  MATCH(vt.tag) AGAINST('some query string') AS relevance
  FROM _video_tag AS vt
  INNER JOIN _video AS v ON( v.id = vt.video_id )
  WHERE MATCH(vt.tag) AGAINST('some query string') )
ORDER BY relevance DESC

Answer 2

这取决于您想要获得什么，提供哪些信息。不过，我假设您希望从 _video_tag 中给定特定标签的 _video 中获取信息。然后，您

select v.* from _video_tag as vt left join _video as v on v.id = vt.video_id where vt.tag = 'Specify tag';