SQL: Introduction to Full Text Search Functions

What Is Full Text?

The first question to consider when learning about Full Text Search Functions is: what do we mean by Full Text?

CREATE TABLE news_articles (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
title VARCHAR(200) NOT NULL
body TEXT NOT NULL
FULLTEXT(title, body);
ALTER TABLE news_articles
ADD FULLTEXT(title, body);

Syntax

The syntax for using the MATCH function follows as below:

MATCH (column_1, column_2, ..., column_n) 
AGAINST (expression <search_modifier>)
  1. IN NATURAL LANGUAGE MODE
  2. IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
  3. and IN BOOLEAN MODE

Using the MATCH Function

The MATCH function itself is usually used with the WHERE clause. Using the default search value IN NATURAL LANGUAGE MODE , the MATCH function will complete a natural language search for an expression or string against a collection of texts determined by the columns specified. For each row, the MATCH function will assign a relevance value, which is a measure of how similar to the string or expression a text is from that row.

SELECT title, body
FROM news_articles
WHERE MATCH (title, body)
AGAINST ('health care' IN NATURAL LANGUAGE MODE);
SELECT
MATCH(title, body) AGAINST ('health care' IN NATURAL LANGUAGE MODE) AS Relevance,
title,
body
FROM news_articles
WHERE MATCH (title, body)
AGAINST ('health care' IN NATURAL LANGUAGE MODE)
LIMIT 10;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store