Syntax:
MATCH (col1,col2,...) AGAINST (expr [search_modifier])

MySQL has support for full-text indexing and searching:

o A full-text index in MySQL is an index of type FULLTEXT.

o Full-text indexes can be used only with InnoDB or MyISAM tables, and
  can be created only for CHAR, VARCHAR, or TEXT columns.

o MySQL provides a built-in full-text ngram parser that supports
  Chinese, Japanese, and Korean (CJK), and an installable MeCab
  full-text parser plugin for Japanese. Parsing differences are
  outlined in
  https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-ngram.html,
  and
  https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-mecab.html.

o A FULLTEXT index definition can be given in the CREATE TABLE
  statement when a table is created, or added later using ALTER TABLE
  or CREATE INDEX.

o For large data sets, it is much faster to load your data into a table
  that has no FULLTEXT index and then create the index after that, than
  to load data into a table that has an existing FULLTEXT index.

Full-text searching is performed using MATCH() AGAINST() syntax.
MATCH() takes a comma-separated list that names the columns to be
searched. AGAINST takes a string to search for, and an optional
modifier that indicates what type of search to perform. The search
string must be a string value that is constant during query evaluation.
This rules out, for example, a table column because that can differ for
each row.

Previously, MySQL permitted the use of a rollup column with MATCH(),
but queries employing this construct performed poorly and with
unreliable results. (This is due to the fact that MATCH() is not
implemented as a function of its arguments, but rather as a function of
the row ID of the current row in the underlying scan of the base
table.) As of MySQL 8.0.28, MySQL no longer allows such queries; more
specifically, any query matching all of the criteria listed here is
rejected with ER_FULLTEXT_WITH_ROLLUP
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
#error_er_fulltext_with_rollup):

o MATCH() appears in the SELECT list, GROUP BY clause, HAVING clause,
  or ORDER BY clause of a query block.

o The query block contains a GROUP BY ... WITH ROLLUP clause.

o The argument of the call to the MATCH() function is one of the
  grouping columns.

Some examples of such queries are shown here:

# MATCH() in SELECT list...
SELECT MATCH (a) AGAINST ('abc') FROM t GROUP BY a WITH ROLLUP;
SELECT 1 FROM t GROUP BY a, MATCH (a) AGAINST ('abc') WITH ROLLUP;

# ...in HAVING clause...
SELECT 1 FROM t GROUP BY a WITH ROLLUP HAVING MATCH (a) AGAINST ('abc');

# ...and in ORDER BY clause
SELECT 1 FROM t GROUP BY a WITH ROLLUP ORDER BY MATCH (a) AGAINST ('abc');

The use of MATCH() with a rollup column in the WHERE clause is
permitted.

There are three types of full-text searches:

o A natural language search interprets the search string as a phrase in
  natural human language (a phrase in free text). There are no special
  operators, with the exception of double quote (") characters. The
  stopword list applies. For more information about stopword lists, see
  https://dev.mysql.com/doc/refman/8.0/en/fulltext-stopwords.html.

  Full-text searches are natural language searches if the IN NATURAL
  LANGUAGE MODE modifier is given or if no modifier is given. For more
  information, see
  https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.htm
  l.

o A boolean search interprets the search string using the rules of a
  special query language. The string contains the words to search for.
  It can also contain operators that specify requirements such that a
  word must be present or absent in matching rows, or that it should be
  weighted higher or lower than usual. Certain common words (stopwords)
  are omitted from the search index and do not match if present in the
  search string. The IN BOOLEAN MODE modifier specifies a boolean
  search. For more information, see
  https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html.

o A query expansion search is a modification of a natural language
  search. The search string is used to perform a natural language
  search. Then words from the most relevant rows returned by the search
  are added to the search string and the search is done again. The
  query returns the rows from the second search. The IN NATURAL
  LANGUAGE MODE WITH QUERY EXPANSION or WITH QUERY EXPANSION modifier
  specifies a query expansion search. For more information, see
  https://dev.mysql.com/doc/refman/8.0/en/fulltext-query-expansion.html
  .

URL: https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

