从 Mysql 4.0 开始就支持全文索引功能
首先创建表
CREATE TABLE `fulltext_sample` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL DEFAULT '',
`keywords` varchar(100) NOT NULL DEFAULT '',
`content` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;
针对content列增加了全文索引,mysql的全文索引只对引擎为MyISAM的表才有效
向表中增加一些数据
INSERT INTO fulltext_sample(title,keywords,content) VALUES
('糖尿病如何防治?','糖尿病','It appears good from here,糖尿病'),
('心血管疾病患者如何做好自我防护','心血管','The here and the past,心血管疾病'),
('脑血管患者如何康复治疗','脑血管','Why are we hear,脑血管疾病'),
('有了皮肤病怎么办?','皮肤病','An all-out alert,皮肤病'),
('压力大亚健康如何调节?','亚健康','All you need is love,亚健康'),
('精神病患者的治疗','精神病','A good alert,精神病');
添加了数据之后需要用以下语句重新生成索引
repair table tablename quick 为有关数据表重新生成全文索引
如果需要修改索引列,可以先删除索引重新添加
alter table fulltext_sample drop index `content`;
//针对keywords增加全文索引
alter table fulltext_sample add fulltext index `fulltext_index` (`keywords`);
//全文索引查询语法
select * from fulltext_sample where id=1 and match(`keywords`) against('糖尿病');
//精确索引,上面这一条语句查不出来内容时可以试一下下面这一条语句,表示精确匹配
select * from fulltext_sample where id=1 and match(`keywords`) against('糖尿病' IN BOOLEAN MODE);
//使用全文索引与like查询的效率对比
mysql> explain select * from fulltext_sample where match(`keywords`) against('糖尿病');
+----+-------------+-----------------+----------+----------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+----------+----------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | fulltext_sample | fulltext | fulltext_index | fulltext_index | 0 | NULL | 1 | Using where |
+----+-------------+-----------------+----------+----------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from fulltext_sample where keywords like '%糖尿病%';
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | fulltext_sample | ALL | NULL | NULL | NULL | NULL | 54 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
可以看出like查询基本上是查询的所有记录,这在超过万条记录时,效率会相当糟糕的,而全文索引则不会有这个问题。
在my.cnf中设置为2个中文字符,一个词一般至少两个中文
ft_min_word_len=2