MySQL MATCH() AGAINST(): Full-Text Search Functions – MySQL Tutorial

MySQL match() against() function can allow mysql support full-text search. In this tutorial, we will introduce basic way to use this function.

Syntax

The match() against() function is defined as:

Select * from table_name where MATCH (col1,col2,...) AGAINST (expr [search_modifier])

Where search_modifier can be:

search_modifier:
  {
       IN NATURAL LANGUAGE MODE
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
  }

Default value is: IN NATURAL LANGUAGE MODE

How to use mysql match() against()?

MySQL InnoDB or MyISAM tables can use.

Here are some tips we must notice before using mysql match() against().

(1) As to

MATCH (col1,col2,...)

We must to add fulltext index for col1, col2…

For example:

We have added fulltext index for (post_title), (post_content) and (post_title, post_content) on wordpress wp_posts table.

Here is an example.

Then you can create sql statements below:

SELECT * FROM `wp_posts` WHERE match(post_title, post_content) against('java python') limit  10
SELECT * FROM `wp_posts` WHERE match(post_title) against('java python') limit  10
SELECT * FROM `wp_posts` WHERE match(post_content) against('java python') limit  10

However, you can not use other columns without fulltext index.

As to sql below:

SELECT * FROM `wp_posts` WHERE match(post_title, post_excerpt) against('java python') limit  10

We do not create a fulltext index on post_excerpt column. Running this sql, we will get:  Can’t find FULLTEXT index matching the column list error.

(2) as to

AGAINST (expr [search_modifier])

Here expr is a string, such as ‘java python’, the default value of search_modifier is IN NATURAL LANGUAGE MODE

In order to use against() to match words, we should notice:

1. The minimum length of word

As to InnoDB engine, three characters is valid, such as: a, at, of, on words are invalid words, they will be ingnored when matching words.

As to MyISAM engine, four characters is valid.

2. How to split words?

expr is a string, MySQL will split it to some words to match query.

For example: “java python” will be splited to two words “java” and “python”.

MySQL use some certain delimiter characters to split words, such as (space), , (comma), and . (period), this point must be notice when you are search chinese text.

(3) When MATCH() is used in a WHERE clause, the query rows returned will be automatically sorted with the highest relevance first.

Here is the example.

SELECT ID, post_title,  match(post_title, post_content) against('java python') as score FROM `wp_posts` WHERE match(post_title, post_content) against('java python') limit  10

The query result is:

We can find the value of score is from big to small.