<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="fulltext-search">
      </a>
      14.9 Full-Text Search Functions
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="fulltext-natural-language.html">
       14.9.1 Natural Language Full-Text Searches
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="fulltext-boolean.html">
       14.9.2 Boolean Full-Text Searches
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="fulltext-query-expansion.html">
       14.9.3 Full-Text Searches with Query Expansion
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="fulltext-stopwords.html">
       14.9.4 Full-Text Stopwords
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="fulltext-restrictions.html">
       14.9.5 Full-Text Restrictions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="fulltext-fine-tuning.html">
       14.9.6 Fine-Tuning MySQL Full-Text Search
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="full-text-adding-collation.html">
       14.9.7 Adding a User-Defined Collation for Full-Text Indexing
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="fulltext-search-ngram.html">
       14.9.8 ngram Full-Text Parser
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="fulltext-search-mecab.html">
       14.9.9 MeCab Full-Text Parser Plugin
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045202030656">
  </a>
  <a class="indexterm" name="idm46045202029168">
  </a>
  <a class="indexterm" name="idm46045202028096">
  </a>
  <a class="indexterm" name="idm46045202027024">
  </a>
  <p>
   <a name="function_match">
   </a>
   <a class="link" href="fulltext-search.html#function_match">
    <code class="literal">
     MATCH
      (
     <em class="replaceable">
      <code>
       col1
      </code>
     </em>
     ,
     <em class="replaceable">
      <code>
       col2
      </code>
     </em>
     ,...)
      AGAINST (
     <em class="replaceable">
      <code>
       expr
      </code>
     </em>
     [
     <em class="replaceable">
      <code>
       search_modifier
      </code>
     </em>
     ])
    </code>
   </a>
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa21360294"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">search_modifier:</em>
  {
       <span class="token keyword">IN</span> <span class="token keyword">NATURAL</span> <span class="token keyword">LANGUAGE</span> <span class="token keyword">MODE</span>
     <span class="token operator">|</span> <span class="token keyword">IN</span> <span class="token keyword">NATURAL</span> <span class="token keyword">LANGUAGE</span> <span class="token keyword">MODE</span> <span class="token keyword">WITH</span> <span class="token keyword">QUERY</span> <span class="token keyword">EXPANSION</span>
     <span class="token operator">|</span> <span class="token keyword">IN</span> <span class="token datatype">BOOLEAN</span> <span class="token keyword">MODE</span>
     <span class="token operator">|</span> <span class="token keyword">WITH</span> <span class="token keyword">QUERY</span> <span class="token keyword">EXPANSION</span>
  }</code></pre>
  </div>
  <p>
   MySQL has support for full-text indexing and searching:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A full-text index in MySQL is an index of type
      <code class="literal">
       FULLTEXT
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Full-text indexes can be used only with
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      or
      <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
       <code class="literal">
        MyISAM
       </code>
      </a>
      tables, and can be created
          only for
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        CHAR
       </code>
      </a>
      ,
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      , or
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      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
      <a class="xref" href="fulltext-search-ngram.html" title="14.9.8 ngram Full-Text Parser">
       Section 14.9.8, “ngram Full-Text Parser”
      </a>
      , and
      <a class="xref" href="fulltext-search-mecab.html" title="14.9.9 MeCab Full-Text Parser Plugin">
       Section 14.9.9, “MeCab Full-Text Parser Plugin”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <code class="literal">
       FULLTEXT
      </code>
      index definition can be given in
          the
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statement when
          a table is created, or added later using
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      or
      <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
       <code class="literal">
        CREATE INDEX
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      For large data sets, it is much faster to load your data into
          a table that has no
      <code class="literal">
       FULLTEXT
      </code>
      index and then
          create the index after that, than to load data into a table
          that has an existing
      <code class="literal">
       FULLTEXT
      </code>
      index.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Full-text searching is performed using
   <a class="link" href="fulltext-search.html#function_match">
    <code class="literal">
     MATCH() AGAINST()
    </code>
   </a>
   syntax.
   <a class="link" href="fulltext-search.html#function_match">
    <code class="literal">
     MATCH()
    </code>
   </a>
   takes a comma-separated
      list that names the columns to be searched.
   <code class="literal">
    AGAINST
   </code>
   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.
  </p>
  <p>
   MySQL does not permit the use of a rollup column with
   <code class="literal">
    MATCH()
   </code>
   ; more specifically, any query matching
      all of the criteria listed here is rejected with
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_fulltext_with_rollup" target="_top">
    <code class="literal">
     ER_FULLTEXT_WITH_ROLLUP
    </code>
   </a>
   :
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       MATCH()
      </code>
      appears in the
      <code class="literal">
       SELECT
      </code>
      list,
      <code class="literal">
       GROUP BY
      </code>
      clause,
      <code class="literal">
       HAVING
      </code>
      clause, or
      <code class="literal">
       ORDER
          BY
      </code>
      clause of a query block.
     </p>
    </li>
    <li class="listitem">
     <p>
      The query block contains a
      <code class="literal">
       GROUP BY ... WITH
          ROLLUP
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      The argument of the call to the
      <code class="literal">
       MATCH()
      </code>
      function is one of the grouping columns.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Some examples of such queries are shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73611191"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true"># MATCH() in SELECT list...</span>
<span class="token keyword">SELECT</span> <span class="token operator">MATCH</span> <span class="token punctuation">(</span>a<span class="token punctuation">)</span> <span class="token keyword">AGAINST</span> <span class="token punctuation">(</span><span class="token string">'abc'</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> t <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> a <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">FROM</span> t <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> a<span class="token punctuation">,</span> <span class="token operator">MATCH</span> <span class="token punctuation">(</span>a<span class="token punctuation">)</span> <span class="token keyword">AGAINST</span> <span class="token punctuation">(</span><span class="token string">'abc'</span><span class="token punctuation">)</span> <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># ...in HAVING clause...</span>
<span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">FROM</span> t <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> a <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span> <span class="token keyword">HAVING</span> <span class="token operator">MATCH</span> <span class="token punctuation">(</span>a<span class="token punctuation">)</span> <span class="token keyword">AGAINST</span> <span class="token punctuation">(</span><span class="token string">'abc'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># ...and in ORDER BY clause</span>
<span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">FROM</span> t <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> a <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token operator">MATCH</span> <span class="token punctuation">(</span>a<span class="token punctuation">)</span> <span class="token keyword">AGAINST</span> <span class="token punctuation">(</span><span class="token string">'abc'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The use of
   <code class="literal">
    MATCH()
   </code>
   with a rollup column in the
   <code class="literal">
    WHERE
   </code>
   clause is permitted.
  </p>
  <p>
   There are three types of full-text searches:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      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
      <a class="xref" href="fulltext-stopwords.html" title="14.9.4 Full-Text Stopwords">
       Section 14.9.4, “Full-Text Stopwords”
      </a>
      .
     </p>
     <p>
      Full-text searches are natural language searches if the
      <code class="literal">
       IN NATURAL LANGUAGE MODE
      </code>
      modifier is given
          or if no modifier is given. For more information, see
      <a class="xref" href="fulltext-natural-language.html" title="14.9.1 Natural Language Full-Text Searches">
       Section 14.9.1, “Natural Language Full-Text Searches”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      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
      <code class="literal">
       IN BOOLEAN MODE
      </code>
      modifier
          specifies a boolean search. For more information, see
      <a class="xref" href="fulltext-boolean.html" title="14.9.2 Boolean Full-Text Searches">
       Section 14.9.2, “Boolean Full-Text Searches”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      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
      <code class="literal">
       IN NATURAL LANGUAGE MODE WITH
          QUERY EXPANSION
      </code>
      or
      <code class="literal">
       WITH QUERY
          EXPANSION
      </code>
      modifier specifies a query expansion
          search. For more information, see
      <a class="xref" href="fulltext-query-expansion.html" title="14.9.3 Full-Text Searches with Query Expansion">
       Section 14.9.3, “Full-Text Searches with Query Expansion”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   For information about
   <code class="literal">
    FULLTEXT
   </code>
   query
      performance, see
   <a class="xref" href="column-indexes.html" title="10.3.5 Column Indexes">
    Section 10.3.5, “Column Indexes”
   </a>
   .
  </p>
  <p>
   For more information about
   <code class="literal">
    InnoDB
   </code>
   <code class="literal">
    FULLTEXT
   </code>
   indexes, see
   <a class="xref" href="innodb-fulltext-index.html" title="17.6.2.4 InnoDB Full-Text Indexes">
    Section 17.6.2.4, “InnoDB Full-Text Indexes”
   </a>
   .
  </p>
  <p>
   Constraints on full-text searching are listed in
   <a class="xref" href="fulltext-restrictions.html" title="14.9.5 Full-Text Restrictions">
    Section 14.9.5, “Full-Text Restrictions”
   </a>
   .
  </p>
  <p>
   The
   <a class="link" href="myisam-ftdump.html" title="6.6.3 myisam_ftdump — Display Full-Text Index information">
    <span class="command">
     <strong>
      myisam_ftdump
     </strong>
    </span>
   </a>
   utility dumps the contents of
      a
   <code class="literal">
    MyISAM
   </code>
   full-text index. This may be helpful
      for debugging full-text queries. See
   <a class="xref" href="myisam-ftdump.html" title="6.6.3 myisam_ftdump — Display Full-Text Index information">
    Section 6.6.3, “myisam_ftdump — Display Full-Text Index information”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
