Mysql 简明教程

MySQL Boolean Full-Text Search

MySQL 提供了全文搜索功能,支持三种类型的搜索,其中一种是布尔全文搜索。

The MySQL provides a full-text search functionality that supports three types of searches, one of which is the Boolean full-text search.

这个布尔全文搜索可以通过允许使用布尔运算符(例如 (+、-、>、<、* 等)和搜索字符串,对大量的文本数据执行复杂的搜索操作。

This Boolean full-text search enables complex search operations on large amounts of text data, by allowing the use of Boolean operators such as (+, -, >, <, *, etc.) and search strings.

与搜索 concepts 的自然语言全文搜索不同,MySQL 中的布尔全文搜索将查找 specific words 。要执行此类搜索,需要在 AGAINST 表达式中包含 IN BOOLEAN MODE 修饰符。

Unlike the natural language full-text search, which searches for concepts, the Boolean full-text search in MySQL looks for specific words. To perform this type of search, it is necessary to include the IN BOOLEAN MODE modifier in the AGAINST expression.

Syntax

以下是使用 MySQL 中的 AGAINST 表达式结合 IN BOOLEAN MODE 修饰符执行布尔全文搜索的语法:

Following is the syntax to perform a Boolean full-text search using the IN BOOLEAN MODE modifier with the AGAINST expression in MySQL −

SELECT column_name(s) FROM table_name
WHERE MATCH(target_column_names)
AGAINST(expression IN BOOLEAN MODE);

其中,

Where,

  1. The target_column_names are the names of the columns that we want to search the keyword in.

  2. The expression is the list of keywords with the Boolean operators.

MySQL Boolean Full-Text Search Operators

下表指定了全文搜索布尔运算符 −

The following table specifies the full-text search Boolean operators −

Example

首先,让我们使用以下查询创建一个名为 ARTICLES 的表 −

First of all, let us create a table with the name ARTICLES using the following query −

CREATE TABLE ARTICLES (
   ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   ARTICLE_TITLE VARCHAR(100),
   DESCRIPTION TEXT,
   FULLTEXT (ARTICLE_TITLE, DESCRIPTION)
);

在上面的查询中,我们在列 ARTICLE_TITLEDESCRIPTION 上定义了全文索引。现在,让我们将值插入到上面创建的表中 −

In the above query, we have defined full-text index on the columns ARTICLE_TITLE and DESCRIPTION. Now, let us insert values into the above-created table −

INSERT INTO ARTICLES (ARTICLE_TITLE, DESCRIPTION) VALUES
('MySQL Tutorial', 'MySQL is a relational database system that uses SQL to structure data stored'),
('Java Tutorial', 'Java is an object-oriented and platform-independent programming languag'),
('Hadoop Tutorial', 'Hadoop is framework that is used to process large sets of data'),
('Big Data Tutorial', 'Big Data refers to data that has wider variety of data sets in larger numbers'),
('JDBC Tutorial', 'JDBC is a Java based technology used for database connectivity');

ARTICLES 表创建如下 −

The ARTICLES table is created as follows −

现在,让我们执行布尔模式的全文搜索,我们正在搜索包含“数据”一词的行 −

Now, let us perform the full-text search in Boolean mode, where we are searching for a row that contains the word ‘data’ −

SELECT * FROM ARTICLES
WHERE MATCH (ARTICLE_TITLE, DESCRIPTION)
AGAINST('data' IN BOOLEAN MODE);

Output

如我们从下面的输出中看到的那样,上面的查询返回了三行,其中包含“数据”一词 −

As we can see in the output below, the above query returned three rows that contains the word ‘data’ −

Example

在下面的查询中,我们正在搜索包含“数据”但不是“集合”的行 −

In the following query, we are searching for the rows that contains the word ‘data’ but not ‘sets’ −

SELECT * FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)
AGAINST('+data -sets' IN BOOLEAN MODE);

Output

以上查询的输出如下所示:

The output for the query above is produced as given below −

Example

在这里,我们正在搜索同时包含“数据”和“集合”这两个词的行 −

Here, we are searching for the rows that contain both the words ‘data’ and ‘set’ −

SELECT * FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)
AGAINST('+data +sets' IN BOOLEAN MODE);

Output

执行给定的查询后,输出如下:

On executing the given query, the output is displayed as follows −

Example

在下面的查询中,我们正在搜索包含“集合”但不是“集合”的较高排名的那些行 −

In the following query, we are searching for the rows that contains the word ‘set’ but not the higher rank for the rows that contain ‘set’ −

SELECT * FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)
AGAINST('+data sets' IN BOOLEAN MODE);

Output

当我们执行以上查询时,输出如下 −

When we execute the query above, the output is obtained as follows −

Example

使用下面的查询,我们正在搜索包含“数据”一词的行,并在搜索中对包含“教程”一词的行进行较低排名 −

Using the following query, we are searching for rows that contain the word ‘data’ and rank the particular record lower in the search, if it contains the word ‘tutorial’ −

SELECT * FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)
AGAINST('+data ~sets' IN BOOLEAN MODE);

Output

执行给定的查询后,输出如下:

On executing the given query, the output is displayed as follows −

Example

在这里,我们正在查找所有包含以“集合”开头的单词的行 −

Here, we are finding all the rows that contains words starting with ‘set’ −

SELECT * FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)
AGAINST('set*' IN BOOLEAN MODE);

Output

执行给定的查询后,输出如下:

On executing the given query, the output is displayed as follows −

MySQL Boolean Full-Text Search Features

以下是 MySQL 布尔全文搜索的一些重要功能 −

Following are some important features of MySQL Boolean full-text search −

  1. In Boolean full-text search, MySQL does not sort the rows automatically by the relevance in descending order.

  2. The InnoDB table requires all columns of the MATCH expression has a FULLTEXT index to perform Boolean queries.

  3. If we provide multiple Boolean operators on a search query on InnoDB tables e.g. '++hello', MySQL does not support them and it generates an error. However, if we do the same thing in MyISAM, it ignores the extra operator and uses the operator that is closest to the search word.

  4. Trailing (+) or (-) signs are not supported in InnoDB full-text search. It only supports leading + or − sign.

  5. MySQL will generate an error if the search word is 'hello+' or 'hello-'. In addition to that, the following will also generate an error '*', '-'.

  6. MySQL will ignore the word in the search result, if it appears in more than 50% of the rows. This is called 50% threshold.

Boolean Full-Text Search Using Client Program

使用客户端程序还可以对 MySQL 数据库执行布尔全文搜索操作。

We can also perform Boolean Full-Text Search operation on a MySQL database using the client program.

Syntax

Example

以下是这些程序 −

Following are the programs −