54.笔记 MySQL学习——使用FULLTEXT搜索

54.笔记 MySQL学习——使用FULLTEXT搜索

MYSQL具备全文搜索能力。可以在不使用模板匹配操作的情况下进行单词或短语的查找。

有3种类型：

l  自然语言搜索：把搜索字符串解析成一系列的单词，然后搜索出包含这些单词的那些行

l  布尔模式搜索：给定的单词必须出现或不出现在匹配行里，或者某些行必须恰好包含某个短语

l  查询扩展搜索：第一阶段是自然语言搜索。第二阶段把原来的搜索字符串与在第一阶段的搜索里高度匹配的那些行连接在一起然后再进行一次搜索。

使用全文搜索，需要创建FULLTEXT索引，然后使用MATCH运算符来查询它们。

使用FULLTEXT索引，要求必须使用MyISAM存储引擎。

建立数据文件

data.txt

如下：

Aeschylus Timeas he grows old teaches many lessons

Alexander Graham Bell        Mr. Watson, come here. I want you!

Benjamin Franklin       It is hard for an empty bag to stand upright

Benjamin Franklin       Little strokes fell great oaks

Benjamin Franklin       Remember that time is money

Miguel de Cervantes Bell, book, and candle

Proverbs 15:1    A soft answer turneth away wrath

Theodore Roosevelt   Speak softly and carry a big stick

William Shakespeare           But, soft! what light through yonder window breaks?

Robert Burton     I light my candle from their torches.

创建带FULLTEXT索引的表如下：

CREATE TABLE apothegm (attribution VARCHAR(40),phrase TEXT) ENGINE=MyISAM;

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLEapothegm;

ALTER TABLE apothegm

  ADD FULLTEXT(phrase),

  ADD FULLTEXT(attribution),

  ADD FULLTEXT(phrase, attribution);

执行如下：

mysql> ALTER TABLE apothegm

    ->   ADD FULLTEXT (phrase),

    ->   ADD FULLTEXT (attribution),

    ->   ADD FULLTEXT (phrase, attribution);

Query OK, 10 rows affected (0.00 sec)

Records: 10 Duplicates: 0  Warnings: 0

 

mysql> select * from apothegm;

+-----------------------+-----------------------------------------------------+

| attribution           | phrase                                              |

+-----------------------+-----------------------------------------------------+

| Aeschylus             | Time as he grows old teachesmany lessons           |

| Alexander Graham Bell | Mr. Watson, comehere. I want you!                  |

| Benjamin Franklin     | It is hard for an empty bag to standupright        |

| Benjamin Franklin     | Little strokes fell great oaks                      |

| Benjamin Franklin     | Remember that time is money                         |

| Miguel de Cervantes   | Bell, book, and candle                              |

| Proverbs 15:1         | A soft answer turneth awaywrath                    |

| Theodore Roosevelt    | Speak softly and carry a big stick                  |

| William Shakespeare   | But, soft! what light through yonderwindow breaks? |

| Robert Burton         | I light my candle from theirtorches.               |

+-----------------------+-----------------------------------------------------+

10 rows in set (0.00 sec)