Describe Mysql full text search?
MySQL version 5.6 or later allows you to define a full-text index for a column whose data type is CHAR, VARCHAR or TEXT in MyISAM or InnoDB table type. Notice that MySQL supported full-text index in the InnoDB tables since version 5.6.
MySQL supports indexing and re-indexing data automatically for a full-text search enabled column.
Add Full Text
ALTER TABLE table_name ADD FULLTEXT(column_name1, column_name2)
Defining FULLTEXT index using CREATE INDEX statement
CREATE FULLTEXT INDEX index_name ON table_name(idx_column_name)
You can explicitly use the IN NATURAL LANGUAGE MODE search modifier in your query as follows:
SELECT X, Y FROM swap_test WHERE MATCH(X,Y) AGAINST('de,rak' IN NATURAL LANGUAGE MODE);
MySQL Boolean full-text searches
SELECT X, Y FROM swap_test WHERE MATCH(X,Y) AGAINST('de -rak' IN BOOLEAN MODE); SELECT X, Y FROM swap_test WHERE MATCH(X,Y) AGAINST('de +rak' IN BOOLEAN MODE);
MySQL Boolean full-text search operators
The following table illustrates the full-text search Boolean operators and their meanings:
+ Include, the word must be present.
– Exclude, the word must not be present.
> Include, and increase ranking value.
< Include, and decrease the ranking value.
() Group words into subexpressions (allowing them to be included, excluded, ranked, and so forth as a group).
~ Negate a word’s ranking value.
* Wildcard at the end of the word.
“” Defines a phrase (as opposed to a list of individual words, the entire phrase is matched for inclusion or exclusion).