使用MySQL全文索引

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_textWHERE 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_textWHERE MATCH(title,description)AGAINST('Frisbee' IN NATURAL LANGUAGE MODE) LIMIT 2\G

输出信息

*************************** 1. row ***************************    film_id: 308      title: FERRIS MOTHERdescription: A Touching Display of a Frisbee And a Frisbee who must Kill a Girlin The Gulf of Mexico*************************** 2. row ***************************    film_id: 326      title: FLYING HOOKdescription: A Thrilling Display of a Mad Cow And a Dog who must Challenge a Frisbee in Nigeria2 rows in set (0.00 sec)

从这个输出结果，我发现了搜索词Frisbee和AGAINST中的description的关系：description中含有关键词Frisbee。但是，关键词和title有什么关系？

执行SQL

-- 自然语言模式SELECT film_id, title,description FROM film_textWHERE 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_textWHERE MATCH(title,description)AGAINST('FLYING HOOK' IN NATURAL LANGUAGE MODE) LIMIT 5\G

输出结果

*************************** 1. row ***************************    film_id: 326      title: FLYING HOOKdescription: A Thrilling Display of a Mad Cow And a Dog who must Challenge a Frisbee in Nigeria*************************** 2. row ***************************    film_id: 430      title: HOOK CHARIOTSdescription: A Insightful Story of a Boy And a Dog who must Redeem a Boy in Australia*************************** 3. row ***************************    film_id: 540      title: LUCKY FLYINGdescription: A Lacklusture Character Study of a A Shark And a Man who must Finda Forensic Psychologist in A U-Boat3 rows in set (0.00 sec)

发现，不管是title还是description中包含有FLYING或HOOK中的一个的，都作为符合查询条件的结果提取出来。这是否说明，自然语言模式的全文索引，是在全文索引字段中的任意一个查询？当关键词用空格分开时，只要包含关键词的一部分就符合查询条件？

执行SQL语句

-- 自然语言模式SELECT film_id, title,description FROM film_textWHERE MATCH(title,description)AGAINST('FLYING' IN NATURAL LANGUAGE MODE) LIMIT 5\G

输出结果

*************************** 1. row ***************************    film_id: 326      title: FLYING HOOKdescription: A Thrilling Display of a Mad Cow And a Dog who must Challenge a Frisbee in Nigeria*************************** 2. row ***************************    film_id: 540      title: LUCKY FLYINGdescription: A Lacklusture Character Study of a A Shark And a Man who must Finda Forensic Psychologist in A U-Boat2 rows in set (0.00 sec)

与上面的输出结果相比，少了film_id为430的数据，因为关键词中没有HOOK。

在执行SQL语句

-- 自然语言模式SELECT film_id, title,description FROM film_textWHERE MATCH(title,description)AGAINST('FLYING And' IN NATURAL LANGUAGE MODE) LIMIT 5\G

输出结果

*************************** 1. row ***************************    film_id: 326      title: FLYING HOOKdescription: A Thrilling Display of a Mad Cow And a Dog who must Challenge a Frisbee in Nigeria*************************** 2. row ***************************    film_id: 540      title: LUCKY FLYINGdescription: A Lacklusture Character Study of a A Shark And a Man who must Finda Forensic Psychologist in A U-Boat2 rows in set (0.00 sec)

film_id为430的数据的description中含有And，但是却没有出现在查询结果中，这是因为And在超过50%的行中都存在吗？

二、布尔模式

执行SQL

-- 布尔模式SELECT film_id,title,description FROM film_textWHERE MATCH(title,description)AGAINST ('technical +writer' IN BOOLEAN MODE)LIMIT 5\G

输出结果

*************************** 1. row ***************************    film_id: 19      title: AMADEUS HOLYdescription: A Emotional Display of a Pioneer And a Technical Writer who must Battle a Man in A Baloon*************************** 2. row ***************************    film_id: 43      title: ATLANTIS CAUSEdescription: A Thrilling Yarn of a Feminist And a Hunter who must Fight a Technical Writer in A Shark Tank*************************** 3. row ***************************    film_id: 44      title: ATTACKS HATEdescription: 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 AGENTdescription: A Taut Saga of a Crocodile And a Boy who must Overcome a TechnicalWriter in Ancient China*************************** 5. row ***************************    film_id: 86      title: BOOGIE AMELIEdescription: A Lacklusture Character Study of a Husband And a Sumo Wrestler whomust Succumb a Technical Writer in The Gulf of Mexico5 rows in set (0.00 sec)

执行SQL

-- 布尔模式SELECT film_id,title,description FROM film_textWHERE MATCH(title,description)AGAINST ('technical -writer' IN BOOLEAN MODE)LIMIT 6\G

输出

无数据

执行SQL

-- 布尔模式SELECT film_id,title,description FROM film_textWHERE MATCH(title,description)AGAINST ('"Fight a Pastry Chef"' IN BOOLEAN MODE)LIMIT 5\G

输出

*************************** 1. row ***************************    film_id: 11      title: ALAMO VIDEOTAPEdescription: A Boring Epistle of a Butler And a Cat who must Fight a Pastry Chef in A MySQL Convention1 row in set (0.00 sec)