<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="fulltext-search-ngram">
      </a>
      14.9.8 ngram Full-Text Parser
     </h3>
    </div>
   </div>
  </div>
  <p>
   The built-in MySQL full-text parser uses the white space between
        words as a delimiter to determine where words begin and end,
        which is a limitation when working with ideographic languages
        that do not use word delimiters. To address this limitation,
        MySQL provides an ngram full-text parser that supports Chinese,
        Japanese, and Korean (CJK). The ngram full-text parser is
        supported for use with
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   and
   <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
    <code class="literal">
     MyISAM
    </code>
   </a>
   .
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    MySQL also provides a MeCab full-text parser plugin for
          Japanese, which tokenizes documents into meaningful words. For
          more information, see
    <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>
  </div>
  <p>
   An ngram is a contiguous sequence of
   <em class="replaceable">
    <code>
     n
    </code>
   </em>
   characters from a given sequence of
        text. The ngram parser tokenizes a sequence of text into a
        contiguous sequence of
   <em class="replaceable">
    <code>
     n
    </code>
   </em>
   characters.
        For example, you can tokenize
   <span class="quote">
    “
    <span class="quote">
     abcd
    </span>
    ”
   </span>
   for different
        values of
   <em class="replaceable">
    <code>
     n
    </code>
   </em>
   using the ngram full-text
        parser.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa5930408"><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-simple">n<span class="token operator">=</span>1<span class="token operator">:</span> 'a'<span class="token punctuation">,</span> 'b'<span class="token punctuation">,</span> 'c'<span class="token punctuation">,</span> 'd'
n<span class="token operator">=</span>2<span class="token operator">:</span> 'ab'<span class="token punctuation">,</span> 'bc'<span class="token punctuation">,</span> 'cd'
n<span class="token operator">=</span>3<span class="token operator">:</span> 'abc'<span class="token punctuation">,</span> 'bcd'
n<span class="token operator">=</span>4<span class="token operator">:</span> 'abcd'</code></pre>
  </div>
  <p>
   The ngram full-text parser is a built-in server plugin. As with
        other built-in server plugins, it is automatically loaded when
        the server is started.
  </p>
  <p>
   The full-text search syntax described in
   <a class="xref" href="fulltext-search.html" title="14.9 Full-Text Search Functions">
    Section 14.9, “Full-Text Search Functions”
   </a>
   applies to the ngram parser
        plugin. Differences in parsing behavior are described in this
        section. Full-text-related configuration options, except for
        minimum and maximum word length options
        (
   <a class="link" href="innodb-parameters.html#sysvar_innodb_ft_min_token_size">
    <code class="literal">
     innodb_ft_min_token_size
    </code>
   </a>
   ,
   <a class="link" href="innodb-parameters.html#sysvar_innodb_ft_max_token_size">
    <code class="literal">
     innodb_ft_max_token_size
    </code>
   </a>
   ,
   <a class="link" href="server-system-variables.html#sysvar_ft_min_word_len">
    <code class="literal">
     ft_min_word_len
    </code>
   </a>
   ,
   <a class="link" href="server-system-variables.html#sysvar_ft_max_word_len">
    <code class="literal">
     ft_max_word_len
    </code>
   </a>
   ) are also
        applicable.
  </p>
  <h4>
   <a name="idm46045201293696">
   </a>
   Configuring ngram Token Size
  </h4>
  <p>
   The ngram parser has a default ngram token size of 2 (bigram).
        For example, with a token size of 2, the ngram parser parses the
        string
   <span class="quote">
    “
    <span class="quote">
     abc def
    </span>
    ”
   </span>
   into four tokens:
   <span class="quote">
    “
    <span class="quote">
     ab
    </span>
    ”
   </span>
   ,
   <span class="quote">
    “
    <span class="quote">
     bc
    </span>
    ”
   </span>
   ,
   <span class="quote">
    “
    <span class="quote">
     de
    </span>
    ”
   </span>
   and
   <span class="quote">
    “
    <span class="quote">
     ef
    </span>
    ”
   </span>
   .
  </p>
  <p>
   ngram token size is configurable using the
   <a class="link" href="server-system-variables.html#sysvar_ngram_token_size">
    <code class="literal">
     ngram_token_size
    </code>
   </a>
   configuration
        option, which has a minimum value of 1 and maximum value of 10.
  </p>
  <p>
   Typically,
   <a class="link" href="server-system-variables.html#sysvar_ngram_token_size">
    <code class="literal">
     ngram_token_size
    </code>
   </a>
   is
        set to the size of the largest token that you want to search
        for. If you only intend to search for single characters, set
   <a class="link" href="server-system-variables.html#sysvar_ngram_token_size">
    <code class="literal">
     ngram_token_size
    </code>
   </a>
   to 1. A
        smaller token size produces a smaller full-text search index,
        and faster searches. If you need to search for words comprised
        of more than one character, set
   <a class="link" href="server-system-variables.html#sysvar_ngram_token_size">
    <code class="literal">
     ngram_token_size
    </code>
   </a>
   accordingly.
        For example,
   <span class="quote">
    “
    <span class="quote">
     Happy Birthday
    </span>
    ”
   </span>
   is
   <span class="quote">
    “
    <span class="quote">
     <span class="phrase" lang="zh-cn">
      生日快乐
     </span>
    </span>
    ”
   </span>
   in
        simplified Chinese, where
   <span class="quote">
    “
    <span class="quote">
     <span class="phrase" lang="zh-cn">
      生日
     </span>
    </span>
    ”
   </span>
   is
   <span class="quote">
    “
    <span class="quote">
     birthday
    </span>
    ”
   </span>
   , and
   <span class="quote">
    “
    <span class="quote">
     <span class="phrase" lang="zh-cn">
      快乐
     </span>
    </span>
    ”
   </span>
   translates
        as
   <span class="quote">
    “
    <span class="quote">
     happy
    </span>
    ”
   </span>
   . To search on two-character words such
        as these, set
   <a class="link" href="server-system-variables.html#sysvar_ngram_token_size">
    <code class="literal">
     ngram_token_size
    </code>
   </a>
   to a value of 2 or higher.
  </p>
  <p>
   As a read-only variable,
   <a class="link" href="server-system-variables.html#sysvar_ngram_token_size">
    <code class="literal">
     ngram_token_size
    </code>
   </a>
   may only be
        set as part of a startup string or in a configuration file:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Startup string:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa39143297"><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-terminal">mysqld <span class="token constant">--ngram_token_size</span><span class="token attr-value"><span class="token punctuation">=</span>2</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Configuration file:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa22435550"><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-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">ngram_token_size</span><span class="token attr-value"><span class="token punctuation">=</span>2</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The following minimum and maximum word length configuration
          options are ignored for
    <code class="literal">
     FULLTEXT
    </code>
    indexes
          that use the ngram parser:
    <a class="link" href="innodb-parameters.html#sysvar_innodb_ft_min_token_size">
     <code class="literal">
      innodb_ft_min_token_size
     </code>
    </a>
    ,
    <a class="link" href="innodb-parameters.html#sysvar_innodb_ft_max_token_size">
     <code class="literal">
      innodb_ft_max_token_size
     </code>
    </a>
    ,
    <a class="link" href="server-system-variables.html#sysvar_ft_min_word_len">
     <code class="literal">
      ft_min_word_len
     </code>
    </a>
    , and
    <a class="link" href="server-system-variables.html#sysvar_ft_max_word_len">
     <code class="literal">
      ft_max_word_len
     </code>
    </a>
    .
   </p>
  </div>
  <h4>
   <a name="idm46045201266384">
   </a>
   Creating a FULLTEXT Index that Uses the ngram Parser
  </h4>
  <p>
   To create a
   <code class="literal">
    FULLTEXT
   </code>
   index that uses the
        ngram parser, specify
   <code class="literal">
    WITH PARSER ngram
   </code>
   with
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   ,
   <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>
  <p>
   The following example demonstrates creating a table with an
   <code class="literal">
    ngram
   </code>
   <code class="literal">
    FULLTEXT
   </code>
   index,
        inserting sample data (Simplified Chinese text), and viewing
        tokenized data in the Information Schema
   <a class="link" href="information-schema-innodb-ft-index-cache-table.html" title="28.4.18 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table">
    <code class="literal">
     INNODB_FT_INDEX_CACHE
    </code>
   </a>
   table.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa82243211"><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 prompt">mysql&gt;</span> <span class="token keyword">USE</span> test<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> articles <span class="token punctuation">(</span>
      id <span class="token datatype">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
      title <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
      body <span class="token datatype">TEXT</span><span class="token punctuation">,</span>
      <span class="token keyword">FULLTEXT</span> <span class="token punctuation">(</span>title<span class="token punctuation">,</span>body<span class="token punctuation">)</span> <span class="token keyword">WITH</span> <span class="token keyword">PARSER</span> ngram
    <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">NAMES</span> utf8mb4<span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> articles <span class="token punctuation">(</span>title<span class="token punctuation">,</span>body<span class="token punctuation">)</span> <span class="token keyword">VALUES</span>
    <span class="token punctuation">(</span><span class="token string">'数据库管理'</span><span class="token punctuation">,</span><span class="token string">'在本教程中我将向你展示如何管理数据库'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token string">'数据库应用开发'</span><span class="token punctuation">,</span><span class="token string">'学习开发数据库应用程序'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> innodb_ft_aux_table<span class="token operator">=</span><span class="token string">"test/articles"</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_FT_INDEX_CACHE <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> doc_id<span class="token punctuation">,</span> position<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To add a
   <code class="literal">
    FULLTEXT
   </code>
   index to an existing table,
        you can use
   <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>
   . For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa32541260"><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 keyword">CREATE</span> <span class="token keyword">TABLE</span> articles <span class="token punctuation">(</span>
      id <span class="token datatype">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
      title <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
      body <span class="token datatype">TEXT</span>
     <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4<span class="token punctuation">;</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> articles <span class="token keyword">ADD</span> <span class="token keyword">FULLTEXT</span> <span class="token keyword">INDEX</span> ft_index <span class="token punctuation">(</span>title<span class="token punctuation">,</span>body<span class="token punctuation">)</span> <span class="token keyword">WITH</span> <span class="token keyword">PARSER</span> ngram<span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># Or:</span>

<span class="token keyword">CREATE</span> <span class="token keyword">FULLTEXT</span> <span class="token keyword">INDEX</span> ft_index <span class="token keyword">ON</span> articles <span class="token punctuation">(</span>title<span class="token punctuation">,</span>body<span class="token punctuation">)</span> <span class="token keyword">WITH</span> <span class="token keyword">PARSER</span> ngram<span class="token punctuation">;</span></code></pre>
  </div>
  <h4>
   <a name="idm46045201249536">
   </a>
   ngram Parser Space Handling
  </h4>
  <p>
   The ngram parser eliminates spaces when parsing. For example:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <span class="quote">
       “
       <span class="quote">
        ab cd
       </span>
       ”
      </span>
      is parsed to
      <span class="quote">
       “
       <span class="quote">
        ab
       </span>
       ”
      </span>
      ,
      <span class="quote">
       “
       <span class="quote">
        cd
       </span>
       ”
      </span>
     </p>
    </li>
    <li class="listitem">
     <p>
      <span class="quote">
       “
       <span class="quote">
        a bc
       </span>
       ”
      </span>
      is parsed to
      <span class="quote">
       “
       <span class="quote">
        bc
       </span>
       ”
      </span>
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045201244288">
   </a>
   ngram Parser Stopword Handling
  </h4>
  <p>
   The built-in MySQL full-text parser compares words to entries in
        the stopword list. If a word is equal to an entry in the
        stopword list, the word is excluded from the index. For the
        ngram parser, stopword handling is performed differently.
        Instead of excluding tokens that are equal to entries in the
        stopword list, the ngram parser excludes tokens that
   <span class="emphasis">
    <em>
     contain
    </em>
   </span>
   stopwords. For example, assuming
   <a class="link" href="server-system-variables.html#sysvar_ngram_token_size">
    <code class="literal">
     ngram_token_size=2
    </code>
   </a>
   , a document
        that contains
   <span class="quote">
    “
    <span class="quote">
     a,b
    </span>
    ”
   </span>
   is parsed to
   <span class="quote">
    “
    <span class="quote">
     a,
    </span>
    ”
   </span>
   and
   <span class="quote">
    “
    <span class="quote">
     ,b
    </span>
    ”
   </span>
   . If a comma (
   <span class="quote">
    “
    <span class="quote">
     ,
    </span>
    ”
   </span>
   ) is defined
        as a stopword, both
   <span class="quote">
    “
    <span class="quote">
     a,
    </span>
    ”
   </span>
   and
   <span class="quote">
    “
    <span class="quote">
     ,b
    </span>
    ”
   </span>
   are
        excluded from the index because they contain a comma.
  </p>
  <p>
   By default, the ngram parser uses the default stopword list,
        which contains a list of English stopwords. For a stopword list
        applicable to Chinese, Japanese, or Korean, you must create your
        own. For information about creating a stopword list, 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>
   Stopwords greater in length than
   <a class="link" href="server-system-variables.html#sysvar_ngram_token_size">
    <code class="literal">
     ngram_token_size
    </code>
   </a>
   are ignored.
  </p>
  <h4>
   <a name="idm46045201234848">
   </a>
   ngram Parser Term Search
  </h4>
  <p>
   For
   <span class="emphasis">
    <em>
     natural language mode
    </em>
   </span>
   search, the
        search term is converted to a union of ngram terms. For example,
        the string
   <span class="quote">
    “
    <span class="quote">
     abc
    </span>
    ”
   </span>
   (assuming
   <a class="link" href="server-system-variables.html#sysvar_ngram_token_size">
    <code class="literal">
     ngram_token_size=2
    </code>
   </a>
   ) is
        converted to
   <span class="quote">
    “
    <span class="quote">
     ab bc
    </span>
    ”
   </span>
   . Given two documents, one
        containing
   <span class="quote">
    “
    <span class="quote">
     ab
    </span>
    ”
   </span>
   and the other containing
   <span class="quote">
    “
    <span class="quote">
     abc
    </span>
    ”
   </span>
   , the search term
   <span class="quote">
    “
    <span class="quote">
     ab bc
    </span>
    ”
   </span>
   matches
        both documents.
  </p>
  <p>
   For
   <span class="emphasis">
    <em>
     boolean mode search
    </em>
   </span>
   , the search term is
        converted to an ngram phrase search. For example, the string
        'abc' (assuming
   <a class="link" href="server-system-variables.html#sysvar_ngram_token_size">
    <code class="literal">
     ngram_token_size=2
    </code>
   </a>
   ) is
        converted to '
   <span class="quote">
    “
    <span class="quote">
     ab bc
    </span>
    ”
   </span>
   '. Given two documents, one
        containing 'ab' and the other containing 'abc', the search
        phrase '
   <span class="quote">
    “
    <span class="quote">
     ab bc
    </span>
    ”
   </span>
   ' only matches the document
        containing 'abc'.
  </p>
  <h4>
   <a name="idm46045201226448">
   </a>
   ngram Parser Wildcard Search
  </h4>
  <p>
   Because an ngram
   <code class="literal">
    FULLTEXT
   </code>
   index contains only
        ngrams, and does not contain information about the beginning of
        terms, wildcard searches may return unexpected results. The
        following behaviors apply to wildcard searches using ngram
   <code class="literal">
    FULLTEXT
   </code>
   search indexes:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If the prefix term of a wildcard search is shorter than
            ngram token size, the query returns all indexed rows that
            contain ngram tokens starting with the prefix term. For
            example, assuming
      <a class="link" href="server-system-variables.html#sysvar_ngram_token_size">
       <code class="literal">
        ngram_token_size=2
       </code>
      </a>
      , a
            search on
      <span class="quote">
       “
       <span class="quote">
        a*
       </span>
       ”
      </span>
      returns all rows starting with
      <span class="quote">
       “
       <span class="quote">
        a
       </span>
       ”
      </span>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      If the prefix term of a wildcard search is longer than ngram
            token size, the prefix term is converted to an ngram phrase
            and the wildcard operator is ignored. For example, assuming
      <a class="link" href="server-system-variables.html#sysvar_ngram_token_size">
       <code class="literal">
        ngram_token_size=2
       </code>
      </a>
      , an
      <span class="quote">
       “
       <span class="quote">
        abc*
       </span>
       ”
      </span>
      wildcard search is converted to
      <span class="quote">
       “
       <span class="quote">
        ab bc
       </span>
       ”
      </span>
      .
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045201216928">
   </a>
   ngram Parser Phrase Search
  </h4>
  <p>
   Phrase searches are converted to ngram phrase searches. For
        example, The search phrase
   <span class="quote">
    “
    <span class="quote">
     abc
    </span>
    ”
   </span>
   is converted to
   <span class="quote">
    “
    <span class="quote">
     ab bc
    </span>
    ”
   </span>
   , which returns documents containing
   <span class="quote">
    “
    <span class="quote">
     abc
    </span>
    ”
   </span>
   and
   <span class="quote">
    “
    <span class="quote">
     ab bc
    </span>
    ”
   </span>
   .
  </p>
  <p>
   The search phrase
   <span class="quote">
    “
    <span class="quote">
     abc def
    </span>
    ”
   </span>
   is converted to
   <span class="quote">
    “
    <span class="quote">
     ab bc de ef
    </span>
    ”
   </span>
   , which returns documents containing
   <span class="quote">
    “
    <span class="quote">
     abc def
    </span>
    ”
   </span>
   and
   <span class="quote">
    “
    <span class="quote">
     ab bc de ef
    </span>
    ”
   </span>
   . A
        document that contains
   <span class="quote">
    “
    <span class="quote">
     abcdef
    </span>
    ”
   </span>
   is not returned.
  </p>
 </div>
 <br/>
</div>
