在本教程中,您将通过使用MATCH()
和AGAINST()
函数来了解MySQL自然语言全文搜索。
MySQL自然语言全文搜索简介
在自然语言全文搜索中,MySQL查找与自由文本自然人类语言查询相关的行或文档,例如“如何使用MySQL自然语言全文搜索”。
相关性是一个正浮点数。 当相关性为零时,这意味着没有相似性。MySQL根据各种因素计算相关性,包括文档中的字数,文档中的唯一字数,集合中的单词总数以及包含特定单词的文档数(行)。
要执行自然语言全文搜索,您可以使用MATCH()
和AGAINST()
函数。 MATCH()
函数指定要搜索的列,AGAINST()
函数确定要使用的搜索表达式。
MySQL自然语言全文搜索示例
我们将使用示例数据库(yiibaidb)中的products
表进行演示。
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
列中启用全文搜索:
ALTER TABLE products
ADD FULLTEXT(productline);
其次,可以搜索产品系列包含Classic
的产品,使用MATCH()
和AGAINST()
函数,如下查询:
SELECT productName, productline
FROM products
WHERE MATCH(productline) AGAINST('Classic');
执行上面查询语句,得到以下结果 -
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
用于布尔文本搜索。
可以在查询中显式使用IN NATURAL LANGUAGE MODE
搜索修饰符,如下所示:
SELECT productName, productline
FROM products
WHERE MATCH(productline)
AGAINST('Classic,Vintage' IN NATURAL LANGUAGE MODE);
执行上面查询语句,得到以下结果 -
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子句中使用MATCH()
函数时,MySQL返回首先更相关的行。
以下示例显示了MySQL如何根据相关性对结果集进行排序。
首先,可以为products
表的productName
列启用全文搜索功能。
ALTER TABLE products
ADD FULLTEXT(productName);
其次,使用以下查询搜索名称包Ford
和/或1932
的产品:
SELECT productName, productline
FROM products
WHERE MATCH(productName) AGAINST('1932,Ford');
我们来查看输出结果:
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中执行自然语言搜索。