<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="adding-collation">
      </a>
      12.14 Adding a Collation to a Character Set
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="charset-collation-implementations.html">
       12.14.1 Collation Implementation Types
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="adding-collation-choosing-id.html">
       12.14.2 Choosing a Collation ID
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="adding-collation-simple-8bit.html">
       12.14.3 Adding a Simple Collation to an 8-Bit Character Set
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="adding-collation-unicode-uca.html">
       12.14.4 Adding a UCA Collation to a Unicode Character Set
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045215283088">
  </a>
  <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
        MySQL 8.4 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>
   A collation is a set of rules that defines how to compare and sort
      character strings. Each collation in MySQL belongs to a single
      character set. Every character set has at least one collation, and
      most have two or more collations.
  </p>
  <p>
   A collation orders characters based on weights. Each character in
      a character set maps to a weight. Characters with equal weights
      compare as equal, and characters with unequal weights compare
      according to the relative magnitude of their weights.
  </p>
  <p>
   The
   <a class="link" href="string-functions.html#function_weight-string">
    <code class="literal">
     WEIGHT_STRING()
    </code>
   </a>
   function can be
      used to see the weights for the characters in a string. The value
      that it returns to indicate weights is a binary string, so it is
      convenient to use
   <code class="literal">
    HEX(WEIGHT_STRING(
    <em class="replaceable">
     <code>
      str
     </code>
    </em>
    ))
   </code>
   to display the weights in printable form. The following example
      shows that weights do not differ for lettercase for the letters in
   <code class="literal">
    'AaBb'
   </code>
   if it is a nonbinary case-insensitive
      string, but do differ if it is a binary string:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa37251663"><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">SELECT</span> <span class="token function">HEX</span><span class="token punctuation">(</span><span class="token function">WEIGHT_STRING</span><span class="token punctuation">(</span><span class="token string">'AaBb'</span> <span class="token keyword">COLLATE</span> latin1_swedish_ci<span class="token punctuation">)</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 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 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 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 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 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 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> HEX(WEIGHT_STRING('AaBb' COLLATE latin1_swedish_ci)) <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 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 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 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 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 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 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> 41414242                                             <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 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 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 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 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 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 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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">HEX</span><span class="token punctuation">(</span><span class="token function">WEIGHT_STRING</span><span class="token punctuation">(</span><span class="token datatype">BINARY</span> <span class="token string">'AaBb'</span><span class="token punctuation">)</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 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 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 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 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> HEX(WEIGHT_STRING(BINARY 'AaBb')) <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 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 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 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 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> 41614262                          <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 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 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 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 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></code></pre>
  </div>
  <p>
   MySQL supports several collation implementations, as discussed in
   <a class="xref" href="charset-collation-implementations.html" title="12.14.1 Collation Implementation Types">
    Section 12.14.1, “Collation Implementation Types”
   </a>
   . Some of these
      can be added to MySQL without recompiling:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Simple collations for 8-bit character sets.
     </p>
    </li>
    <li class="listitem">
     <p>
      UCA-based collations for Unicode character sets.
     </p>
    </li>
    <li class="listitem">
     <p>
      Binary (
      <code class="literal">
       <em class="replaceable">
        <code>
         xxx
        </code>
       </em>
       _bin
      </code>
      )
          collations.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following sections describe how to add user-defined collations
      of the first two types to existing character sets. All existing
      character sets already have a binary collation, so there is no
      need here to describe how to add one.
  </p>
  <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Warning
   </div>
   <p>
    Redefining built-in collations is not supported and may result
        in unexpected server behavior.
   </p>
  </div>
  <p>
   Summary of the procedure for adding a new user-defined collation:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Choose a collation ID.
     </p>
    </li>
    <li class="listitem">
     <p>
      Add configuration information that names the collation and
          describes the character-ordering rules.
     </p>
    </li>
    <li class="listitem">
     <p>
      Restart the server.
     </p>
    </li>
    <li class="listitem">
     <p>
      Verify that the server recognizes the collation.
     </p>
    </li>
   </ol>
  </div>
  <p>
   The instructions here cover only user-defined collations that can
      be added without recompiling MySQL. To add a collation that does
      require recompiling (as implemented by means of functions in a C
      source file), use the instructions in
   <a class="xref" href="adding-character-set.html" title="12.13 Adding a Character Set">
    Section 12.13, “Adding a Character Set”
   </a>
   . However, instead of adding
      all the information required for a complete character set, just
      modify the appropriate files for an existing character set. That
      is, based on what is already present for the character set's
      current collations, add data structures, functions, and
      configuration information for the new collation.
  </p>
  <a class="indexterm" name="idm46045215255920">
  </a>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    If you modify an existing user-defined collation, that may
        affect the ordering of rows for indexes on columns that use the
        collation. In this case, rebuild any such indexes to avoid
        problems such as incorrect query results. See
    <a class="xref" href="rebuilding-tables.html" title="3.14 Rebuilding or Repairing Tables or Indexes">
     Section 3.14, “Rebuilding or Repairing Tables or Indexes”
    </a>
    .
   </p>
  </div>
  <h3>
   <a name="idm46045215252736">
   </a>
   Additional Resources
  </h3>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Example showing how to add a collation for full-text searches:
      <a class="xref" href="full-text-adding-collation.html" title="14.9.7 Adding a User-Defined Collation for Full-Text Indexing">
       Section 14.9.7, “Adding a User-Defined Collation for Full-Text Indexing”
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      The Unicode Collation Algorithm (UCA) specification:
      <a class="ulink" href="http://www.unicode.org/reports/tr10/" target="_blank">
       http://www.unicode.org/reports/tr10/
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      The Locale Data Markup Language (LDML) specification:
      <a class="ulink" href="http://www.unicode.org/reports/tr35/" target="_blank">
       http://www.unicode.org/reports/tr35/
      </a>
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
