MySQL全文本搜索

启用全文本搜索支持

create table productnotes
(
	note_id int NOT NULL AUTO_INCREMENT,
	prod_id char(10) NOTNULL,
	note_date datetime NOT NULL,
	note_text text NULL,
	FULLTEXT(note_text)
)ENGINE = MyISAM;

进行全文本搜索：match()指定搜索列，against()指定搜索的文本

select note_text 
from productnotes 
where match(note_text) against('rabbit');

或者

select note_text 
from productnotes 
where note_text like '%rabbit%';

对结果进行排序

select note_text 
where match(note_text) against('rabbit') as rank
from productnotes;

使用查询扩展

select note_text 
from productnotes 
where match(note_text) against('anvils' with query expansion);

布尔文本搜索
匹配词heavy，排除包含rope*

select note_text 
from productnotes 
where match(note_text) against('heavy -rope*' in boolean mode);

匹配包含rabbit和bait的行

select note_text 
from productnotes 
where match(note_text) against('+rabbit +bait' in boolean mode);

匹配包含rabbit或bait的行

select note_text 
from productnotes 
where match(note_text) against('rabbit bait' in boolean mode);

匹配短语rabbit bait

select note_text 
from productnotes 
where match(note_text) against('"rabbit bait"' in boolean mode);

匹配rabbit和bait，增加前者等级，降低后者等级

select note_text 
from productnotes 
where match(note_text) against('>rabbit <bait' in boolean mode);