介绍
Full-text search,或FTS,是搜索引擎用来在数据库中找到结果的一种技术,您可以使用它来在商店,搜索引擎,报纸等网站上提供搜索结果。
更具体地说,FTS可以检索不完全匹配搜索标准的文档。 Documents 是包含文本数据的数据库实体,这意味着当用户搜索猫和狗时,例如,FTS 支持的应用程序可以返回包含单独单词的结果(只包含猫或狗),包含单词的不同顺序(狗和猫),或包含单词的变体(猫或狗)的结果。
从技术上讲,像MySQL这样的数据库管理系统(DBMS)通常允许使用LIKE条款进行部分文本搜索,然而,这些请求往往在大数据集上表现不佳。
使用FTS,您可以建立一个更强大的文本搜索引擎,而无需在更先进的工具上引入额外的依赖性. 在本教程中,您将使用MySQL 5.6来使用全文本搜索查询数据库,然后根据搜索输入的相关性量化结果,并显示只有最佳匹配。
前提条件
在开始本教程之前,您将需要:
- 一个 Ubuntu 16.04 服务器通过遵循本 初始服务器设置与 Ubuntu 16.04 指南设置,包括一个 sudo 非根用户和防火墙
- MySQL 5.6 或更高版本通过遵循 如何在 Ubuntu 16.04 安装 MySQL 指南安装
步骤 1 - 创建测试数据
在这个步骤中,我们将创建一个名为testdb的数据库,有一个名为新闻的表,我们将填充一些示例数据,代表来自虚构新闻集合网站的文章。
<$>[注] 注:如果您有自己的文本数据表,您可以跳过步骤 2并在跟进时进行适当的替代。
首先,访问 MySQL 控制台,您将被要求输入您在安装 MySQL 时设置的 root 密码。
1mysql -u root -p
一旦连接,您的提示将更改为mysql>。
接下来,创建一个名为testdb的新数据库,该数据库将包含测试数据。
1CREATE DATABASE testdb;
默认情况下,切换到使用testdb数据库,以便您无需指定数据库的名称来创建或更新其中的内容。
1USE testdb;
接下来,在数据库中创建一个名为新闻的表,其中包含示例新闻汇总器的文章的列。
1CREATE TABLE news (
2 id INT NOT NULL AUTO_INCREMENT,
3 title TEXT NOT NULL,
4 content TEXT NOT NULL,
5 author TEXT NOT NULL,
6
7 PRIMARY KEY (id)
8);
让我们通过这个命令做什么:
CREATE TABLE是一个 SQL 命令,可以创建一个表,类似于许多其他数据库news是表的名称title、content和author是无限长度的文本列NOT NULL是用于标记不能具有 null values的列(尽管它们可能包含空串)。
现在将一些示例数据添加到表中。
1INSERT INTO news (id, title, content, author) VALUES
2 (1, 'Pacific Northwest high-speed rail line', 'Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.', 'Greg'),
3 (2, 'Hitting the beach was voted the best part of life in the region', 'Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.', 'Ethan'),
4 (3, 'Machine Learning from scratch', 'Bare bones implementations of some of the foundational models and algorithms.', 'Jo');
让我们通过这个命令做什么:
INSERT输入数据.INTO指定了数据应该输入的位置. 在这种情况下,它是‘新闻’表- `(ID,标题,内容,作者)值’ 指定了每个条目的数据值应该存储的列
- 最后三行是我们在表中添加的三行数据。
每个条目都有一个独特的 identifier,自动输入到数据库索引中。 database index 是一个数据结构,可以改善数据检索操作的性能。 该索引与主要数据分开存储。 它更新了表内容的任何变化,以额外的字体和相对较小的存储空间。 其小尺寸和定制的数据结构允许索引比使用主表空间来选择查询更有效地运作。
现在我们有一些数据,我们可以开始写查询以使用FTS搜索这些数据。
步骤 2:创建 FTS 索引并使用 FTS 函数
让我们为我们所拥有的文本列创建索引,以便我们可以使用FTS。
要做到这一点,我们将使用一个名为FULLTEXT的MySQL独家命令,该命令告诉MySQL将我们希望能够使用FTS搜索的所有字段放入内部索引中。
1ALTER TABLE news ADD FULLTEXT (title, content, author);
这通过组合所有文本列并清理它们(例如,删除点缀并使上级字母下载)。
接下来,尝试使用函数 MATCH() AGAINST() 对西雅图海滩进行全文本搜索。
1SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('Seattle beach' IN NATURAL LANGUAGE MODE)\G
命令的 MATCH() 部分指定使用 FTS 索引哪些列组;它必须匹配您创建索引时使用的列表。
NATURAL LANGUAGE MODE意味着搜索单词直接从用户输入中提供,而无需任何预处理。
<$>[注] 注: 与自然语言模式相比, word stemming 是另一种有用的 FTS 技术,使索引带成为一个单词的贴纸,仅存储根部分。
遗憾的是,MySQL不支持词源。 Stemming是在MySQL的工作日志中,但还没有时间框架来实现和发布它。
美元
上面的查询末尾的\G使输出中的每个列打印在一个新的行上,这可以使长结果更容易阅读。
1[secondary_label Output]
2*************************** 1. row ***************************
3 id: 1
4 title: Pacific Northwest high-speed rail line
5content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
6 author: Greg
7*************************** 2. row ***************************
8 id: 2
9 title: Hitting the beach was voted the best part of life in the region
10content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
11 author: Ethan
122 rows in set (0.00 sec)
任何条目都没有包含西雅图海滩一词,但由于我们使用了全文本搜索,我们仍然得到两个结果:第一行只包含西雅图海滩一词,第二行只包含西雅图海滩一词。
现在,您可以在 SQL 查询中使用 FTS 函数来找到与搜索输入相关的行,您可以使这些结果更相关。
步骤3 - 精简FTS结果
有两种技术可以帮助使全文本搜索结果更相关,一个是通过结果的相关性分数进行过滤,另一个是使用IN BOOLEAN来排除结果中的特定单词,并指定搜索术语之间的最大距离。
使用相关性分数
结果的 relevance score 量化了它对搜索术语的匹配程度,其中 0 根本没有相关性. 相关性分数基于一系列因素,包括该术语在特定文档中被发现的频率以及包含该术语的文档数量。
根据前往公园查询获取每个行的相关性分数。
1SELECT id, MATCH (title,content,author) AGAINST ('traveling to parks') as score FROM news;
这个命令的as score部分将输出中的第二列标记为score。否则,它将被标记为用于填充命令,在这种情况下是MATCH(标题,内容,作者)反对(旅行到公园)。
结果将类似于此:
1[secondary_label Output]
2+----+----------------------+
3| id | score |
4+----+----------------------+
5| 1 | 0.031008131802082062 |
6| 2 | 0.25865283608436584 |
7| 2 | 0 |
8+----+----------------------+
93 rows in set (0.00 sec)
第三行有0的相关性分数,因为没有任何搜索术语出现在第三行. 第一行包含旅行,但没有到或公园,并且具有非常低的相关性分数为0.03。
您可以使用这些分数先返回最相关的结果,或者只返回超过特定相关性范围的结果。
以下命令运行相同的查询,但添加了两件事:
- 仅通过添加
WHERE MATCH (title,content,author) AGAINST(旅行到公园) > 0来显示非零相关性得分的行* 通过添加ORDER BY score DESC来对应结果
1SELECT id, MATCH (title,content,author) AGAINST ('traveling to parks') as score FROM news WHERE MATCH (title,content,author) AGAINST ('traveling to parks') > 0 ORDER BY score DESC;
您需要在WHERE条款中重复MATCH() AGAINST()函数,因为SQL对该条款中可能包含的内容存在限制。
结果将是这样的:
1[secondary_label Output]
2+----+----------------------+
3| id | score |
4+----+----------------------+
5| 2 | 0.25865283608436584 |
6| 1 | 0.031008131802082062 |
7+----+----------------------+
82 rows in set (0.01 sec)
最相关的结果,第 2 行,首先显示,其次是最不相关的 1 行,第 3 行根本不显示,因为其相关性分数为 0。
例如,如果您使用0.1而不是0作为切割,只会返回行2。
在波兰使用
在步骤 2 中,您在指定查询术语时使用了IN NATURAL LANGUAGE的默认模式,还有另一种模式,即IN BOOLEAN,允许您从搜索中排除特定单词,定义输入中的单词必须彼此距离的范围,等等。
若要在查询中省略一个术语,请使用含有IN BOOLEAN的减数运算符。 下面的命令将返回包含旅行字但不包含西雅图字的结果。
1SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('traveling -Seattle' IN BOOLEAN MODE)\G
结果只会显示行2:
1[secondary_label Output]
2*************************** 1. row ***************************
3 id: 2
4 title: Hitting the beach was voted the best part of life in the region
5content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
6 author: Ethan
71 row in set (0.01 sec)
这是因为 minus 运算符告诉 DMS 以 0 的相关性分数标记任何文档以排除的单词,此模式只显示非零相关性分数的结果。
您还可以使用「IN BOOLEAN MODE」来指定搜索术语之间的最大距离. 这个距离以单词计量,最重要的是包括搜索术语。
以下命令返回结果,其中旅行和英里单词之间不超过2个单词。
1SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('"traveling miles" @4' IN BOOLEAN MODE)\G
您将看到一个结果,在行 2 的内容中匹配旅行 140 英里。
1[secondary_label Output]
2*************************** 1. row ***************************
3 id: 1
4 title: Pacific Northwest high-speed rail line
5content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
6 author: Greg
71 row in set (0.00 sec)
如果您在原始命令中将@4更改为@3,则不会看到任何结果。
通过搜索术语之间的距离来限制搜索结果,在搜索具有多种词汇的非常大的文档时可能有帮助. 查询术语之间的差距越小,结果就越准确,虽然调整距离将取决于您正在处理的文档集。
结论
在本指南中,您使用了MySQL的全文本搜索功能,在为您的文档驱动的数据库构建数据库方案时创建了一个索引,然后使用特殊运算器在对其进行查询时找到最相关的结果。
如果您想进一步探索 MySQL 的 FTS 功能,您可以阅读 MySQL 5.6 关于全文搜索的官方文档。