任何在 InnoDB 上实现类全文搜索的方法

2021-11-20 00:00:00 sql full-text-search mysql

我有一个非常简单的查询:

I have a very simple query:

SELECT ... WHERE row LIKE '%some%' OR row LIKE '%search%' OR  row LIKE '%string%'

搜索一些搜索字符串，但是如你所见，它单独搜索每个字符串，这对性能也不利.

to search for some search string, but as you can see, it searches for each string individually and it's also not good for performance.

有没有办法在 InnoDB 表上使用 LIKE 重新创建类似全文的搜索.当然，我知道我可以使用 Sphinx 之类的东西来实现这一点，但我正在寻找纯 MySQL 解决方案.

Is there a way to recreate a fulltext-like search using LIKE on an InnoDB table. Of course, I know I can use something like Sphinx to achieve this but I'm looking for a pure MySQL solution.

推荐答案

使用 myisam 全文表索引回你的 innodb 表，例如:

use a myisam fulltext table to index back into your innodb tables for example:

使用 innodb 构建您的系统:

Build your system using innodb:

create table users (...) engine=innodb;

create table forums (...) engine=innodb;

create table threads
(
forum_id smallint unsigned not null,
thread_id int unsigned not null default 0,
user_id int unsigned not null,
subject varchar(255) not null, -- gonna want to search this... !!
created_date datetime not null,
next_reply_id int unsigned not null default 0,
view_count int unsigned not null default 0,
primary key (forum_id, thread_id) -- composite clustered PK index
)
engine=innodb;

现在是全文搜索表，我们将使用它来索引回我们的 innodb 表.您可以使用触发器或每晚批量更新等方式维护该表中的行.

Now the fulltext search table which we will use just to index back into our innodb tables. You can maintain rows in this table either by using a trigger or nightly batch updates etc.

create table threads_ft
(
forum_id smallint unsigned not null,
thread_id int unsigned not null default 0,
subject varchar(255) not null,
fulltext (subject), -- fulltext index on subject
primary key (forum_id, thread_id) -- composite non-clustered index 
)
engine=myisam;

最后是您从 php/应用程序调用的搜索存储过程:

Finally the search stored procedure which you call from your php/application:

drop procedure if exists ft_search_threads;
delimiter #

create procedure ft_search_threads
(
in p_search varchar(255)
)
begin

select
 t.*,
 f.title as forum_title,
 u.username,
 match(tft.subject) against (p_search in boolean mode) as rank
from
 threads_ft tft
inner join threads t on tft.forum_id = t.forum_id and tft.thread_id = t.thread_id
inner join forums f on t.forum_id = f.forum_id
inner join users u on t.user_id = u.user_id
where
 match(tft.subject) against (p_search in boolean mode) 
order by 
 rank desc
limit 100;

end;

call ft_search_threads('+innodb +clustered +index');

希望这有帮助:)

相关文章