MATCH and AGAINST in MySQLf

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(
EmployeeDetails VARCHAR(100));

INSERT INTO Dept VALUES
(‘John|Sales Team’),
(‘Mike|Sales’),
(‘Smith|Development’),
(‘Jim|New Sales Team’),
(‘Lucy|Development’),
(‘Will|Development’);

ALTER TABLE Dept ADD FULLTEXT(EmployeeDetails);

SELECT * FROM Dept WHERE MATCH(EmployeeDetails) AGAINST(‘+Sales+’) ;