# 自然语言全文搜索 在本教程中，您将通过使用`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中执行自然语言搜索。 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://hezhiqiang-book.gitbook.io/mysql/di-liu-zhang/zi-ran-yu-yan-quan-wen-sou-suo.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.