sql – 在InnoDB上实现全文搜索的任何方法教程

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

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

搜索一些搜索字符串,但正如您所看到的,它会单独搜索每个字符串,但它对性能也不利.

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

解决方法:

使用myisam全文表来索引回你的innodb表,例如：

使用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表.您可以通过使用触发器或每晚批量更新等来维护此表中的行.

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 /应用程序调用的搜索存储过程：

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');

希望这可以帮助 ：)