MySQL Full-Text Search Examples
- Creating a Full Text Searchable Table:
DROP TABLE IF EXISTS ft; CREATE TABLE ft ( entryID INT AUTO_INCREMENT, entry text, PRIMARY KEY (entryID), FULLTEXT (entry)) ENGINE=MyISAM;
- Adding Rows into the table:
INSERT INTO ft VALUES (1, 'I was born in michigan in 1980 in a small town called Adrian. My mother is named Sue, while my father is named Mike. They currently live in a small town called East Jordan. On April 27th, 2003 I will graduate from Eastern Michigan University with a degree in Computer Information Systems.'); INSERT INTO ft VALUES (2, 'While I was at work today I was having some problems with the RAID array. It seems that we have a rogue cron script that is causing problems. When I find out more info I will post it here.'); INSERT INTO ft VALUES (3, 'After I graduate I am taking a 2 week vacation. On my agenda is a trip to Washington DC to see my girlfriend\'s sister as well as throwing a few discs at the local disc golf course.');
- Searching for whole words
SELECT entryID FROM ft WHERE MATCH (entry) AGAINST ('golf'); RESULT : entryID : 3
- Prefix Search
SELECT entryID FROM ft WHERE MATCH (entry) AGAINST ('grad*' IN BOOLEAN MODE); RESULT entryID: 1 and 3
- Boolean Searches
SELECT entryID FROM ft WHERE MATCH (entry) AGAINST ('+graduate -michigan' IN BOOLEAN MODE); # No operator signifies OR operation SELECT entryID FROM ft WHERE MATCH (entry) AGAINST ('graduate michigan' IN BOOLEAN MODE);
Last modified 8 years ago
Last modified on Feb 15, 2017 11:06:09 PM