用于innoDB表的MySQL全文搜索解决方法

问题描述

我正在设计一个使用MySQL作为其后端数据库的内部Web应用程序。数据的完整性至关重要，所以我将 innoDB 引擎用于其外键约束功能。

我想对一种类型的记录进行全文搜索，并且本机不支持innoDB表。我不愿意移动到 MyISAM 表中，因为它们缺少外键支持，并且由于它们锁定在每个表中而不是每行。

使用MyISAM引擎创建需要搜索的记录的镜像表并将其用于全文搜索会是不好的做法吗？通过这种方式，我只需要搜索数据的副本，如果数据发生了任何变化，那么它就不是什么大事，因为它总是可以重新创建。

或者，这是一个尴尬的方式，应该避免这样做吗？

谢谢。

解决方案

您可以使用触发器进行某种数据同步（如果您的MySQL版本支持它们）。它们允许你在某些点上运行SQL的小片段，例如在数据插入到表中或从表中删除之后。

b
$ b

 插入INNODB_TABLE后创建触发器TRIGGER_NAME 
插入MYISAM_TABLE select * from INNODB_TABLE 
其中id = last_insert_id（）; 
  

...只要将数据插入到INNODB表中，相同的数据就会自动插入MYISAM表。

I'm designing an internal web application that uses MySQL as its backend database. The integrity of the data is crucial, so I am using the innoDB engine for its foreign key constraint features.

I want to do a full-text search of one type of records, and that is not supported natively with innoDB tables. I'm not willing to move to MyISAM tables due to their lack of foreign key support and due to the fact that their locking is per table, not per row.

Would it be bad practice to create a mirrored table of the records I need to search using the MyISAM engine and use that for the full-text search? This way I'm just searching a copy of the data and if anything happens to that data it's not as big of a deal because it can always be re-created.

Or is this an awkward way of doing this that should be avoided?

Thanks.

解决方案

You might be able to do some kind of data sync using triggers (if your version of mysql supports them). They allow you to run small snippets of SQL at certain points such as after data is inserted into or deleted from a table.

For example...

create trigger TRIGGER_NAME after insert on INNODB_TABLE
insert into MYISAM_TABLE select * from INNODB_TABLE
where id = last_insert_id();

... Whenever data is inserted into the INNODB table, the same data is automatically inserted into the MYISAM table.

这篇关于用于innoDB表的MySQL全文搜索解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！