Mysql 简明教程
MySQL - Query Expansion Full-Text Search
在 MySQL 等关系数据库中，全文搜索是一种用于检索可能与搜索关键字不完全匹配的结果集的技术。这种类型的搜索在用于搜索的关键字不与用户期望的结果匹配时非常有用。因此，这种搜索技术旨在关注于提高搜索相关性，以缩小搜索查询与搜索结果之间的准确性差距。因此，搜索结果按与搜索关键字相关性从最高到最低的顺序显示。
In relational databases like MySQL, Full-text search is a technique used to retrieve result-sets that might not perfectly match the search keyword. This type of search is useful in cases where the keywords used for searching do not match the results a user expects. So, this searching technique is designed to focus on increasing search relevance in order to reduce the accuracy gap between search queries and search results. Thus, search results are displayed in the order of highest to the lowest relevancy to the search keyword.
全文搜索使用三种类型的搜索模式：
There are three types of search modes used with Full-text search −
-
Natural Language Mode
-
Query Expansion Mode
-
Boolean Mode
Query Expansion Full-Text Search
搜索总是由用户使用他们掌握的有限知识进行的。因此，有这样的情况：当搜索关键字太短以执行适当的搜索时。盲目扩展搜索技术就在这种情况下发挥作用。
Search is always done by the user with the limited knowledge they possess. Thus, there are cases when the search keywords are way too short to conduct a proper search. This is where Blind Expansion Search technique comes into picture.
盲目扩展搜索也称为自动相关性反馈，用于基于与原始关键字密切相关的附加关键字来扩大搜索结果。它使用“WITH QUERY EXPANSION”搜索词组启用。
Blind Expansion Search, also known as Automatic Relevance Feedback, is used to widen the search results based on additional keywords that are closely related to the original keywords. It is enabled using the 'WITH QUERY EXPANSION' search phrase.
在以下情况下，按以下步骤执行两次搜索-
The search is performed twice in this cases by following the steps given below −
Step 1 −首先搜索所有与给定搜索关键字匹配的行。
Step 1 − All the rows that match the given search keyword are searched first.
Step 2 −然后检查获得的行中与原始关键字相关的相关单词。
Step 2 − These obtained rows are then checked for relevant words to the original keyword in them.
Step 3 −最后，再次基于这些相关单词（而不是用户指定的原始关键字）搜索这些行。
Step 3 − Finally, the rows are searched again based on these relevant words instead of the original keywords specified by the users.
要在数据库表的全文搜索中执行查询扩展，则必须在 AGAINST() 函数中指定带 QUERY EXPANSION 的 WITH 或带 QUERY EXPANSION 的 IN NATURAL LANGUAGE MODE 搜索修饰符。
To perform the query expansion full-text search on a database table, the WITH QUERY EXPANSION or IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION search modifiers must be specified in the AGAINST() function.
Example
让我们在以下示例中了解如何在数据库表中执行查询扩展全文搜索。
Let us understand how to perform Query Expansion Full-text Search on a database table in the following example.
为此，我们将首先创建一个名为 DBMS_TUTORIALS 的表，其中包含文章的标题和说明。全文索引应用于文本列 TUTORIAL_TITLE 和 DESCRIPTIONS，如下所示 −
For that, we will first create a table named DBMS_TUTORIALS containing the title and description of an article. The FULLTEXT index is applied on text columns TUTORIAL_TITLE and DESCRIPTIONS as shown below −
CREATE TABLE DBMS_TUTORIALS(
TUTORIAL_TITLE VARCHAR(200),
DESCRIPTIONS TEXT,
FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS)
);现在，让我们使用以下查询将教程的详细信息（例如其标题和说明）插入到此表中 −
Now, let us insert details about tutorials, like their titles and descriptions, into this table using the following queries −
INSERT INTO DBMS_TUTORIALS VALUES
('MySQL Tutorial', 'MySQL is an RDBMS that uses SQL to structure the data stored'),
('ORACLE Tutorial', 'ORACLE is an RDBMS that uses SQL to structure the data stored'),
('MySQL Security', 'MySQL Database can store sensitive data, so security is required'),
('MySQL vs MariaDB', 'Comparing two databases...'),
('JDBC Tutorial', 'In this Java-based database connectivity...');表创建如下 −
The table is created as −
在全文搜索中使用查询扩展模式，我们用关键字“RDBMS”搜索与数据相关的文章记录。
Using the Query Expansion Mode in full-text search, we search for records of articles relevant to data, with the keyword ‘RDBMS’
SELECT * FROM DBMS_TUTORIALS
WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS)
AGAINST ('RDBMS' WITH QUERY EXPANSION);IN NATURAL LANGUAGE MODE
在上面获得的结果集中，所有教程记录都与数据库有关，这就是查询基于相关性检索所有记录的原因。
In the result-set obtained above, all tutorial records are about databases, which is why the query retrieved all the records ordered based on relevance.
SELECT * FROM DBMS_TUTORIALS
WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS)
AGAINST ('Security' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);Output
获得的输出为 −
The output is obtained as −
在此结果集中，即使搜索关键字为“Security”，实际的安全相关教程也只有“MySQL Security”和“JDBC Tutorial”，因此首先检索这些教程。然后，这些记录后跟与数据库相关的记录，作为扩展查询。
In this result-set, even if the search keyword is 'Security', the actual security related tutorials are just 'MySQL Security' and 'JDBC Tutorial', so they are retrieved first. These records are then followed by database related records as an expanded query.