使用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_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)

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

执行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)

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

二、布尔模式

执行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)