MySQL full-text Search Examples

2020-06-12

Steps on performing the full-text search on MySQL

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

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:

1
2
3

SELECT entryID FROM ft WHERE MATCH (entry) AGAINST ('golf');

RESULT : entryID : 3

Prefix Search:

1
2
3

SELECT entryID FROM ft WHERE MATCH (entry) AGAINST ('grad*' IN BOOLEAN MODE);

RESULT entryID: 1 and 3

Boolean Searches:

# No operator signifies OR operation 
SELECT entryID FROM ft WHERE MATCH (entry) AGAINST ('graduate michigan' IN BOOLEAN MODE);

RESULT entryID: 1 and 3

# AND

SELECT entryID FROM ft WHERE MATCH (entry) AGAINST ('+graduate +michigan' IN BOOLEAN MODE); 

RESULT entryID: 1

# Negation
SELECT entryID FROM ft WHERE MATCH (entry) AGAINST ('+graduate -michigan' IN BOOLEAN MODE);

RESULT entryID: 3

Haoming Gao

MySQL full-text Search Examples

Steps on performing the full-text search on MySQL

References