MySQL的全文索引,不复杂,要掌握它,我只需要掌握三点:
1.创建全文索引的语法。
2.查询时的语法
MATCH() AGAINST ()
3.MySQL全文索引的缺陷。
不必花时间去记这些语法,用到时去查看准确的笔记就可以了。
另外,全文索引里的match()against()的具体使用的含义,我并不清楚,这需要在实践中去弄明白。不在研究这个问题了。
参考资料:
MySQL全文检索笔记 http://www.cnblogs.com/feichexia/archive/2012/06/09/2543049.html
我的测试过程。
一、下载sakila数据库
1.下载地址:http://downloads.mysql.com/docs/sakila-db.zip
2.解压后,执行SQL语句
SOURCE yourpath sakila-schema.sql;
SOURCE yourpath sakila-data.sql;
二、使用MySQL全文索引
1.自然语言模式
SQL语句
-- 自然语言模式
SELECT film_id, title FROM film_text
WHERE MATCH(title,description)
AGAINST('Frisbee' IN NATURAL LANGUAGE MODE)
LIMIT 6;
+---------+-------------------+
| film_id | title |
+---------+-------------------+
| 308 | FERRIS MOTHER |
| 326 | FLYING HOOK |
| 585 | MOB DUFFEL |
| 714 | RANDOM GO |
| 210 | DARKO DORADO |
| 112 | CALENDAR GUNFIGHT |
+---------+-------------------+
6 rows in set (0.02 sec)
我没有从上述结果中看出该结果和Frisbee有什么关系。
再执行SQL语句
-- 自然语言模式
SELECT film_id, title,description FROM film_text
WHERE MATCH(title,description)
AGAINST('Frisbee' IN NATURAL LANGUAGE MODE)
LIMIT 2\G
输出信息
*************************** 1. row ***************************
film_id: 308
title: FERRIS MOTHER
description: A Touching Display of a Frisbee And a Frisbee who must Kill a Girl
in The Gulf of Mexico
*************************** 2. row ***************************
film_id: 326
title: FLYING HOOK
description: A Thrilling Display of a Mad Cow And a Dog who must Challenge a Fri
sbee in Nigeria
2 rows in set (0.00 sec)
从这个输出结果,我发现了搜索词Frisbee和AGAINST中的description的关系:description中含有关键词Frisbee。但是,关键词和title有什么关系?
执行SQL
-- 自然语言模式
SELECT film_id, title,description FROM film_text
WHERE MATCH(title)
AGAINST('Frisbee' IN NATURAL LANGUAGE MODE)
LIMIT 2\G
输出信息
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
这说明,Match必须包含所有的fulltext index。
执行SQL
-- 自然语言模式
SELECT film_id, title,description FROM film_text
WHERE MATCH(title,description)
AGAINST('FLYING HOOK' IN NATURAL LANGUAGE MODE)
LIMIT 5\G
输出结果
*************************** 1. row ***************************
film_id: 326
title: FLYING HOOK
description: A Thrilling Display of a Mad Cow And a Dog who must Challenge a Fri
sbee in Nigeria
*************************** 2. row ***************************
film_id: 430
title: HOOK CHARIOTS
description: A Insightful Story of a Boy And a Dog who must Redeem a Boy in Aust
ralia
*************************** 3. row ***************************
film_id: 540
title: LUCKY FLYING
description: A Lacklusture Character Study of a A Shark And a Man who must Find
a Forensic Psychologist in A U-Boat
3 rows in set (0.00 sec)
执行SQL语句
-- 自然语言模式
SELECT film_id, title,description FROM film_text
WHERE MATCH(title,description)
AGAINST('FLYING' IN NATURAL LANGUAGE MODE)
LIMIT 5\G
输出结果
*************************** 1. row ***************************
film_id: 326
title: FLYING HOOK
description: A Thrilling Display of a Mad Cow And a Dog who must Challenge a Fri
sbee in Nigeria
*************************** 2. row ***************************
film_id: 540
title: LUCKY FLYING
description: A Lacklusture Character Study of a A Shark And a Man who must Find
a Forensic Psychologist in A U-Boat
2 rows in set (0.00 sec)
与上面的输出结果相比,少了film_id为430的数据,因为关键词中没有HOOK。
在执行SQL语句
-- 自然语言模式
SELECT film_id, title,description FROM film_text
WHERE MATCH(title,description)
AGAINST('FLYING And' IN NATURAL LANGUAGE MODE)
LIMIT 5\G
输出结果
*************************** 1. row ***************************
film_id: 326
title: FLYING HOOK
description: A Thrilling Display of a Mad Cow And a Dog who must Challenge a Fri
sbee in Nigeria
*************************** 2. row ***************************
film_id: 540
title: LUCKY FLYING
description: A Lacklusture Character Study of a A Shark And a Man who must Find
a Forensic Psychologist in A U-Boat
2 rows in set (0.00 sec)
二、布尔模式
执行SQL
-- 布尔模式
SELECT film_id,title,description FROM film_text
WHERE MATCH(title,description)
AGAINST ('technical +writer' IN BOOLEAN MODE)
LIMIT 5\G
输出结果
*************************** 1. row ***************************
film_id: 19
title: AMADEUS HOLY
description: A Emotional Display of a Pioneer And a Technical Writer who must Ba
ttle a Man in A Baloon
*************************** 2. row ***************************
film_id: 43
title: ATLANTIS CAUSE
description: A Thrilling Yarn of a Feminist And a Hunter who must Fight a Techni
cal Writer in A Shark Tank
*************************** 3. row ***************************
film_id: 44
title: ATTACKS HATE
description: A Fast-Paced Panorama of a Technical Writer And a Mad Scientist who
must Find a Feminist in An Abandoned Mine Shaft
*************************** 4. row ***************************
film_id: 67
title: BERETS AGENT
description: A Taut Saga of a Crocodile And a Boy who must Overcome a Technical
Writer in Ancient China
*************************** 5. row ***************************
film_id: 86
title: BOOGIE AMELIE
description: A Lacklusture Character Study of a Husband And a Sumo Wrestler who
must Succumb a Technical Writer in The Gulf of Mexico
5 rows in set (0.00 sec)
执行SQL
-- 布尔模式
SELECT film_id,title,description FROM film_text
WHERE MATCH(title,description)
AGAINST ('technical -writer' IN BOOLEAN MODE)
LIMIT 6\G
输出
无数据
执行SQL
-- 布尔模式
SELECT film_id,title,description FROM film_text
WHERE MATCH(title,description)
AGAINST ('"Fight a Pastry Chef"' IN BOOLEAN MODE)
LIMIT 5\G
输出
*************************** 1. row ***************************
film_id: 11
title: ALAMO VIDEOTAPE
description: A Boring Epistle of a Butler And a Cat who must Fight a Pastry Chef
in A MySQL Convention
1 row in set (0.00 sec)