mysql全文搜索错误
我尝试将全文搜索添加到现有表中。当我尝试时:
alter table tweets add fulltext index(tags);
我收到错误:
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
问题是什么?我如何知道它是什么表类型?
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
如果您想使用全文索引,您需要确保表的底层引擎是 MyISAM。您可以使用 ALTER TABLE tweets ENGINE = MYISAM; 来更改此设置
If you want to use full text indexing you need to make sure your table's underlying engine is MyISAM. You can change this using
ALTER TABLE tweets ENGINE = MYISAM;
这是检查表类型的方法:
只有
MyISAM
支持FULLTEXT 索引
。您可能还想抢占停用词列表。
单击此处查看
全文索引 通常会忽略。
您可以按如下方式覆盖它:
1) 在
/var/lib/mysql
中创建一个文本文件,如下所示2) 将其添加到
/etc/my .cnf
3) service mysql restart
这里还有一些需要考虑的事情:
您可能不想将表 'tweets' 转换为
MyISAM
。1) 如果
InnoDB
表“tweets”包含CONSTRAINT(s)
。2) 如果
InnoDB
表 'tweets' 是其他InnoDB
表的父级,并且外键约束返回到 'tweets'。3) 您无法承受“tweets”表的表级锁定。
请记住,如果“tweets”表是一个
MyISAM
表,则每次INSERT
都会触发表级锁定。由于它当前是一个InnoDB
表(执行行级锁定),因此可以非常快速地INSERTed
到“tweets”表中。您可能希望创建一个名为
tweets_tags
的单独的MyISAM
表,该表具有与“tweets”表相同的主键以及TEXT
列称为“标签”,与“推文”表中的名称相同。接下来,像这样初始加载 tweets_tags:
然后,定期(每晚或每 6 小时)将新推文加载到 tweets_tags 中,如下所示:
This is how you check the table type:
Only
MyISAM
supportsFULLTEXT 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 this2) Add this to
/etc/my.cnf
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' containsCONSTRAINT(s)
.2) If the
InnoDB
table 'tweets' is the parent of otherInnoDB
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 aMyISAM
table. Since it currently anInnoDB
table (which does row-level locking), the 'tweets' table can beINSERTed
into very quickly.You many want to create a separate
MyISAM
table, calledtweets_tags
, with the same Primary Key of the 'tweets' table along with aTEXT
column called 'tags' the same as in the 'tweets' table.Next, do an initial load of tweets_tags like this:
Then, periodically (every night or every 6 hours), load new tweets into tweets_tags like this :