Embedded Files
MySQL FullText Search
Introduction
MySQL FullText Search offers the ability to perform a natural language search for a string against a text-collection. A collection would be a set of one or more columns included in FULLTEXT Indexes in the database. It is interpreting the search strings as a phrase in natural human language.
WOW displays exactly the same rows which are returned by the SQL Operation Code.
To view sample Operations, please click here.
For more detailed information, please go to:
Note: Words that match 50% or more of the columns being searched in for a string are not considered matches and will not display as results because they are considered common words by MySQL.
How to create
You want to set up your database correctly by creating the appropriate columns you wish to have along with what you are setting as FULLTEXT Indexes as the columns you will be performing searches in. In my sample, I have created a database and set my Indexes as well. I set my columns as data types of varchar and text.
example:
In the Indexes, I set my column(s) to FULLTEXT as well as choose what other columns may be searched while setting it to another column.
Here is how to set FULLTEXT Indexes: Using MySQL Workbench in this sample.
example: setting FULLTEXT on the columns in the Indexes.
example: you can set each column individually as a FULLTEXT to perform specific searches only in that field… (i.e. MATCH(title))
example: … or you can set multiple columns under one column that is set as a FULLTEXT Indexes. This way, you can search for a string in all 3 columns. (i.e. MATCH(title, body, comments))
Creating an Operation
When you create an Operation, in the Operation Code, write the SQL statement using: MATCH(FULLTEXT Indexes column(s) here)...AGAINST(‘string here’)
So my sample looks something like this:
SELECT *
FROM Table.Library
WHERE MATCH (title, body, comments) AGAINST ('string')
In the MATCH() portion of the code, the columns set as FULLTEXT Indexes in the database are what is placed inside this parenthesis, MATCH(FULLTEXT Indexes here), and using commas to separate each column specified.
In the AGAINST() portion of the code, the string you want to search for in the columns, you put in the MATCH(FULLTEXT Indexes here) portion, is what goes in this set of parenthesis in the AGAINST(‘string here’).
After you are done creating your Operation, run your Operation and if you come across this error, “Can't find FULLTEXT index matching the column list”, or similar, then you might want to consider the following:
For more information on FULLTEXT restrictions please see the MySQL documentation:
https://dev.mysql.com/doc/refman/8.0/en/fulltext-restrictions.html
Boolean
You can also create Boolean searches using the MATCH()...AGAINST() as well. Here is a sample of what it may look like using my columns I created.
example:
SELECT *
FROM Table.Library
WHERE MATCH (title,body,comments) AGAINST ('+boy' '-world' IN BOOLEAN MODE)
Note: My search strings are set as one positive and one negative.
The plus (+) sign represents “AND”. A leading plus sign indicates that this word must be present in each row that is returned.
The minus (-) represents “NOT”. Using no operator indicates “OR”. A leading minus sign indicates that this word must not be present in any of the rows that are returned.
For more information, please go to Boolean Full-Text Searches.
Additional Help
Here are some other helpful pages:
Page updated
Report abuse