How to perform full-text search operation in MySQL?

How to perform full-text search operation in MySQL?

Full text search is a function to find keywords in a text field. In MySQL, we can use full-text search to improve query efficiency and quickly find relevant results. This article will introduce how to perform full-text search operations in MySQL and provide code examples.

1. Create a full-text index
Before performing a full-text search, you first need to create a full-text index on the field to be searched. In MySQL, you can use the FULLTEXT index to implement full-text search functionality.

For example, in a table named articles, we want to perform a full-text search on the content field. We can execute the following SQL statement to create a FULLTEXT index:

ALTER TABLE articles ADD FULLTEXT(content );

2. Perform full-text search query
After creating the full-text index, we can use the MATCH AGAINST statement to perform full-text search query.

Syntax example:
SELECT * FROM articles WHERE MATCH(content) AGAINST('keyword');

Among them, content is the field that needs to be searched, and 'keyword' is the field that needs to be searched. Search keywords. It can be modified according to the actual situation.

Code example:
SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL full-text search');

This query statement will return the keyword "MySQL full-text search" article records.

3. Set full-text search options
The MATCH AGAINST statement in MySQL supports some options that can further control the behavior of full-text search.

1. IN BOOLEAN MODE
   Use IN BOOLEAN MODE to perform Boolean operations on searches.

Code example:
SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL -Full text' IN BOOLEAN MODE);

This query statement will return the key Article records containing the word "MySQL" but not containing the keyword "full text".

2. WITH QUERY EXPANSION
   Using WITH QUERY EXPANSION can expand the search and return more article records related to keywords.

Code example:
SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL full text search' WITH QUERY EXPANSION);

This query statement will return the key More article records related to the word "MySQL full-text search".

4. Limit the number of search results
You can use the LIMIT statement to limit the number of search results to improve query efficiency.

Code example:
SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL full text search') LIMIT 10;

This query statement will return the keyword "MySQL full text" The first 10 article records of "search".

5. Use search rankings
The MATCH AGAINST statement in MySQL can sort keywords according to their frequency of occurrence in the text and return a search ranking.

Code example:
SELECT *, MATCH(content) AGAINST('MySQL full-text search') AS rank FROM articles WHERE MATCH(content) AGAINST('MySQL full-text search') ORDER BY rank DESC;

This query statement will return article records sorted in descending order according to the search ranking of the keyword "MySQL full-text search".

To sum up, the full-text search function in MySQL can be achieved by creating a FULLTEXT index and using the MATCH AGAINST statement. When performing a full-text search operation, you can set options, limit the number of search results, and use search rankings to improve the accuracy and efficiency of your queries.

The above is the detailed content of How to perform full-text search operation in MySQL?. For more information, please follow other related articles on the PHP Chinese website!