Search special characters or text using MATCH and AGAINST in MySQL

Easy Solution Web : MySQL Tutorials

This concept comes under FULLTEXT index. If your table has FULLTEXT index in the fields having field types of varchar, char, text. It is faster to load fields having large data set in comparison to those who don’t have FULLTEXT index.

You need to create FULLTEXT index to a field or multiple fields to load or search text from tables.

Create index of FULLTEXT while creation

Create table `table_name` ( `field_name` varchar(100) FULLTEXT, FULLTEXT idx(`field_name`)) engine=InnoDB;

For this, you need MySQL engine MyISAM and from 5.6 and up we can also use InnoDB.

You can also create index by using Alter command

ALTER TABLE ` table_name` ADD FULLTEXT ( ` field_name ` ) ;

Now how to use MATCH() and AGAINST() in FULLTEXT search.

SELECT * FROM `table_name` WHERE MATCH(`field_name`) against('search_key');

 

Formal Definition as mentioned in MySQL Manual :

MATCH (col1,col2,…) AGAINST (expr [search_modifier])

search_modifier:

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