How to Perform a Full-Text Search in MySQL?

To use MySQL full text search, you must first create a FULLTEXT index, and then use the MATCH()...AGAINST() syntax to query. 1. Create a full-text index: You can add FULLTEXT indexes to CHAR, VARCHAR, and TEXT columns when creating a table or through ALTER TABLE, supporting MyISAM and InnoDB (MySQL 5.6) engines; 2. Use MATCH()...AGAINST() to perform searches: Basic natural language searches such as SELECT FROM articles WHERE MATCH(title, content) AGAINST('database tutorial' IN NATURAL LANGUAGE MODE), returning results sorted by correlation; 3. Select the appropriate search mode: including IN NATURAL LANGUAGE MODE (default, based on correlation score), IN BOOLEAN MODE (supports, -,, quotes, etc. operators) and WITH QUERY EXPANSION (extended search based on high correlation results); 4. Understand correlation scores: In natural language mode, MySQL returns a correlation score of more than 0, which can be explicitly viewed through SELECT statements and arranged in descending order of scores; 5. Pay attention to limitations and optimization: By default, words with less than 4 characters are ignored (can be adjusted by ft_min_word_len or innodb_ft_min_token_size), and common stop words (such as "the", "and"), and can customize the stop word list. The index should be rebuilt after a large number of data changes, and the columns in MATCH() must belong to the same FULLTEXT index. Correctly setting indexes, selecting appropriate modes and adjusting server parameters can significantly improve text search efficiency and are suitable for text-intensive applications such as blogs and document systems.

Performing a full-text search in MySQL allows you to efficiently search for text within large volumes of string data, especially in CHAR , VARCHAR , and TEXT columns. Unlike basic LIKE queries, full-text searches use a specialized index and search algorithm that supports relevance ranking and natural language processing. Here's how to set up and use full-text search in MySQL.

1. Create a Full-Text Index

Before you can perform a full-text search, you must create a FULLTEXT index on the column(s) you want to search. This can be done at table creation or added later.

At table creation:

 CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(title, content)
);

Add to existing table:

 ALTER TABLE articles ADD FULLTEXT(title, content);

Note: Full-text indexes are supported by the MyISAM and InnoDB storage engines (InnoDB from MySQL 5.6).

2. Use MATCH() ... AGAINST() Syntax

Once the index is in place, use the MATCH() ... AGAINST() syntax to perform searches.

Basic natural language search:

 SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('database tutorial' IN NATURAL LANGUAGE MODE);

This returns rows where the search terms appear in either title or content , ranked by relevance.

3. Choose the Right Search Mode

MySQL supports three full-text search modes:

• IN NATURAL LANGUAGE MODE (default): Treats the input as plain text and returns rows based on relevance score.
• IN BOOLEAN MODE : Allows advanced operators like , - , * , and quotes for exact phrases.
• WITH QUERY EXPANSION : Expands the search using related terms from the most relevant results.

Examples:

Boolean mode – required and excluded terms:

 SELECT * FROM articles 
WHERE MATCH(title, content) 
AGAINST(' database -mysql' IN BOOLEAN MODE);

Finds articles about "database" but excludes those mentioning "mysql".

Exact phrase search:

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

Finds rows containing the exact phrase "learn MySQL".

Wildcard (prefix) search:

 SELECT * FROM articles 
WHERE MATCH(title, content) 
AGAINST('sql*' IN BOOLEAN MODE);

Matches words like "SQL", "SQLi", "SQLServer", etc.

4. Understand Relevance Scoring

In natural language mode, MySQL returns a relevance score (a positive decimal). You can view it:

 SELECT id, title, MATCH(title, content) 
AGAINST('database' IN NATURAL LANGUAGE MODE) AS score 
FROM articles 
WHERE MATCH(title, content) AGAINST('database' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

Higher scores mean better match.

5. Know the Limitations and Tips

• Minimum word length: By default, words shorter than 4 characters are ignored. Change this via ft_min_word_len (MyISAM) or innodb_ft_min_token_size (InnoDB).
• Stopwords: Common words like "the", "and", "or" are ignored. You can customize the stopword list.
• Rebuild indexes after changes: Large data changes may require rebuilding the full-text index for optimal performance.
• Only works on indexed columns: Ensure all columns in MATCH() are part of the same FULLTEXT index.

Basically, full-text search in MySQL is powerful for text-heavy applications like blogs or document systems. Set up the index correctly, use the right search mode, and fine-tune server settings for better results.

The above is the detailed content of How to Perform a Full-Text Search in MySQL?. For more information, please follow other related articles on the PHP Chinese website!