SQL functions

原文地址：http://www.indepthinfo.com/sql-functions/match-against-function.php

MATCH() AGAINST() Function SQL

The MATCH()...AGAINST() syntax is used to search in FULLTEXT indexes

MATCH()...AGAINST()语法通常用在全文索引的搜索中。

An index is a way to perform searches more quickly through tables. In general, searches cannot be performed on large text files unless they have first been associated with a FULLTEXT index. The index is created in the CREATE TABLE statement or the ALTER TABLE statement.

添加索引是一种让数据表搜索得更快的一种方法。通常，搜索不能在很大的文本文件中进行，除非已经关联了一个FULLTEXT索引。这个索引是再create table 或 alter table时创建。

ALTER TABLE table ADD FULLTEXT(column1, column2, etc.)

To search the FULLTEXT index, the combination of MATCH()...AGAINST() is used. The MATCH portion defines the particular columns to be searched through their indexes, and the AGAINST() portion contains the keyword or keywords to be searched. Syntax:

当搜索FULLTEXT索引时，MATCH()...AGAINST()的组合就会被用到。Match部分定义了指定了要搜索的列，AGAINST部分指定了要搜索的关键字。

SELECT column(s) FROM table WHERE MATCH(column) AGAINST('string')

Results of this natural language query are listed in order of relevance. AGAINST() may contain a second parameter that defines the type of search to be made. IN BOOLEAN MODE allows specific items to be excluded from a search, for example:

自然语言模式的查询结果会在列表中列出。AGAINST()可能会包含第二个参数，用来指定搜索的模式。比如以下，在布尔模式下，允许将指定的项排除掉。

SELECT column1, column2, etc. FROM table WHERE MATCH (column(s)) AGAINST ('string -exclusion' IN BOOLEAN MODE)

Meanwhile WITH QUERY EXPANSION allows for a deeper search.

同时，使用WITH QUERY EXPANSION可以进行更深层次的搜索。

In general, FULLTEXT queries are not case sensitive. Minimum word length is four characters. Other common words such as "this", "that", and "other" are ignored. Words found in more than half the rows are also ignored. In mySQL the myISAM storage engine may need to be installed.

通常，FULLTEXT搜索不区分大小写。支持最小4个字节。其他像“this”、“that”、“and”等停词，因为在英文中大量出现但没有意义，将会被忽略。关键字长度超过两行的也会被忽略。