MySQL Full-Text Search

Full-Text Search

-- Create fulltext index
CREATE FULLTEXT INDEX idx_content ON posts(title, body);

-- MATCH AGAINST (natural language mode — default)
SELECT *, MATCH(title, body) AGAINST ('docker container') AS relevance
FROM posts
WHERE MATCH(title, body) AGAINST ('docker container')
ORDER BY relevance DESC;

-- Boolean mode (precise control)
SELECT * FROM posts
WHERE MATCH(title, body) AGAINST ('+docker +container -kubernetes' IN BOOLEAN MODE);
-- + must contain
-- - must not contain
-- * wildcard
-- "phrase" exact phrase

-- Query expansion (finds related terms)
SELECT * FROM posts
WHERE MATCH(title, body) AGAINST ('MySQL' WITH QUERY EXPANSION);