create table articles (
id int unsigned auto_increment not null primary key,
title varchar(200),
content text,
fulltext (content)
) engine=InnoDB;
或者
create table articles (
id int unsigned auto_increment not null primary key,
title varchar(200),
content text,
) engine=InnoDB;
alter table articles add fulltext index_content(content) ;
导入数据:
insert into articles (title,content) values
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you ...'),
('Optimizing MySQL','In this tutorial we will ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
例1:
select * from articles where match (content) against ('database');
可以看到,语句查找到了包含指定内容的行。实际上,返回的行是按与所查找内容的相关度由高到低的顺序排列的。这个相关度的值由WHERE语句中的MATCH (…) AGAINST (…)计算所得,是一个非负浮点数。该值越大表明相应的行与所查找的内容越相关,0值表明不相关。该值基于行中的单词数、行中不重复的单词数、文本集合中总单词数以及含特定单词的行数计算得出。
例2: 由上例可知MATCH (…) AGAINST (…)实际上会计算一个相关值,可通过下例来验证。
select id, match(content) against ('database') as score from articles;
可以看到,所得结果的第二列即为改行与查找内容的相关度。例1中所得结果的顺序就是按此相关度排列的。
例3: 若想既看到查找到的结果又需要了解具体的相关度,可用下述方法达成。
select id, content, match (content) against ('database') as score from articles where match (content) against ('databse');