编程

MySQL全文检索

321 2023-04-08 23:45:00

MySQL支持使用LIKE运算符和正则表达式进行文本搜索 。但是，当text列很大并且表中的行数增加时，使用这些方法有一些限制：

  • 性能：MySQL必须扫描整个表，以根据LIKE  正则表达式中的语句或模式中的模式查找确切的文本。
  • 灵活的搜索：通过LIKE运算符和正则表达式搜索，很难有灵活的搜索查询，例如，查找描述中包含car但不包含的产品classic.
  • 相关性排名：无法指定结果集中哪一行与搜索项更相关。

由于这些限制，MySQL扩展了一个非常好的功能，即所谓的全文搜索。从技术上讲，MySQL根据启用的全文搜索列的单词创建索引，并对此索引执行搜索。MySQL使用复杂的算法来确定与搜索查询匹配的行。

以下是MySQL全文搜索的一些重要功能：

  • SQL-LIKE接口：您使用SQL-LIKE语句来使用全文搜索。
  • 完全动态索引：只要列的数据发生更改，MySQL就会自动更新文本列的索引。
  • 中等索引大小：存储索引不需要太多内存。
  • 最后但并非最不重要的是，基于复杂的搜索查询搜索速度很快。
  • 注意：并非所有存储引擎都支持全文搜索功能。在MySQL 5.6或更高版本中，只有MyISAM和InnoDB存储引擎支持全文搜索。

在表的列中执行全文搜索之前，必须将其数据编入索引。只要列的数据发生变化，MySQL就会重新创建全文索引。在MySQL中，全文索引是一种具有名称FULLTEXT的索引。

MySQL支持 为启用全文搜索的列自动索引和重新索引数据  。MySQL的5.6或更高版本允许你定义一个全文索引的数据类型为一列CHAR，VARCHAR或TEXT 在MyISAM中或InnoDB的表型。请注意，自5.6版以来，MySQL支持InnoDB表中的全文索引。

MySQL允许您在为现有表创建表或ALTER TABLE或CREATE INDEX语句时使用CREATE TABLE语句定义FULLTEXT索引。

使用CREATE TABLE语句定义FULLTEXT索引

通常，FULLTEXT使用以下CREATE TABLE语句创建新表时，可以为列定义索引：

CREATE TABLE table_name(
 column1 data_type, 
        column2 data_type,
        column3 data_type,
 …
PRIMARY_KEY(key_column),
FULLTEXT (column1,column2,..)
);

要创建FULLTEXT索引，请在FULLTEXT关键字后面的括号中放置逗号分隔列的列表。

以下语句创建一个名为posts具有FULLTEXT包含 post_content列的索引的新表。

CREATE TABLE posts (
  id int(4) NOT NULL AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  post_content text,
  PRIMARY KEY (id),
  FULLTEXT KEY post_content (post_content)
);

为现有表定义FULLTEXT索引

如果您已有现有表并想要定义全文索引，则可以使用ALTER TABLE语句或CREATE INDEX语句。

使用ALTER TABLE语句定义FULLTEXT索引

以下语法FULLTEXT使用以下语句定义索引ALTER TABLE：

ALTER TABLE table_name  
ADD FULLTEXT(column_name1, column_name2,…)

在此语法中，您将使用table_name 和ADD FULLTEXT子句定义FULLTEXT一个或多个列的索引。

例如，您可以FULLTEXT为示例数据库的表中的productDescription和productLine列定义索引，如下所示：products

ALTER TABLE products  
ADD FULLTEXT(productDescription,productLine);

使用CREATE INDEX语句定义FULLTEXT索引

您还可以使用  CREATE INDEX语句为现有表创建 FULLTEXT索引。请参阅以下语法：

CREATE FULLTEXT INDEX index_name
ON table_name(idx_column_name,...)

以下语句FULLTEXT为表的列addressLine1和addressLine2列创建索引offices。

CREATE FULLTEXT INDEX address
ON offices(addressLine1,addressLine2);

请注意，对于具有多行的表，将数据加载到FULLTEXT首先没有索引的表中然后创建FULLTEXT索引比将大量数据加载到具有现有FULLTEXT索引的表中更快。

删除全文搜索列

要删除FULLTEXT索引，只需使用ALTER TABLE … DROP INDEX语句删除索引即可。例如，以下语句删除表中的索引：address FULLTEXT offices

ALTER TABLE offices
DROP INDEX address;

MySQL自然语言全文搜索简介

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

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

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

MySQL自然语言全文搜索示例

我们将使用示例数据库中的products表进行演示。

+--------------------+
| products           |
+--------------------+
| productCode        |
| productName        |
| productLine        |
| productScale       |
| productVendor      |
| productDescription |
| quantityInStock    |
| buyPrice           |
| MSRP               |
+--------------------+
9 rows in set (0.00 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'); 
+-------------------------------------+--------------+
| 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 |
...

要搜索其产品系列包含Classic或Vintage 术语的产品，您可以执行以下查询：

SELECT productName, productline
FROM products
WHERE MATCH(productline) AGAINST('Classic,Vintage');
 +-------------------------------------------+--------------+
| 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 |
| 1932 Model A Ford J-Coupe                 | Vintage Cars |
| 1928 Mercedes-Benz SSK                    | Vintage Cars |
| 1913 Ford Model T Speedster               | Vintage Cars |
| 1934 Ford V8 Coupe                        | Vintage Cars |
| 18th Century Vintage Horse Carriage       | Vintage Cars |
| 1903 Ford Model A                         | Vintage Cars |
| 1917 Maxwell Touring Car                  | Vintage Cars |
| 1941 Chevrolet Special Deluxe Cabriolet   | Vintage Cars |
| 1932 Alfa Romeo 8C2300 Spider Sport       | Vintage Cars |
| 1904 Buick Runabout                       | Vintage Cars |
| 1939 Cadillac Limousine                   | Vintage Cars |
| 1939 Chevrolet Deluxe Coupe               | Vintage Cars |
| 1938 Cadillac V-16 Presidential Limousine | Vintage Cars |
| 1912 Ford Model T Delivery Wagon          | Vintage Cars |
| 1937 Horch 930V Limousine                 | Vintage Cars |
| 1940 Ford Delivery Sedan                  | Vintage Cars |
| 1936 Mercedes Benz 500k Roadster          | Vintage Cars |
| 1936 Chrysler Airflow                     | Vintage Cars |
| 1928 Ford Phaeton Deluxe                  | Vintage Cars |
| 1930 Buick Marquette Phaeton              | Vintage Cars |
| 1952 Alpine Renault 1300                  | Classic Cars |
| 1972 Alfa Romeo GTA                       | Classic Cars |
...

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以不区分大小写的方式执行搜索。但是，您可以指示MySQL使用二进制排序规则对索引列执行区分大小写的搜索。

按相关性对结果集进行排序

全文搜索的一个非常重要的特性是MySQL如何根据结果集的相关性对结果集中的行进行排名。MATCH()  在WHERE子句中使用函数时  ，MySQL首先返回更相关的行。

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

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

ALTER TABLE products 
ADD FULLTEXT(productName);

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

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 (0.00 sec)

产品其名称中包含两个1932  和  Ford先返回，然后他们的名字中包含的不仅是产品  Ford关键字。

使用全文搜索时，您应记住以下几点：

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

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

 

数据库 MySQL
上一篇
下一篇