The MATCH() AGAINST() syntax is used to conduct full-text searches. The columns to be searched are listed in a comma-separated list that MATCH() accepts. AGAINST requires a search string and an optional modifier to specify the kind of search to conduct. The search string needs to have a constant value during the assessment of the query. This eliminates options like a table column, which can vary for each row.
You must index the data in a column of a table before conducting a full-text search within that column. Every time the data in the column changes, MySQL will recreate the full-text index.
If the column doesn’t have full text index, the operation fails and the following error will be returned.
1191 – Can’t find FULLTEXT index matching the column list
Let’s give an example to demonstrate how MATCH and AGAINST can be used.
CREATE TABLE Dept(
INSERT INTO Dept VALUES
(‘Jim|New Sales Team’),
ALTER TABLE Dept ADD FULLTEXT(EmployeeDetails);
SELECT * FROM Dept WHERE MATCH(EmployeeDetails) AGAINST(‘+Sales+’) ;
Hope this helps.