wiki:cs122b-2017-winter-mysql-fulltext
Last modified 9 months ago Last modified on 02/15/17 23:06:09

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);