如何在多列上创建FULLTEXT索引？ [英] How to create FULLTEXT index on multiple columns?

问题描述

我在 tbl_query上运行以下查询

  select * from tbl_query q其中match（q.query_desc，q.query_desc_details）against（'test1'WITH QUERY EXPANSION）; 
  

出现错误

  16:46:22 select * from tbl_query q其中match（q.query_desc，q.query_desc_details）against（'test1'WITH QUERY EXPANSION）LIMIT 0，1000错误代码：1191.找不到FULLTEXT索引匹配列列表0.078秒
  

我的表如下

CREATE TABLE`tbl_query`（
`query_id` int（11）NOT NULL AUTO_INCREMENT，
`query_desc` text NOT NULL，
`query_desc_details` text，
PRIMARY KEY（`query_id`），
KEY`QUERY_DESC`（`query_desc`（333））使用BTREE，
KEY`QUERY_DESC_DETAILS`（`query_desc_details` （333））使用BTREE
）ENGINE = MyISAM AUTO_INCREMENT = 5 DEFAULT CHARSET = utf8;


在数据库中，全文字边界类似于

  ft_max_word_len = 84 
 ft_min_word_len = 4 
  

我正在搜索两个列。

所以我的问题是如何创建表的全文索引。

解决方案

2 的完整文本

  ALTER TABLE tbl_query 
 ADD FULLTEXT INDEX`FullText` 
（`query_desc` ASC，
`query_desc_details` ASC）; 
  

I am running the following query on tbl_query

select * from tbl_query q where match(q.query_desc,q.query_desc_details) against ('test1' WITH QUERY EXPANSION);

It's giving an error

16:46:22    select * from tbl_query q where match(q.query_desc,q.query_desc_details) against ('test1' WITH QUERY EXPANSION) LIMIT 0, 1000   Error Code: 1191. Can't find FULLTEXT index matching the column list    0.078 sec  

My table is like this

 CREATE TABLE `tbl_query` (
  `query_id` int(11) NOT NULL AUTO_INCREMENT,
  `query_desc` text NOT NULL,
  `query_desc_details` text,
  PRIMARY KEY (`query_id`),
  KEY `QUERY_DESC` (`query_desc`(333)) USING BTREE,
  KEY `QUERY_DESC_DETAILS` (`query_desc_details`(333)) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

In database full text words boundaries are like

ft_max_word_len=    84
ft_min_word_len=    4  

I am searching against two column.
So my question is how to create the full text index for the table?

解决方案

Fulltext with 2 columns you create like this

ALTER TABLE tbl_query
ADD FULLTEXT INDEX `FullText` 
(`query_desc` ASC, 
 `query_desc_details` ASC);

这篇关于如何在多列上创建FULLTEXT索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！