<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="full-text-adding-collation">
      </a>
      14.9.7 Adding a User-Defined Collation for Full-Text Indexing
     </h3>
    </div>
   </div>
  </div>
  <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Warning
   </div>
   <p>
    User-defined collations are deprecated; you should expect
          support for them to be removed in a future version of MySQL.
          The server issues a warning for any use of
    <code class="literal">
     COLLATE
     <em class="replaceable">
      <code>
       user_defined_collation
      </code>
     </em>
    </code>
    in
          an SQL statement; a warning is also issued when the server is
          started with
    <a class="link" href="server-system-variables.html#sysvar_collation_server">
     <code class="option">
      --collation-server
     </code>
    </a>
    set equal to the name of a user-defined collation.
   </p>
  </div>
  <p>
   This section describes how to add a user-defined collation for
        full-text searches using the built-in full-text parser. The
        sample collation is like
   <code class="literal">
    latin1_swedish_ci
   </code>
   but treats the
   <code class="literal">
    '-'
   </code>
   character as a letter
        rather than as a punctuation character so that it can be indexed
        as a word character. General information about adding collations
        is given in
   <a class="xref" href="adding-collation.html" title="12.14 Adding a Collation to a Character Set">
    Section 12.14, “Adding a Collation to a Character Set”
   </a>
   ; it is assumed
        that you have read it and are familiar with the files involved.
  </p>
  <p>
   To add a collation for full-text indexing, use the following
        procedure. The instructions here add a collation for a simple
        character set, which as discussed in
   <a class="xref" href="adding-collation.html" title="12.14 Adding a Collation to a Character Set">
    Section 12.14, “Adding a Collation to a Character Set”
   </a>
   , can be created using a
        configuration file that describes the character set properties.
        For a complex character set such as Unicode, create collations
        using C source files that describe the character set properties.
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Add a collation to the
      <code class="filename">
       Index.xml
      </code>
      file.
            The permitted range of IDs for user-defined collations is
            given in
      <a class="xref" href="adding-collation-choosing-id.html" title="12.14.2 Choosing a Collation ID">
       Section 12.14.2, “Choosing a Collation ID”
      </a>
      . The
            ID must be unused, so choose a value different from 1025 if
            that ID is already taken on your system.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-xml"><div class="docs-select-all right" id="sa18389068"><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-xml"><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>charset</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>latin1<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>
...
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>collation</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>latin1_fulltext_ci<span class="token punctuation">"</span></span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>1025<span class="token punctuation">"</span></span><span class="token punctuation">/&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>charset</span><span class="token punctuation">&gt;</span></span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Declare the sort order for the collation in the
      <code class="filename">
       latin1.xml
      </code>
      file. In this case, the
            order can be copied from
      <code class="literal">
       latin1_swedish_ci
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-xml"><div class="docs-select-all right" id="sa33380513"><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-xml"><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>collation</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>latin1_fulltext_ci<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>map</span><span class="token punctuation">&gt;</span></span>
00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
10 11 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F
20 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F
30 31 32 33 34 35 36 37 38 39 3A 3B 3C 3D 3E 3F
40 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F
50 51 52 53 54 55 56 57 58 59 5A 5B 5C 5D 5E 5F
60 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F
50 51 52 53 54 55 56 57 58 59 5A 7B 7C 7D 7E 7F
80 81 82 83 84 85 86 87 88 89 8A 8B 8C 8D 8E 8F
90 91 92 93 94 95 96 97 98 99 9A 9B 9C 9D 9E 9F
A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 AA AB AC AD AE AF
B0 B1 B2 B3 B4 B5 B6 B7 B8 B9 BA BB BC BD BE BF
41 41 41 41 5C 5B 5C 43 45 45 45 45 49 49 49 49
44 4E 4F 4F 4F 4F 5D D7 D8 55 55 55 59 59 DE DF
41 41 41 41 5C 5B 5C 43 45 45 45 45 49 49 49 49
44 4E 4F 4F 4F 4F 5D F7 D8 55 55 55 59 59 DE FF
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>map</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>collation</span><span class="token punctuation">&gt;</span></span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Modify the
      <code class="literal">
       ctype
      </code>
      array in
      <code class="filename">
       latin1.xml
      </code>
      . Change the value
            corresponding to 0x2D (which is the code for the
      <code class="literal">
       '-'
      </code>
      character) from 10 (punctuation) to
            01 (uppercase letter). In the following array, this is the
            element in the fourth row down, third value from the end.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-xml"><div class="docs-select-all right" id="sa41533233"><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-xml"><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>ctype</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>map</span><span class="token punctuation">&gt;</span></span>
00
20 20 20 20 20 20 20 20 20 28 28 28 28 28 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
48 10 10 10 10 10 10 10 10 10 10 10 10 <em class="replaceable">01</em> 10 10
84 84 84 84 84 84 84 84 84 84 10 10 10 10 10 10
10 81 81 81 81 81 81 01 01 01 01 01 01 01 01 01
01 01 01 01 01 01 01 01 01 01 01 10 10 10 10 10
10 82 82 82 82 82 82 02 02 02 02 02 02 02 02 02
02 02 02 02 02 02 02 02 02 02 02 10 10 10 10 20
10 00 10 02 10 10 10 10 10 10 01 10 01 00 01 00
00 10 10 10 10 10 10 10 10 10 02 10 02 00 02 01
48 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01
01 01 01 01 01 01 01 10 01 01 01 01 01 01 01 02
02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02
02 02 02 02 02 02 02 10 02 02 02 02 02 02 02 02
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>map</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>ctype</span><span class="token punctuation">&gt;</span></span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Restart the server.
     </p>
    </li>
    <li class="listitem">
     <p>
      To employ the new collation, include it in the definition of
            columns that are to use it:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34413581"><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">DROP</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> t1<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.13 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>
    a <span class="token datatype">TEXT</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> latin1 <span class="token keyword">COLLATE</span> latin1_fulltext_ci<span class="token punctuation">,</span>
    <span class="token keyword">FULLTEXT</span> <span class="token keyword">INDEX</span><span class="token punctuation">(</span>a<span class="token punctuation">)</span>
    <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.47 sec)</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Test the collation to verify that hyphen is considered as a
            word character:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8467724"><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">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUEs</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 punctuation">,</span><span class="token punctuation">(</span><span class="token string">'abcd'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 3 rows affected (0.22 sec)</span>
<span class="token output">Records: 3  Duplicates: 0  Warnings: 0</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> t1 <span class="token keyword">WHERE</span> <span class="token operator">MATCH</span> a <span class="token keyword">AGAINST</span> <span class="token punctuation">(</span><span class="token string">'----'</span> <span class="token keyword">IN</span> <span class="token datatype">BOOLEAN</span> <span class="token keyword">MODE</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> <span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span> <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
     </div>
    </li>
   </ol>
  </div>
 </div>
 <br/>
</div>
