innoDB 表的 MySQL 全文搜索解决方法 [英] MySQL Full-text Search Workaround for innoDB tables

问题描述

我正在设计一个使用 MySQL 作为其后端数据库的内部 Web 应用程序.数据的完整性至关重要，因此我使用 innoDB 引擎来实现其外键约束功能.

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.

我想对一种类型的记录进行全文搜索，而 innoDB 表本身并不支持这种类型的记录.我不愿意转向 MyISAM 表，因为它们缺乏外键支持，而且它们的锁定是按表而不是按行进行的.

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.

创建我需要使用 MyISAM 引擎搜索的记录的镜像表并将其用于全文搜索是否是不好的做法?通过这种方式，我只是搜索数据的副本，如果该数据发生任何问题，也没什么大不了的，因为它总是可以重新创建的.

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?

谢谢.

推荐答案

您也许能够使用触发器进行某种数据同步(如果您的 mysql 版本支持它们).它们允许您在某些点运行 SQL 的小片段，例如在表中插入或删除数据之后.

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.

例如...

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

... 每当数据插入到 INNODB 表时，相同的数据就会自动插入到 MYISAM 表中.

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

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