Use of Match Against in Mysql

Hello friends, in this article i will explain you how to find exact word from database using mysql. all developer know in some condition LIKE is not working perfectly i.e means in some searching functionality LIKE function of mysql not worked at that time for perfect match word you can use instead of LIKE to MATCH AGAINST function of mysql. so let me explain how it will works for finding exact matching word.

first you will create one sample demo table in mysql database

In that table i m inserting record something like

in above first table while creating table i have added fulltext i.e MATCH AGAINST field to h_name field of table. so you can later also alter that table and add fulltext into that h_name field something like

most importing things you have to remember for this fulltext is your table engine should be “MyISAM” engine otherwise fulltext function will not work on that table and also you can apply fulltext function on that table. and suppose your table that you have created for that table’s engine is different that you can change like

now i want to search exact word “mill” found in that table than i will write query for that something like

after fired above mysql query you will get exact result from table, and most important is before running above query you have to once check that default searching word is what for full text search. so for that you can check like in below query

suppose in that value you will get 4 than it means in your searching word minimum 4character length otherwise it will not getting perfect result i.e suppose instead of mill i m trying to find word from query is “mil” than if that word is exist on that table than only you will not get result of that because you min_length is 4 and you are trying to find three word text i.e “mil” so it will not work. so for that you have to change its ft_min_word_len.

now i will explain you how to change default value of ft_min_word_len value of mysql fulltext searching functionality.

1)go to C:\xampp\mysql\bin\my.ini and find word ft_min_word_len and changed it to ft_min_word_len = 1 than once restart your xampp server.

2)after restart xampp server you have assign again fulltext searching on column h_name than only you will get its changing effect. so once again add this query

-so now your exact word searching functionality will work nicely using MATCH AGAINST i.e fulltext searching functionality.


That’s it. so do simple and easy code like this and share to your friend also.