Mysql 简明教程

MySQL - Full-Text Search

MySQL Full-Text Search 允许我们搜索存储在数据库中的基于文本的数据。在对表的列执行全文搜索之前，我们必须在这些列上创建一个全文索引。

The MySQL Full-Text Search allows us to search for a text-based data, stored in the database. Before performing the full-text search in a column(s) of table, we must create a full-text index on those columns.

FTS（全文搜索）提供了通过大型文本内容（例如博客、文章等）匹配搜索的字符串值的能力。

The FTS (full-text search) provides the capability to match the searched string value through large text content such as blogs, articles, etc.

MySQL Full-Text Search

要在 MySQL 表上执行全文搜索，我们在 SQL SELECT 语句的 WHERE 子句中使用 MATCH() 和 AGAINST() 函数。

To perform a Full-Text Search on a MySQL table, we use MATCH() and AGAINST() functions in a WHERE clause of an SQL SELECT statement.

在 MySQL 上执行全文搜索的基本语法如下 −

The basic syntax to perform a full-text search on a MySQL is as follows −

SELECT column_name(s) FROM table_name
WHERE MATCH(col1, col2, ...)
AGAINST(expression [search_modifier])

在此，

Here,

  1. MATCH() function contains one or more columns separated by commas to be searched.

  2. AGAINST() function contains a search string to use for the full-text search.

Key Points of MySQL Full-Text Search

以下是有关 MySQL 中全文搜索的一些要点 −

Following are some key points about the full-text search in MySQL −

  1. Either InnoDB or MyISAM tables use the full-text indexes. The minimum length of the word for full-text searches is three characters for InnoDB tables and four characters for MyISAM tables.

  2. Full-Text indexes can be created on text-based columns (CHAR, VARCHAR or TEXT columns).

  3. A FULLTEXT index can be defined while creating the table using CREATE TABLE statement or can be defined later using the ALTER TABLE or CREATE INDEX statements.

  4. Without FULLTEXT index, it is faster to load large data sets into a table than to load data into a table which has an existing FULLTEXT index. Therefore it is recommended to create the index after loading data.

Types of Full-Text Searches

有三种全文搜索类型。描述如下：

There are three types of full-text searches. The same is described below:

  1. Natural Language Full-Text Searches: This allows the user to enter the search query in a natural human language without any special characters or operators. The search engine will examine the query entered by the user and returns the relevant results based on the user’s intent.

  2. Boolean Full-Text Searches: This allows us to perform a full-text search based on very complex queries in the Boolean mode along with Boolean operators such as +, -, >, <, (), ~, *, "".

  3. Query Expansion Searches: This expands the user’s query to widen the search result of the full-text searches based on automatic relevance feedback or blind query expansion.

Creating MySQL FULLTEXT Index

在 MySQL 中，我们可以在创建新表时或在现有表上为特定列定义全文索引。这可以通过以下三种方式完成：

In MySQL, we can define a full-text index on particular column while creating a new table or on an existing table. This can be done in three ways:

  1. Using the FULLTEXT Keyword

  2. Using the ALTER TABLE Statement

  3. Using the CREATE INDEX Statement

Using the FULLTEXT Keyword

要在创建新表时为列定义全文索引，我们在 CREATE TABLE 查询中对该列使用 FULLTEXT 关键字。以下是语法：

To define full-text index on a column while creating a new table, we use the FULLTEXT keyword on that column within the CREATE TABLE query. Following is the syntax −

CREATE TABLE table_name(
   column1 data_type,
   column2 data_type,
   ...,
   FULLTEXT (column1, column2, ...)
);

让我们首先创建一个名为 FILMS 的表，并使用以下查询对 NAME 和 DIRECTOR 列定义全文索引：

Let us create first a table named FILMS and define the full-text index on NAME and DIRECTOR columns, using the following query −

CREATE TABLE FILMS (
   ID int auto_increment not null primary key,
   NAME varchar(50),
   DIRECTOR TEXT,
   FULLTEXT (NAME, DIRECTOR)
);

现在，让我们使用以下查询将值插入到此表中：

Now, let us insert values into this table using the following query −

INSERT INTO FILMS (NAME, DIRECTOR) VALUES
('RRR', 'Directed by Rajamouli'),
('Bahubali', 'Directed by Rajamouli'),
('Avatar', 'Directed by James cameron'),
('Robot', 'Directed by Shankar');

该表将被创建为：

The table will be created as −

在此，我们使用 MATCH 和 AGAINST 函数，如下所示，从 FILMS 表中获取 NAME 或 DIRECTOR 列与字符串“Rajamouli”匹配的所有行：

Here, we are fetching all the rows from the FILMS table where the NAME or DIRECTOR column matches the string ‘Rajamouli’ using the MATCH and AGAINST functions as shown below −

SELECT * FROM FILMS
WHERE MATCH (NAME, DIRECTOR)
AGAINST ('Rajamouli');

正如我们在以下输出中所看到的，已经针对字符串“Rajamouli”执行了全文搜索，并且它返回了包含此字符串的行。

As we can see in the output below, the full-text search has been performed against a string ‘Rajamouli’ and it returned the rows which contains this string.

Using the ALTER TABLE Statement

在 MySQL 中，我们可以使用 ALTER TABLE 语句为现有表中的特定列创建全文索引。以下是语法：

In MySQL, we can create full-text index on particular columns of an existing table using the ALTER TABLE statement. Following is the syntax −

ALTER TABLE table_name
ADD FULLTEXT (column1, column2,...)

在此示例中，我们对先前创建的 FILMS 表的 NAME 和 DIRECTOR 列定义了一个名为 FULLTEXT 的全文索引：

In this example, we are defining a full-text index named FULLTEXT on NAME and DIRECTOR columns of the previously created FILMS table −

ALTER TABLE FILMS ADD FULLTEXT (NAME, DIRECTOR);

现在，让我们从 FILMS 表中检索 NAME 或 DIRECTOR 列与字符串“Shankar”匹配的所有行。

Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string 'Shankar'.

SELECT * FROM FILMS
WHERE MATCH (NAME, DIRECTOR)
AGAINST ('Shankar');

输出如下：

Following is the output −

Using the CREATE INDEX Statement

在 MySQL 中，我们还可以使用 CREATE INDEX 语句为现有表创建全文索引。以下是语法：

In MySQL, we can also create a full-text index for an existing table using the CREATE INDEX statement. Following is the syntax −

CREATE FULLTEXT INDEX index_name
ON table_name (index_column1, index_column2,...)

我们正在使用 FILMS 表中 NAME 和 DIRECTOR 列的名称 INDEX_FULLTEXT 创建全文索引 −

We are creating a full-text index with the name INDEX_FULLTEXT on the NAME and DIRECTOR column of the FILMS table −

CREATE FULLTEXT INDEX INDEX_FULLTEXT ON FILMS (NAME, DIRECTOR);

现在，让我们从 FILMS 表中检索所有满足以下查询中所示字符串值的 NAME 或 DIRECTOR 列的行 −

Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string value as shown in the below query −

SELECT * FROM FILMS
WHERE MATCH(NAME, DIRECTOR)
AGAINST ('James Cameron');

输出如下：

Following is the output −

Dropping MySQL FULLTEXT index

在 MySQL 中，我们可以使用 ALTER TABLE DROP INDEX 语句从表中删除或丢弃全文索引。

In MySQL, we can remove or drop a full-text index from a table using the ALTER TABLE DROP INDEX statement.

以下是语法 -

Following is the syntax −

ALTER TABLE table_name DROP INDEX index_name;

在以下查询中，我们将删除前面创建的全文索引 −

In the following query, we will delete the previously created full-text index −

ALTER TABLE FILMS DROP INDEX INDEX_FULLTEXT;

让我们通过执行以下查询来验证索引是否已被丢弃 −

Let us verify whether the index is dropped or not by executing the below query −

SELECT * FROM FILMS
WHERE MATCH(NAME, DIRECTOR)
AGAINST ('James Cameron');

正如我们在输出中看到的，全文索引已在 NAME 和 DIRECTOR 列中删除。

As we can see in the output, the full-text index is removed on the NAME and DIRECTOR columns.

ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

Full-Text Search Using Client Program

除了使用 MySQL 查询执行全文搜索，我们还可以使用客户端程序执行。

In addition to performing the full-text search using MySQL Query, we can also do so using the client program.

Syntax

Example

以下是这些程序 −

Following are the programs −