How do you use MySQL for full-text search?

Full-text search is a technique for searching records in a database that match query terms. In MySQL, full-text search is implemented using FULLTEXT indexes and related SQL functions. Next, I will detail how to use full-text search in MySQL.

1. Create a Table with FULLTEXT Index

First, we need a table with a FULLTEXT index. FULLTEXT indexes can be created on columns of type CHAR, VARCHAR, or TEXT. Here is an example of creating such a table:

sql
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(title, content)
) ENGINE=InnoDB;

In this example, we create a table named articles with two columns title and content, and we add a FULLTEXT index to both columns.

2. Insert Data into the Table

Next, we insert sample data into the table to enable full-text search later:

sql
INSERT INTO articles (title, content) VALUES
('MySQL Full-Text Search Introduction', 'This article details the full-text search feature in MySQL.'),
('Learning SQL', 'SQL is a powerful database query language for managing relational database data.'),
('Full-Text Search Example', 'This article demonstrates how to use full-text search in MySQL.');

3. Perform Search Using MATCH() AGAINST()

Now we can execute searches using the FULLTEXT index. Use the MATCH() AGAINST() syntax:

sql
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('full-text search' IN NATURAL LANGUAGE MODE);

This query returns all records where the title or content fields contain the phrase 'full-text search'. IN NATURAL LANGUAGE MODE is the default mode, which performs search based on natural language processing.

4. Use Boolean Mode for More Complex Searches

Additionally, MySQL full-text search supports boolean mode for advanced queries, such as finding records containing certain words but excluding others:

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

In this example, results include records containing 'MySQL' but not 'SQL'.

5. Adjust Search Result Sorting

We can control search result ordering by specifying detailed conditions in the AGAINST() function. For instance, we can increase keyword weights to prioritize records containing specific terms:

sql
SELECT *, MATCH(title, content) AGAINST('full-text search' IN NATURAL LANGUAGE MODE) AS score
FROM articles
ORDER BY score DESC;

Here, we calculate a score value; records with more occurrences of 'full-text search' have higher scores and appear earlier in results.

Summary

Using MySQL's full-text search feature enables efficient keyword searching in text data. Creating FULLTEXT indexes, using MATCH() AGAINST() queries, and selecting the appropriate search mode are key steps for effective full-text search. Through these examples, you can see that setting up and using full-text search is straightforward and efficient.