启用全文本搜索支持
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);