mysql搜索标题，描述和多行标记 [英] mysql search title, description and multi rows tag

问题描述

入口表描述

  + ------------- + ------------------- + ------ + ----- +  - -------- + ---------------- + 
 |字段|类型|空| Key |默认|额外| 
 + ------------- + ------------------- + ------ + ---- -  + --------- + ---------------- + 
 | id | int（11）unsigned | NO | PRI | NULL | auto_increment | 
 |标题| varchar（255）|是| | NULL | | 
 | slug | varchar（255）|是| | NULL | | 
 |描述|文字|是| | NULL | | 
 | user_id | int（10）unsigned | NO | | NULL | | 
 |不安全|枚举（'0'，'1'）| NO | | NULL | | 
 |版权|枚举（'0'，'1'）| NO | | 0 | | 
 |状态|枚举（'0'，'1'，'2'）| NO | | 0 | | 
 | date_add | datetime | NO | | NULL | | 
 + ------------- + ------------------- + ------ + ---- -  + --------- + ---------------- + 
  

标签表格描述

  + ------------- + --------------------- + ------ + ------ + --------- + ---- ------------ + 
 |字段|类型|空| Key |默认|额外| 
 + ------------- + --------------------- + ------ +  - --- + --------- + ---------------- + 
 | id | bigint（20）unsigned | NO | PRI | NULL | auto_increment | 
 | relation_id | int（10）unsigned | NO | | NULL | | 
 |名称| varchar（255）| NO | | NULL | | 
 + ------------- + --------------------- + ------ +  - --- + --------- + ---------------- + 
  

我想在entry.title和tag.name中搜索。这个查询的性能如何？

我继续使用全文。你怎么样？

解决方案

  SELECT entries.id，entries.title 
 FROM条目
 INNER JOIN标记ON条目.id = tags.relation_id 
 WHERE tags.name IN（tag1，tag2，tag3）AND MATCH（entries.title，entries.description） AGAINST（'{text}'）
  

i have following tables.

the entry table describe

+-------------+-------------------+------+-----+---------+----------------+
| Field       | Type              | Null | Key | Default | Extra          |
+-------------+-------------------+------+-----+---------+----------------+
| id          | int(11) unsigned  | NO   | PRI | NULL    | auto_increment |
| title       | varchar(255)      | YES  |     | NULL    |                |
| slug        | varchar(255)      | YES  |     | NULL    |                |
| description | text              | YES  |     | NULL    |                |
| user_id     | int(10) unsigned  | NO   |     | NULL    |                |
| unsafe      | enum('0','1')     | NO   |     | NULL    |                |
| copyright   | enum('0','1')     | NO   |     | 0       |                |
| status      | enum('0','1','2') | NO   |     | 0       |                |
| date_add    | datetime          | NO   |     | NULL    |                |
+-------------+-------------------+------+-----+---------+----------------+

the tags table describe

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| relation_id | int(10) unsigned    | NO   |     | NULL    |                |
| name        | varchar(255)        | NO   |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

i want search in entry.title and tag.name. How can the performance for this query.

i continue usign fulltext. How do you ?

解决方案

SELECT entries.id, entries.title
FROM entries
INNER JOIN tags ON entries.id = tags.relation_id
WHERE tags.name IN ("tag1", "tag2", "tag3") AND MATCH(entries.title, entries.description) AGAINST ('{text}')

这篇关于mysql搜索标题，描述和多行标记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！