MySQL MATCH Function: Full-Text Search Guide

1 year ago
Jackson Davis
2 minutes

MATCH is a full-text search function in MySQL used to conduct full-text search operations. It searches for rows containing specific keywords in specified columns or column sets. The MATCH function is primarily used for full-text indexing in MySQL.

The general syntax of the MATCH function is as follows:

MATCH (column1, column2, ...) AGAINST ('search_keyword' [search_modifier])

In this case, column1, column2, … are the names of the columns to be searched, which can be either single column names or a collection of column names. search_keyword is the keyword or phrase to be searched.

The search modifier is optional and is used to specify the behavior of the search. Commonly used search modifiers include:

  1. Default search mode is used to perform searches in the full-text index. It processes keywords and text in a natural language manner and returns the most relevant results.
  2. IN BOOLEAN MODE: used for performing boolean full-text search. It allows for combining keywords using boolean operators (AND, OR, NOT) and specifying the importance of keywords using + and – symbols.
  3. WITH QUERY EXPANSION: used to broaden a search by including more vocabulary related to the search keywords.

Example usage:

SELECT * FROM table_name WHERE MATCH (column1, column2) AGAINST ('search_keyword' IN NATURAL LANGUAGE MODE);

Please note that in order to use the MATCH function, the columns in the table need to have a full-text index. You can create a full-text index using the FULLTEXT index type.

bannerAds