如何在 PostgreSQL 数据库上执行全文搜索

介绍

PostgreSQL 全文搜索可让您以较高的准确度找到与某些关键字匹配的记录。此查询技术速度很快，因为它实现了索引、高级查询函数和规则。在生产环境中，您应该使用全文搜索来解决模式匹配搜索表达式带来的限制。

例如，在电子商务网站中，客户搜索Party Dress。模式匹配搜索算法无法返回部分匹配的记录，如Party Ready Dress。因此，模式匹配不准确且速度慢，因为它们不实现索引。PostgreSQL 全文搜索解决了这些问题。它提供了灵活性、准确性和速度来查找记录并根据相关性对结果进行排名。

为了实现这一点，PostgreSQL 使用了to_tsvector()和to_tsquery()函数。本指南将引导您完成在 PostgreSQL 数据库中实现全文搜索的过程。

先决条件

要遵循本指南，您需要：

1. 设置示例数据库

按照以下步骤设置示例数据库，创建表并插入一些测试记录。

以非 root sudo用户身份通过 SSH 访问您的 Linux 服务器。
以postgres用户身份登录 PostgreSQL 服务器。
```
 $ sudo -u postgres psql
```
输入密码并按Enter继续。

创建一个示例电子商务数据库。

 postgres=# CREATE DATABASE e_commerce;

输出。

 CREATE DATABASE

切换到新的电子商务数据库。

 postgres=# \connect e_commerce;

输出。

 You are now connected to database "e_commerce" as user "postgres".

创建产品表。此表存储产品的详细信息，包括唯一的product_id、product_name和retail_price。在PRIMARY KEY列上使用SERIAL关键字，以允许 PostgreSQL 服务器为新记录**自动**分配新的product_id 。

 e_commerce=# CREATE TABLE products (
                  product_id SERIAL PRIMARY KEY,
                  product_name VARCHAR (50),
                  retail_price NUMERIC(5,2)  
              );

输出。

 CREATE TABLE

将示例数据插入产品表。

 e_commerce=# INSERT INTO products(product_name, retail_price) VALUES ('BUTTERFLY DECORATIONS', 35.20);
              INSERT INTO products(product_name, retail_price) VALUES ('DELL WIRELESS MOUSE', 25.50);
              INSERT INTO products(product_name, retail_price) VALUES ('RIB MEAT', 4.89);
              INSERT INTO products(product_name, retail_price) VALUES ('LOGITECH WIRELESS MOUSE', 16.50);
              INSERT INTO products(product_name, retail_price) VALUES ('WIRELESS USB DONGLE FOR DELL', 4.50);
              INSERT INTO products(product_name, retail_price) VALUES ('FRUITWORLD WATERMELON', 3.25);
              INSERT INTO products(product_name, retail_price) VALUES ('HP WIRELESS MOUSE', 25.50);
              INSERT INTO products(product_name, retail_price) VALUES ('RIBENA JUICE', 7.70);
              INSERT INTO products(product_name, retail_price) VALUES ('DELL WIRELESS KEYBOARD', 25.50);

输出。

 ...
 INSERT 0 1

通过针对产品表运行**SELECT**语句来确保示例数据到位。

 e_commerce=# SELECT 
                  product_id,
                  product_name, 
                  retail_price
              FROM products;

输出。

  product_id |         product_name         | retail_price
 ------------+------------------------------+--------------
           1 | BUTTERFLY DECORATIONS        |        35.20
           2 | DELL WIRELESS MOUSE          |        25.50
           3 | RIB MEAT                     |         4.89
           4 | LOGITECH WIRELESS MOUSE      |        16.50
           5 | WIRELESS USB DONGLE FOR DELL |         4.50
           6 | FRUITWORLD WATERMELON        |         3.25
           7 | HP WIRELESS MOUSE            |        25.50
           8 | RIBENA JUICE                 |         7.70
           9 | DELL WIRELESS KEYBOARD       |        25.50
 (9 rows)

2. to_tsvector()和to_tsquery()函数的基本语法

PostgreSQL 全文搜索的强大之处在于to_tsvector()和to_tsquery()函数。以下是每个函数工作原理的总结。

2.1. to_tsvector()函数

此函数调用内部 PostgreSQL 解析器将文档或列值分解为单个单词（标记）。该函数进一步将标记简化为词素（基本单词），因此，像coming这样的单词会转换为come，而mangoes会转换为mango。最后，to_tsvector()函数返回一个tsvector列表，其中包含所有基本单词及其位置，以及**a和the** stripped等停用词。

对短语“大男孩们在他们的父亲接受道歉后来吃芒果”运行to_tsvector()函数，看看它是如何工作的。
```
 e_commerce=# SELECT to_tsvector('The big boys are coming to eat the mangoes after their fathers accepted their apologies');
```
上述to_tsvector()命令输出下面的tsvector列表。现在所有单词都已提取为其基本词干，并且已排除停用词（如the、are、to和after）。
```
 'accept':13 'apolog':15 'big':2 'boy':3 'come':5 'eat':7 'father':12 'mango':9
 (1 row)
```

2.2. to_tsquery()函数

to_tsquery ()函数将关键字或关键短语转换为规范化的标记。换句话说，to_tsquery()函数将搜索词编译为 PostgreSQL 服务器在**tsvector**列表中定位文档/记录时可以理解的结构。

了解了这两个全文搜索功能的工作原理后，您现在可以在数据库中测试它们。

3.实现PostgreSQL全文搜索功能

在 PostgreSQL 数据库上执行全文搜索时，必须同时实现to_tsvector()和to_tsquery()函数以及匹配运算符@@。

当您对tsvector文档运行关键字搜索（tsquery ）时，匹配运算符将返回布尔值（t或f ）。

在实现全文功能之前，使用关键字rib对您的产品表进行常规搜索。
```
 e_commerce=# SELECT * FROM products WHERE product_name like '%RIB%';
```

下面的常规搜索输出显示两条记录：RIB MEAT和RIBENA JUICE。但是，上述搜索方法不够智能，无法知道RIB与**RIBENA JUICE**无关。

  product_id | product_name | retail_price
 ------------+--------------+--------------
           3 | RIB MEAT     |         4.89
           8 | RIBENA JUICE |         7.70
 (2 rows)

使用to_tsvector和to_tsquery()函数再次执行相同的搜索。

 e_commerce=# SELECT * FROM products WHERE to_tsvector(product_name) @@ to_tsquery('RIB');

从下面的输出中可以清楚地看出，全文搜索足够智能，知道RIBENA JUICE与**RIB不相关，因此只返回一条记录。那就是RIB MEAT**。
```
  product_id | product_name | retail_price
 ------------+--------------+--------------
           3 | RIB MEAT     |         4.89
 (1 row)
```

使用MOUSE关键字执行另一次全文搜索。

 e_commerce=# SELECT * FROM products WHERE to_tsvector(product_name) @@ to_tsquery('MOUSE');

上述查询返回三条记录，如下所示。

  product_id |      product_name       | retail_price
 ------------+-------------------------+--------------
           2 | DELL WIRELESS MOUSE     |        25.50
           4 | LOGITECH WIRELESS MOUSE |        16.50
           7 | HP WIRELESS MOUSE       |        25.50
 (3 rows) 

4. 使用 PostgreSQL 全文搜索运算符

为了在搜索大型记录集时提供更大的灵活性，PostgreSQL 数据库服务器支持不同的搜索运算符，如下所述。

4.1. &运算符

使用AND / &运算符仅返回包含所有由&分隔的关键字列表的记录/文档。

运行以下查询以返回所有DELL WIRELESS产品。

 e_commerce=# SELECT * FROM products WHERE to_tsvector(product_name) @@ to_tsquery('DELL & WIRELESS');

上述查询返回以下输出。

  product_id |         product_name         | retail_price
 ------------+------------------------------+--------------
           2 | DELL WIRELESS MOUSE          |        25.50
           5 | WIRELESS USB DONGLE FOR DELL |         4.50
           9 | DELL WIRELESS KEYBOARD       |        25.50
 (3 rows)

4.2. |操作符

使用OR / |运算符返回与一个或多个搜索关键字匹配的记录。

执行以下查询以返回LOGITECH和HP品牌的所有产品。

 e_commerce=# SELECT * FROM products WHERE to_tsvector(product_name) @@ to_tsquery('LOGITECH | HP');

上述查询返回以下结果。

  product_id |      product_name       | retail_price
 ------------+-------------------------+--------------
           4 | LOGITECH WIRELESS MOUSE |        16.50
           7 | HP WIRELESS MOUSE       |        25.50
 (2 rows)

4.3. !操作符

使用NOT / !运算符排除包含特定关键字的记录。

运行以下语句从搜索结果中排除所有DELL产品。

 e_commerce=# SELECT * FROM products WHERE to_tsvector(product_name) @@ to_tsquery('!DELL');

下面的输出显示所有不包括DELL产品的产品。

  product_id |      product_name       | retail_price
 ------------+-------------------------+--------------
           1 | BUTTERFLY DECORATIONS   |        35.20
           3 | RIB MEAT                |         4.89
           4 | LOGITECH WIRELESS MOUSE |        16.50
           6 | FRUITWORLD WATERMELON   |         3.25
           7 | HP WIRELESS MOUSE       |        25.50
           8 | RIBENA JUICE            |         7.70
 (6 rows)

4.4. ''操作符

使用双引号''运算符返回与精确搜索词匹配的记录。

运行以下命令搜索短语DELL WIRELESS KEYBOARD。

 e_commerce=# SELECT * FROM products WHERE to_tsvector(product_name) @@ to_tsquery('''DELL WIRELESS KEYBOARD''');

从下面的输出中，只有一条记录与DELL WIRELESS KEYBOARD关键字完全匹配。

  product_id |      product_name      | retail_price
 ------------+------------------------+--------------
           9 | DELL WIRELESS KEYBOARD |        25.50
 (1 row)

4.5. 组合多个全文搜索运算符

您可以在单个查询中组合多个搜索运算符来执行更复杂的全文查询。

运行以下查询仅返回来自DELL或HP公司的**无线鼠标**产品。

 e_commerce=# SELECT * FROM products WHERE to_tsvector(product_name) @@ to_tsquery('(DELL | HP) & (WIRELESS & MOUSE)');

查询返回以下两种产品。

  product_id |    product_name     | retail_price
 ------------+---------------------+--------------
           2 | DELL WIRELESS MOUSE |        25.50
           7 | HP WIRELESS MOUSE   |        25.50
 (2 rows)

5. 使用预先计算的向量加速全文搜索

在 PostgreSQL 数据库中，全文函数运行速度很快，因为tsvector数据类型实现了索引。数据库服务器在搜索过程中执行的唯一困难任务是计算向量。为了在处理大型数据集时加快操作速度，一个好的做法是预先计算向量并将其与记录一起存储在单独的列中。

使用这种方法，除非您更新现有记录，否则数据库服务器只会计算一次向量。这大大加快了全文搜索操作的速度。按照以下步骤将预先计算的向量添加到您的产品表中。

向产品表添加TSVECTOR数据类型列，并将其命名为product_name_tokens。

 e_commerce=# ALTER TABLE products
              ADD COLUMN product_name_tokens TSVECTOR;

输出。

 ALTER TABLE

通过使用语句to_tsvector(p.product_name)转换产品名称来更新**产品**表中的记录以包含新的向量信息。

 e_commerce=# UPDATE products p  
                 SET product_name_tokens = to_tsvector(p.product_name)  
              FROM products p2;

输出。

 UPDATE 9

查询产品表以确保您已成功计算向量。

 e_commerce=# SELECT 
                  product_id,
                  product_name, 
                  retail_price,
                  product_name_tokens
              FROM products;

以下输出显示了记录以及预先计算的向量。按Q继续。

  product_id |         product_name         | retail_price |           product_name_tokens
 ------------+------------------------------+--------------+-----------------------------------------
           1 | BUTTERFLY DECORATIONS        |        35.20 | 'butterfli':1 'decor':2
           2 | DELL WIRELESS MOUSE          |        25.50 | 'dell':1 'mous':3 'wireless':2
           3 | RIB MEAT                     |         4.89 | 'meat':2 'rib':1
           4 | LOGITECH WIRELESS MOUSE      |        16.50 | 'logitech':1 'mous':3 'wireless':2
           5 | WIRELESS USB DONGLE FOR DELL |         4.50 | 'dell':5 'dongl':3 'usb':2 'wireless':1
           6 | FRUITWORLD WATERMELON        |         3.25 | 'fruitworld':1 'watermelon':2
           7 | HP WIRELESS MOUSE            |        25.50 | 'hp':1 'mous':3 'wireless':2
           8 | RIBENA JUICE                 |         7.70 | 'juic':2 'ribena':1
           9 | DELL WIRELESS KEYBOARD       |        25.50 | 'dell':1 'keyboard':3 'wireless':2
 (9 rows)

针对TSVECTOR数据类型列 ( product_name_tokens )运行查询以搜索关键字RIBENA。

 e_commerce=# SELECT 
                  product_id,
                  product_name, 
                  retail_price                         
              FROM products
              WHERE product_name_tokens @@ to_tsquery('RIBENA'); 

从下面的输出可以看出，您的查询按预期工作，而无需在搜索操作期间计算向量。

 product_id | product_name | retail_price
 ------------+--------------+--------------
           8 | RIBENA JUICE |         7.70
 (1 row)

结论

在本指南中，您已使用 PostgreSQL 内置函数在示例数据库中实现全文搜索。您还可以使用基本的全文搜索运算符，如&、|和!。最后，您学习了如何在处理大型数据集时通过预先计算向量来节省时间。使用本指南中的知识为您的下一个 PostgreSQL 搜索引擎项目提供支持。

介绍​

先决条件​

1. 设置示例数据库​

2. to_tsvector()和to_tsquery()函数的基本语法​

2.1. to_tsvector()函数​

2.2. to_tsquery()函数​

3.实现PostgreSQL全文搜索功能​

4. 使用 PostgreSQL 全文搜索运算符​

4.1. &运算符​

4.2. |操作符​

4.3. !操作符​

4.4. ''操作符​

4.5. 组合多个全文搜索运算符​

5. 使用预先计算的向量加速全文搜索​

结论​

介绍