mysql全文搜索错误 [英] mysql full text search error

问题描述

我尝试将全文搜索添加到现有表格。当我尝试时：

  alter table tweets添加全文索引（标签）; 
  

我收到错误消息：

 错误1214（HY000）：使用的表类型不支持FULLTEXT索引
  

有什么问题？我怎样才能知道它是什么样的表类型？

解决方案

这是您如何检查表类型：

  SELECT table_schema，engine FROM information_schema.tables WHERE table_name ='tweet'; 
  

只有 MyISAM 支持 FULLTEXT Indexes 。

您可能还想抢先停用词列表。

点击此处查看 FullText的停用词索引通常会忽略。

您可以将其重写为：

1）像这样在 / var / lib / mysql 中创建一个文本文件

  echoa> /var/lib/mysql/stopwords.txt<BR> 
回声an>> /var/lib/mysql/stopwords.txt<BR> 
回声the>> /var/lib/mysql/stopwords.txt<BR> 
  

2）将其添加到 /etc/my.cnf $ b

  ft_stopword_file = / var / lib / mysql / stopwords.txt< BR> 
 ft_min_word_len = 2 
  



3）服务mysql重启



以下是需要考虑的其他内容：

您可能不想将表格tweets转换为 MyISAM InnoDB 表'tweets'包含 CONSTRAINT 。

（s）。

<2>如果 InnoDB 表'tweets'是其他 InnoDB 表的外部关键字约束返回给'tweets'。

3）您不能拥有请注意，每个 INSERT 放入'tweets'表中将会将'tweets'表中的每个

如果它是 MyISAM 表，则触发表级锁。由于它现在是一个 InnoDB 表（它可以进行行级锁定），因此'tweets'表可以将 INSERTed 转换为很多人想要创建一个单独的 MyISAM 表，名为 tweets_tags / code>，其中'tweets'表的相同主关键字和 TEXT 列被称为'tags'，与'tweets'表中的相同。

接下来，执行tweets_tags的初始加载，如下所示：

  INSERT INTO tweets_tags（id，tags）SELECT id，tags FROM tweets; 
  

然后，定期（每晚或每6小时）将新推文加载到tweets_tags中，如下所示：

  INSERT INTO tweets_tags（id，tags）SELECT id，tags FROM tweets WHERE id> （SELECT max（id）FROM tweets_tags）; 
  

I try to add full text search to an existing table. When I tried:

alter table tweets add fulltext index(tags);

I got the error:

ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

what is the problem? How can I know what table type it is?

解决方案

This is how you check the table type:

SELECT table_schema,engine FROM information_schema.tables WHERE table_name='tweet';

Only MyISAM supports FULLTEXT Indexes.

You may also want to preempt the stopword list.

Click Here for the Stop Words that FullText Indexing Would Normally Ignore.

You can override this as Follows:

1) Create a text file in /var/lib/mysql like this

echo "a" > /var/lib/mysql/stopwords.txt<BR>
echo "an" >> /var/lib/mysql/stopwords.txt<BR>
echo "the" >> /var/lib/mysql/stopwords.txt<BR>

2) Add this to /etc/my.cnf

ft_stopword_file=/var/lib/mysql/stopwords.txt<BR>
ft_min_word_len=2

3) service mysql restart

Here is something else to consider:

You may not want to convert the table 'tweets' to MyISAM.

1) If the InnoDB table 'tweets' contains CONSTRAINT(s).

2) If the InnoDB table 'tweets' is the parent of other InnoDB tables with Foreign Key Constraints back to 'tweets'.

3) You cannot afford to have table-level locking of the 'tweets' table.

Remember, each INSERT into the 'tweets' table will trigger a table-level lock if it were a MyISAM table. Since it currently an InnoDB table (which does row-level locking), the 'tweets' table can be INSERTed into very quickly.

You many want to create a separate MyISAM table, called tweets_tags, with the same Primary Key of the 'tweets' table along with a TEXT column called 'tags' the same as in the 'tweets' table.

Next, do an initial load of tweets_tags like this:

INSERT INTO tweets_tags (id,tags) SELECT id,tags FROM tweets;

Then, periodically (every night or every 6 hours), load new tweets into tweets_tags like this :

INSERT INTO tweets_tags (id,tags) SELECT id,tags FROM tweets WHERE id > (SELECT max(id) FROM tweets_tags);

这篇关于mysql全文搜索错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！