MySQL 全文索引(fulltext)

CREATE TABLE articles (
 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 title VARCHAR(200),
 body TEXT,
 FULLTEXT (title,body)
)engine=MyIsam charset=utf8;

INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

--使用全文索引
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');

--查询匹配概率
select match(title,body) against('database') from articles;

注意事项

1 在mysql中fulltext只针对myisam引擎生效.

2 mysql自己提供的fulltext针对英文有效-->sphinx(coreseek)技术处理中文.

3 使用方式是match(字段名......) against(‘关键字’),select * from articles where body like ‘%mysql%’不会使用全文索引;

4 停止词：因为在一个文本中,创建索引是一个无穷大的数，因此,对一些常用词和字符,就不会创建索引,这些词称为停止词.