Full text search techniques in MySQL

MySQL is one of the most commonly used relational databases at present, and full-text search is also a very important function in it. This article will introduce you to the full-text search techniques in MySQL and help you better use MySQL for full-text search.

1. Configure full-text search support

To use the full-text search function in MySQL, you need to first ensure that the server has enabled this function. In MySQL 5.6.4 and above, you can use the following command to check whether full-text search has been enabled:

SHOW VARIABLES LIKE 'innodb_ft%';

If innodb_ft_server is set to ON, full-text search has been enabled.

If full-text search support is not enabled, you need to modify the my.cnf (or my.ini) file and set the value of the ft_min_word_len parameter to the minimum length of the word to be found. By default, its value is 4. For example, if you want to find words with length 3, you need to change its value to 3 and restart the MySQL service.

2. Create a full-text index

When full-text search support is enabled, you can create a full-text index for the table that needs to be searched. Creating a full-text index is as simple as creating a FULLTEXT index on the columns that require full-text search. For example, if you wish to perform a full-text search within the body of an article, you can create a FULLTEXT index on the "content" column of the "articles" table using the following command:

ALTER TABLE articles ADD FULLTEXT(content);

This will create a FULLTEXT for the "content" column Index to speed up full-text search queries. Note that FULLTEXT indexes can only be created on columns of CHAR, VARCHAR or TEXT data type. Once the full-text index is created, full-text search can begin.

3. Use full-text search query

The syntax of full-text search using MySQL is very similar to ordinary SQL query statements. For example, the statement to find all articles containing the word "MySQL" is as follows:

SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL');

Among them, the MATCH clause specifies the columns to be searched, and the AGAINST clause specifies the keywords to be searched. If you want to do a full-text search in multiple columns, just list the columns. For example, to search for articles that contain "MySQL" in both the text and the title, you can use the following command:

SELECT * FROM articles WHERE MATCH(title,content) AGAINST('MySQL');

When performing full-text search queries, we usually need to pay attention to the following issues:

1. Match words in order: By default, MySQL will treat the searched keywords as a single word and sort them by the best match. If you want to match multiple words in order, you can use double quotes to surround the words. For example, to query all articles containing "MySQL" and "database" in the text, you can use the following command:

SELECT * FROM articles WHERE MATCH(content) AGAINST('"MySQL database"' IN BOOLEAN MODE);

2. Use Boolean pattern matching: MySQL provides Boolean pattern query, which can We search data more flexibly. For example, to find all articles that contain "MySQL" but not "database" in the text, you can use the following command:

SELECT * FROM articles WHERE MATCH(content) AGAINST('+MySQL -database' IN BOOLEAN MODE);

In Boolean mode, the plus sign " " indicates that the word must be included, " "Minus sign" means that the word is not included, "asterisk" means matching any word, and "double quotes" means matching the complete phrase.

3. Adjust the similarity measure: By default, MySQL uses a similarity measure based on word frequency to determine the similarity between texts. However, in some cases it may be necessary to adjust the similarity measure, for example, when searching for music lyrics, we pay more attention to the match rather than the word frequency. To adjust the similarity measure, you can specify the IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION parameter in the parameters of the AGAINST function. For example:

SELECT * FROM lyrics WHERE MATCH(text) AGAINST ('+love +song' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);

In this example, MySQL will use natural language patterns and query expansion to determine the similarity between texts, resulting in better matching results.

4. Conclusion

Full-text search has become a standard feature in modern databases and is widely used in various fields, including social networks, music and video-related applications, etc. The MySQL full-text search techniques are introduced here. I believe these techniques can help you use MySQL for full-text search more efficiently. Of course, there are many other skills and techniques that need to be learned and mastered. I hope this article can open up a new idea for you and inspire more inspiration.

The above is the detailed content of Full text search techniques in MySQL. For more information, please follow other related articles on the PHP Chinese website!