Detailed usage of MySql match anta tool

In MySQL, MATCH...AGAINST is the query syntax for full-text index. It allows you to efficient full-text search of text and supports natural language search and Boolean search modes. The following are detailed usage and examples of MATCH…AGAINST

1. The basic concept of full-text index

• Full-text index is suitable for columns of CHAR, VARCHAR, and TEXT types
• Full-text index supports natural language search and boolean search
• Full-text index can only be used for MyISAM and InnoDB storage engines (MySQL 5.6 and above support InnoDB full-text index)

2. Create a full text index

Create a full-text index on a column that needs to use a full-text index

Example:

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    FULLTEXT (title, content)  -- existtitleandcontentCreate a full text index on the column
);

3. Natural language search

Natural language search is the default mode for full-text indexing. It returns results based on the relevance of search terms

SELECT * FROM table_name 
WHERE MATCH(column1, column2, ...) AGAINST('search_term');

Example:

-- Insert data
INSERT INTO articles (title, content) VALUES
('MySQL Tutorial', 'This is a tutorial about MySQL.'),
('Advanced MySQL', 'Learn advanced techniques in MySQL.'),
('PostgreSQL vs MySQL', 'A comparison between PostgreSQL and MySQL.');
​
-- Natural Language Search
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL');

• result:
  • Return records containing MySQL and sort by dependency
  • The correlation score can be obtained by MATCH…AGAINST results:

SELECT id, title, MATCH(title, content) AGAINST('MySQL') AS score 
FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL');

IV. Boolean search

Boolean search allows specific operators to accurately control search behavior

grammar:

SELECT * FROM table_name 
WHERE MATCH(column1, column2, ...) AGAINST('search_term' IN BOOLEAN MODE);

Common operators:

• +: This word must be included.
• -: This word must not be included.
• *: Wildcard character, matching words that start with the specified word.
• "": Phrase search, match the complete phrase.
• (): Group operator.

Example:

-- Must includeMySQL，And not includedPostgreSQL
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+MySQL -PostgreSQL' IN BOOLEAN MODE);
​
-- IncludeMySQLorPostgreSQL
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL PostgreSQL' IN BOOLEAN MODE);
​
-- Include以MyThe word that begins
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('My*' IN BOOLEAN MODE);
​
-- Include完整短语"MySQL Tutorial"
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('"MySQL Tutorial"' IN BOOLEAN MODE);

5. Relevance sorting

The full-text index calculates a Relevance Score for each record, and the results can be sorted according to the score.

Example:

SELECT id, title, MATCH(title, content) AGAINST('MySQL') AS score 
FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL') 
ORDER BY score DESC;

6. Limitations of full-text index

• Minimum word length: By default, the MySQL full text index ignores words with a length less than 4. It can be adjusted by modifying the ft_min_word_len (MyISAM) or innodb_ft_min_token_size (InnoDB) parameters
• Stop words: Full-text index ignores common stop words (such asthe、andwait). Can be modifiedft_stopword_fileParameter custom stop word list.
• Chinese support: MySQL's full-text index has poor support for Chinese, and it usually requires word segmentation tools (such asngram)use.

7. Configure the full text index

Modify the minimum word length:

-- View the current configuration
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
​
-- Modify configuration（Need to restartMySQL）
SET GLOBAL innodb_ft_min_token_size = 2;

usengramWord participle (supports Chinese):

-- Specified when creating a tablengramWord participle
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    FULLTEXT (title, content) WITH PARSER ngram
);
​
-- Used during queryngramWord participle
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('Keyword' IN BOOLEAN MODE);

8. Delete the full text index

If you need to delete the full text index, you can use the following syntax:

ALTER TABLE table_name DROP INDEX index_name;

Example:

ALTER TABLE articles DROP INDEX title;

9. Performance optimization of full-text index

• Index column selection: Create full-text indexes only for columns that need to be searched to avoid unnecessary index overhead.
• Word participle selection: For Chinese search, usengramWord participle.
• Cache results: For high-frequency queries, the results can be cached to cache systems such as Redis.

This is the end of this article about the detailed usage of MySql match against tool. For more related content of mysql match against tool, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!