55.笔记 MySQL学习——自然语言FULLTEXT搜索

55.笔记 MySQL学习——自然语言FULLTEXT搜索

用AGAINST()指定搜索字符串，对表进行自然语言类型的全文搜索，如下：

mysql> select * from apothegm wherematch(attribution) against ('roosevelt');

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

| attribution        | phrase                             |

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

| Theodore Roosevelt | Speak softly andcarry a big stick |

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

1 row in set (0.00 sec)

mysql> select * from apothegm where match(phrase)against ('time');

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

| attribution       | phrase                                    |

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

| Benjamin Franklin | Remember that time ismoney               |

| Aeschylus         | Time as he grows old teaches manylessons |

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

2 rows in set (0.00 sec)

mysql> select * from apothegm wherematch(attribution,phrase) against ('bell');

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

| attribution           | phrase                             |

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

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

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

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

2 rows in set (0.00 sec)

自然搜索能够将包含所有搜索单词的那些行找出来，如下：

mysql> select * from apothegm where match(phrase)against('hard soft');

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

| attribution         | phrase                                              |

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

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

| Proverbs 15:1       | A soft answer turneth away wrath                    |

| William Shakespeare | But, soft! whatlight through yonder window breaks? |

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

3 rows in set (0.00 sec)

自然语言默认是全文搜索模式。如果想要显示地指定这个模式，可以再字符串后面加上IN NATURAL LANGUAGE MODE。

mysql> select * from apothegm where  match(phrase) against('hard soft' in naturallanguage mode);

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

| attribution         | phrase                                              |

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

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

| Proverbs 15:1       | A soft answer turneth away wrath                    |

| William Shakespeare | But, soft! whatlight through yonder window breaks? |

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

3 rows in set (0.00 sec)