> For the complete documentation index, see [llms.txt](https://hezhiqiang-book.gitbook.io/mysql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hezhiqiang-book.gitbook.io/mysql/di-liu-zhang/zi-ran-yu-yan-quan-wen-sou-suo.md).

# 自然语言全文搜索

在本教程中，您将通过使用`MATCH()`和`AGAINST()`函数来了解MySQL自然语言全文搜索。

## MySQL自然语言全文搜索简介

在自然语言全文搜索中，MySQL查找与自由文本自然人类语言查询相关的行或文档，例如“如何使用MySQL自然语言全文搜索”。

相关性是一个正浮点数。 当相关性为零时，这意味着没有相似性。MySQL根据各种因素计算相关性，包括文档中的字数，文档中的唯一字数，集合中的单词总数以及包含特定单词的文档数(行)。

要执行自然语言全文搜索，您可以使用`MATCH()`和`AGAINST()`函数。 `MATCH()`函数指定要搜索的列，`AGAINST()`函数确定要使用的搜索表达式。

## MySQL自然语言全文搜索示例

我们将使用[示例数据库(yiibaidb)](http://www.yiibai.com/mysql/sample-database.html)中的`products`表进行演示。

```sql
mysql> desc products;
+--------------------+---------------+------+-----+---------+------------------+
| Field              | Type          | Null | Key | Default | Extra            |
+--------------------+---------------+------+-----+---------+------------------+
| productCode        | varchar(15)   | NO   | PRI |         |                  |
| productName        | varchar(70)   | NO   |     | NULL    |                  |
| productLine        | varchar(50)   | NO   | MUL | NULL    |                  |
| productScale       | varchar(10)   | NO   |     | NULL    |                  |
| productVendor      | varchar(50)   | NO   |     | NULL    |                  |
| productDescription | text          | NO   |     | NULL    |                  |
| quantityInStock    | smallint(6)   | NO   |     | NULL    |                  |
| buyPrice           | decimal(10,2) | NO   |     | NULL    |                  |
| MSRP               | decimal(10,2) | NO   |     | NULL    |                  |
| stockValue         | double        | YES  |     | NULL    | STORED GENERATED |
+--------------------+---------------+------+-----+---------+------------------+
10 rows in set (0.22 sec)
```

*首先*，需要使用`ALTER TABLE ADD FULLTEXT`语句在`products`表的`productLine`列中启用全文搜索：

```sql
ALTER TABLE products 
ADD FULLTEXT(productline);
```

*其次*，可以搜索产品系列包含`Classic`的产品，使用`MATCH()`和`AGAINST()`函数，如下查询：

```sql
SELECT productName, productline
FROM products
WHERE MATCH(productline) AGAINST('Classic');
```

执行上面查询语句，得到以下结果 -

```sql
mysql> SELECT productName, productline
FROM products
WHERE MATCH(productline) AGAINST('Classic');
+-------------------------------------+--------------+
| productName                         | productline  |
+-------------------------------------+--------------+
| 1952 Alpine Renault 1300            | Classic Cars |
| 1972 Alfa Romeo GTA                 | Classic Cars |
| 1962 LanciaA Delta 16V              | Classic Cars |
| 1968 Ford Mustang                   | Classic Cars |
| 2001 Ferrari Enzo                   | Classic Cars |
| 1969 Corvair Monza                  | Classic Cars |
| 1968 Dodge Charger                  | Classic Cars |
| 1969 Ford Falcon                    | Classic Cars |
| 1970 Plymouth Hemi Cuda             | Classic Cars |
| 1969 Dodge Charger                  | Classic Cars |
| 1993 Mazda RX-7                     | Classic Cars |
| 1965 Aston Martin DB5               | Classic Cars |
| 1948 Porsche 356-A Roadster         | Classic Cars |
| 1995 Honda Civic                    | Classic Cars |
| 1998 Chrysler Plymouth Prowler      | Classic Cars |
| 1999 Indy 500 Monte Carlo SS        | Classic Cars |
| 1992 Ferrari 360 Spider red         | Classic Cars |
| 1985 Toyota Supra                   | Classic Cars |
| 1969 Dodge Super Bee                | Classic Cars |
| 1976 Ford Gran Torino               | Classic Cars |
| 1948 Porsche Type 356 Roadster      | Classic Cars |
| 1970 Triumph Spitfire               | Classic Cars |
| 1957 Corvette Convertible           | Classic Cars |
| 1957 Ford Thunderbird               | Classic Cars |
| 1970 Chevy Chevelle SS 454          | Classic Cars |
| 1970 Dodge Coronet                  | Classic Cars |
| 1966 Shelby Cobra 427 S/C           | Classic Cars |
| 1949 Jaguar XK 120                  | Classic Cars |
| 1958 Chevy Corvette Limited Edition | Classic Cars |
| 1952 Citroen-15CV                   | Classic Cars |
| 1982 Lamborghini Diablo             | Classic Cars |
| 1969 Chevrolet Camaro Z28           | Classic Cars |
| 1971 Alpine Renault 1600s           | Classic Cars |
| 2002 Chevy Corvette                 | Classic Cars |
| 1956 Porsche 356A Coupe             | Classic Cars |
| 1992 Porsche Cayenne Turbo Silver   | Classic Cars |
| 1961 Chevrolet Impala               | Classic Cars |
| 1982 Camaro Z28                     | Classic Cars |
+-------------------------------------+--------------+
38 rows in set
```

`AGAINST()`函数默认使用`IN NATURAL LANGUAGE MODE`搜索修饰符，因此您可以在查询中省略它。还有其他搜索修饰符，例如`IN BOOLEAN MODE`用于[布尔文本搜索](http://www.yiibai.com/mysql/boolean-text-searches.html)。

可以在查询中显式使用`IN NATURAL LANGUAGE MODE`搜索修饰符，如下所示：

```sql
SELECT productName, productline
FROM products
WHERE MATCH(productline) 
AGAINST('Classic,Vintage' IN NATURAL LANGUAGE MODE);
```

执行上面查询语句，得到以下结果 -

```sql
mysql> SELECT productName, productline
FROM products
WHERE MATCH(productline) 
AGAINST('Classic,Vintage' IN NATURAL LANGUAGE MODE);
+-------------------------------------------+--------------+
| productName                               | productline  |
+-------------------------------------------+--------------+
| 1937 Lincoln Berline                      | Vintage Cars |
| 1936 Mercedes-Benz 500K Special Roadster  | Vintage Cars |
| 1917 Grand Touring Sedan                  | Vintage Cars |
| 1911 Ford Town Car                        | Vintage Cars |
************ 此处省略了一大波数据 **************************
| 1971 Alpine Renault 1600s                 | Classic Cars |
| 2002 Chevy Corvette                       | Classic Cars |
| 1956 Porsche 356A Coupe                   | Classic Cars |
| 1992 Porsche Cayenne Turbo Silver         | Classic Cars |
| 1961 Chevrolet Impala                     | Classic Cars |
| 1982 Camaro Z28                           | Classic Cars |
+-------------------------------------------+--------------+
62 rows in set
```

默认情况下，MySQL以不区分大小写的方式执行搜索。但是，您可以指示MySQL使用二进制排序规则对索引列进行区分大小写搜索。

**按相关性排序结果集**

全文搜索的一个非常重要的特征是MySQL根据其相关性对结果集中的行进行排序。 当[WHERE](http://www.yiibai.com/mysql/where.html)子句中使用`MATCH()`函数时，MySQL返回首先更相关的行。

以下示例显示了MySQL如何根据相关性对结果集进行排序。

*首先*，可以为`products`表的`productName`列启用全文搜索功能。

```sql
ALTER TABLE products 
ADD FULLTEXT(productName);
```

*其次*，使用以下查询搜索名称包`Ford`和/或`1932`的产品：

```sql
SELECT productName, productline
FROM products
WHERE MATCH(productName) AGAINST('1932,Ford');
```

我们来查看输出结果：

```sql
mysql> SELECT productName, productline
FROM products
WHERE MATCH(productName) AGAINST('1932,Ford');
+-------------------------------------+------------------+
| productName                         | productline      |
+-------------------------------------+------------------+
| 1932 Model A Ford J-Coupe           | Vintage Cars     |
| 1932 Alfa Romeo 8C2300 Spider Sport | Vintage Cars     |
| 1968 Ford Mustang                   | Classic Cars     |
| 1969 Ford Falcon                    | Classic Cars     |
| 1940 Ford Pickup Truck              | Trucks and Buses |
| 1911 Ford Town Car                  | Vintage Cars     |
| 1926 Ford Fire Engine               | Trucks and Buses |
| 1913 Ford Model T Speedster         | Vintage Cars     |
| 1934 Ford V8 Coupe                  | Vintage Cars     |
| 1903 Ford Model A                   | Vintage Cars     |
| 1976 Ford Gran Torino               | Classic Cars     |
| 1940s Ford truck                    | Trucks and Buses |
| 1957 Ford Thunderbird               | Classic Cars     |
| 1912 Ford Model T Delivery Wagon    | Vintage Cars     |
| 1940 Ford Delivery Sedan            | Vintage Cars     |
| 1928 Ford Phaeton Deluxe            | Vintage Cars     |
+-------------------------------------+------------------+
16 rows in set
```

首先返回其名称包含`1932`和`Ford`的产品，然后返回名称包含唯一`Ford`关键字的产品。

使用全文搜索时，应该记住一些重点：

* MySQL全文搜索引擎中定义的搜索项的最小长度为`4`，这意味着如果搜索长度小于`4`的关键字，例如`car`，`cat`等，则不会得到任何结果。
* 停止词被忽略，MySQL定义了MySQL源代码分发`storage/myisam/ft_static.c`中的停止词列表。

在本教程中，向您展示了如何使用`MATCH()`和`AGAINST()`函数在MySQL中执行自然语言搜索。