<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="charset-unicode-sets">
      </a>
      12.10.1 Unicode Character Sets
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045216397808">
  </a>
  <a class="indexterm" name="idm46045216396320">
  </a>
  <p>
   This section describes the collations available for Unicode
        character sets and their differentiating properties. For general
        information about Unicode, see
   <a class="xref" href="charset-unicode.html" title="12.9 Unicode Support">
    Section 12.9, “Unicode Support”
   </a>
   .
  </p>
  <p>
   MySQL supports multiple Unicode character sets:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       utf8mb4
      </code>
      : A UTF-8 encoding of the Unicode
            character set using one to four bytes per character.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       utf8mb3
      </code>
      : A UTF-8 encoding of the Unicode
            character set using one to three bytes per character. This
            character set is deprecated; please use
      <code class="literal">
       utf8mb4
      </code>
      instead.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       utf8
      </code>
      : A deprecated alias for
      <code class="literal">
       utf8mb3
      </code>
      . Use
      <code class="literal">
       utf8mb4
      </code>
      instead.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       <code class="literal">
        utf8
       </code>
       is expected in a future release to
              become an alias for
       <code class="literal">
        utf8mb4
       </code>
       .
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ucs2
      </code>
      : The UCS-2 encoding of the Unicode
            character set using two bytes per character. Deprecated;
            expect support for this character set to be removed in a
            future version of MySQL.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       utf16
      </code>
      : The UTF-16 encoding for the
            Unicode character set using two or four bytes per character.
            Like
      <code class="literal">
       ucs2
      </code>
      but with an extension for
            supplementary characters.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       utf16le
      </code>
      : The UTF-16LE encoding for the
            Unicode character set. Like
      <code class="literal">
       utf16
      </code>
      but
            little-endian rather than big-endian.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       utf32
      </code>
      : The UTF-32 encoding for the
            Unicode character set using four bytes per character.
     </p>
    </li>
   </ul>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The
    <code class="literal">
     utf8mb3
    </code>
    character set is deprecated and
          you should expect it to be removed in a future MySQL release.
          Please use
    <code class="literal">
     utf8mb4
    </code>
    instead.
    <code class="literal">
     utf8
    </code>
    is currently an alias for
    <code class="literal">
     utf8mb3
    </code>
    , but it is now deprecated as such,
          and
    <code class="literal">
     utf8
    </code>
    is expected subsequently to become
          a reference to
    <code class="literal">
     utf8mb4
    </code>
    .
    <code class="literal">
     utf8mb3
    </code>
    is also displayed in place of
    <code class="literal">
     utf8
    </code>
    in columns of Information Schema
          tables, and in the output of SQL
    <code class="literal">
     SHOW
    </code>
    statements.
   </p>
   <p>
    To avoid ambiguity about the meaning of
    <code class="literal">
     utf8
    </code>
    , consider specifying
    <code class="literal">
     utf8mb4
    </code>
    explicitly for character set
          references.
   </p>
  </div>
  <p>
   <code class="literal">
    utf8mb4
   </code>
   ,
   <code class="literal">
    utf16
   </code>
   ,
   <code class="literal">
    utf16le
   </code>
   , and
   <code class="literal">
    utf32
   </code>
   support
        Basic Multilingual Plane (BMP) characters and supplementary
        characters that lie outside the BMP.
   <code class="literal">
    utf8mb3
   </code>
   and
   <code class="literal">
    ucs2
   </code>
   support only BMP characters.
  </p>
  <p>
   Most Unicode character sets have a general collation (indicated
        by
   <code class="literal">
    _general
   </code>
   in the name or by the absence of
        a language specifier), a binary collation (indicated by
   <code class="literal">
    _bin
   </code>
   in the name), and several
        language-specific collations (indicated by language specifiers).
        For example, for
   <code class="literal">
    utf8mb4
   </code>
   ,
   <code class="literal">
    utf8mb4_general_ci
   </code>
   and
   <code class="literal">
    utf8mb4_bin
   </code>
   are its general and binary
        collations, and
   <code class="literal">
    utf8mb4_danish_ci
   </code>
   is one of
        its language-specific collations.
  </p>
  <a class="indexterm" name="idm46045216355328">
  </a>
  <a class="indexterm" name="idm46045216353840">
  </a>
  <p>
   Most character sets have a single binary collation.
   <code class="literal">
    utf8mb4
   </code>
   is an exception that has two:
   <code class="literal">
    utf8mb4_bin
   </code>
   and
   <code class="literal">
    utf8mb4_0900_bin
   </code>
   . These two binary collations
        have the same sort order but are distinguished by their pad
        attribute and collating weight characteristics. See
   <a class="xref" href="charset-unicode-sets.html#charset-unicode-sets-pad-attributes" title="Collation Pad Attributes">
    Collation Pad Attributes
   </a>
   , and
   <a class="xref" href="charset-unicode-sets.html#charset-unicode-sets-collating-weights" title="Character Collating Weights">
    Character Collating Weights
   </a>
   .
  </p>
  <p>
   Collation support for
   <code class="literal">
    utf16le
   </code>
   is limited. The
        only collations available are
   <code class="literal">
    utf16le_general_ci
   </code>
   and
   <code class="literal">
    utf16le_bin
   </code>
   . These are similar to
   <code class="literal">
    utf16_general_ci
   </code>
   and
   <code class="literal">
    utf16_bin
   </code>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="charset-unicode-sets.html#charset-unicode-sets-uca" title="Unicode Collation Algorithm (UCA) Versions">
       Unicode Collation Algorithm (UCA) Versions
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="charset-unicode-sets.html#charset-unicode-sets-pad-attributes" title="Collation Pad Attributes">
       Collation Pad Attributes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="charset-unicode-sets.html#charset-unicode-sets-language-specific-collations" title="Language-Specific Collations">
       Language-Specific Collations
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="charset-unicode-sets.html#charset-unicode-sets-general-versus-unicode" title="_general_ci Versus _unicode_ci Collations">
       _general_ci Versus _unicode_ci Collations
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="charset-unicode-sets.html#charset-unicode-sets-collating-weights" title="Character Collating Weights">
       Character Collating Weights
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="charset-unicode-sets.html#charset-unicode-sets-miscellaneous" title="Miscellaneous Information">
       Miscellaneous Information
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="charset-unicode-sets-uca">
       </a>
       Unicode Collation Algorithm (UCA) Versions
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045216336032">
   </a>
   <p>
    MySQL implements the
    <code class="literal">
     <em class="replaceable">
      <code>
       xxx
      </code>
     </em>
     _unicode_ci
    </code>
    collations according to the Unicode Collation Algorithm (UCA)
          described at
    <a class="ulink" href="http://www.unicode.org/reports/tr10/" target="_blank">
     http://www.unicode.org/reports/tr10/
    </a>
    . The
          collation uses the version-4.0.0 UCA weight keys:
    <a class="ulink" href="http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt" target="_blank">
     http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt
    </a>
    .
          The
    <code class="literal">
     <em class="replaceable">
      <code>
       xxx
      </code>
     </em>
     _unicode_ci
    </code>
    collations have only partial support for the Unicode Collation
          Algorithm. Some characters are not supported, and combining
          marks are not fully supported. This affects languages such as
          Vietnamese, Yoruba, and Navajo. A combined character is
          considered different from the same character written with a
          single unicode character in string comparisons, and the two
          characters are considered to have a different length (for
          example, as returned by the
    <a class="link" href="string-functions.html#function_char-length">
     <code class="literal">
      CHAR_LENGTH()
     </code>
    </a>
    function or in
          result set metadata).
   </p>
   <p>
    Unicode collations based on UCA versions higher than 4.0.0
          include the version in the collation name. Examples:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        utf8mb4_unicode_520_ci
       </code>
       is based on UCA
              5.2.0 weight keys
              (
       <a class="ulink" href="http://www.unicode.org/Public/UCA/5.2.0/allkeys.txt" target="_blank">
        http://www.unicode.org/Public/UCA/5.2.0/allkeys.txt
       </a>
       ),
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        utf8mb4_0900_ai_ci
       </code>
       is based on UCA
              9.0.0 weight keys
              (
       <a class="ulink" href="http://www.unicode.org/Public/UCA/9.0.0/allkeys.txt" target="_blank">
        http://www.unicode.org/Public/UCA/9.0.0/allkeys.txt
       </a>
       ).
      </p>
     </li>
    </ul>
   </div>
   <p>
    The
    <a class="link" href="string-functions.html#function_lower">
     <code class="literal">
      LOWER()
     </code>
    </a>
    and
    <a class="link" href="string-functions.html#function_upper">
     <code class="literal">
      UPPER()
     </code>
    </a>
    functions perform case
          folding according to the collation of their argument. A
          character that has uppercase and lowercase versions only in a
          Unicode version higher than 4.0.0 is converted by these
          functions only if the argument collation uses a high enough
          UCA version.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="charset-unicode-sets-pad-attributes">
       </a>
       Collation Pad Attributes
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045216319584">
   </a>
   <a class="indexterm" name="idm46045216318096">
   </a>
   <a class="indexterm" name="idm46045216317024">
   </a>
   <a class="indexterm" name="idm46045216315536">
   </a>
   <p>
    Collations based on UCA 9.0.0 and higher are faster than
          collations based on UCA versions prior to 9.0.0. They also
          have a pad attribute of
    <code class="literal">
     NO PAD
    </code>
    , in contrast
          to
    <code class="literal">
     PAD SPACE
    </code>
    as used in collations based on
          UCA versions prior to 9.0.0. For comparison of nonbinary
          strings,
    <code class="literal">
     NO PAD
    </code>
    collations treat spaces at
          the end of strings like any other character (see
    <a class="xref" href="charset-binary-collations.html#charset-binary-collations-trailing-space-comparisons" title="Trailing Space Handling in Comparisons">
     Trailing Space Handling in Comparisons
    </a>
    ).
   </p>
   <p>
    To determine the pad attribute for a collation, use the
    <code class="literal">
     INFORMATION_SCHEMA
    </code>
    <a class="link" href="information-schema-collations-table.html" title="28.3.6 The INFORMATION_SCHEMA COLLATIONS Table">
     <code class="literal">
      COLLATIONS
     </code>
    </a>
    table, which has a
    <code class="literal">
     PAD_ATTRIBUTE
    </code>
    column. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa84975734"><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> COLLATION_NAME<span class="token punctuation">,</span> PAD_ATTRIBUTE
       <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>COLLATIONS
       <span class="token keyword">WHERE</span> CHARACTER_SET_NAME <span class="token operator">=</span> <span class="token string">'utf8mb4'</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>
<span class="token output"><span class="token punctuation">|</span> COLLATION_NAME             <span class="token punctuation">|</span> PAD_ATTRIBUTE <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>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_general_ci         <span class="token punctuation">|</span> PAD SPACE     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_bin                <span class="token punctuation">|</span> PAD SPACE     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_unicode_ci         <span class="token punctuation">|</span> PAD SPACE     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_icelandic_ci       <span class="token punctuation">|</span> PAD SPACE     <span class="token punctuation">|</span></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> utf8mb4_0900_ai_ci         <span class="token punctuation">|</span> NO PAD        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_de_pb_0900_ai_ci   <span class="token punctuation">|</span> NO PAD        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_is_0900_ai_ci      <span class="token punctuation">|</span> NO PAD        <span class="token punctuation">|</span></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> utf8mb4_ja_0900_as_cs      <span class="token punctuation">|</span> NO PAD        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_ja_0900_as_cs_ks   <span class="token punctuation">|</span> NO PAD        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_0900_as_ci         <span class="token punctuation">|</span> NO PAD        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_ru_0900_ai_ci      <span class="token punctuation">|</span> NO PAD        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_ru_0900_as_cs      <span class="token punctuation">|</span> NO PAD        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_zh_0900_as_cs      <span class="token punctuation">|</span> NO PAD        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_0900_bin           <span class="token punctuation">|</span> NO PAD        <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></code></pre>
   </div>
   <p>
    Comparison of nonbinary string values
          (
    <code class="literal">
     CHAR
    </code>
    ,
    <code class="literal">
     VARCHAR
    </code>
    , and
    <code class="literal">
     TEXT
    </code>
    ) that have a
    <code class="literal">
     NO PAD
    </code>
    collation differ from other collations with respect to
          trailing spaces. For example,
    <code class="literal">
     'a'
    </code>
    and
    <code class="literal">
     'a '
    </code>
    compare as different strings, not
          the same string. This can be seen using the binary collations
          for
    <code class="literal">
     utf8mb4
    </code>
    . The pad attribute for
    <code class="literal">
     utf8mb4_bin
    </code>
    is
    <code class="literal">
     PAD
          SPACE
    </code>
    , whereas for
    <code class="literal">
     utf8mb4_0900_bin
    </code>
    it is
    <code class="literal">
     NO
          PAD
    </code>
    . Consequently, operations involving
    <code class="literal">
     utf8mb4_0900_bin
    </code>
    do not add trailing
          spaces, and comparisons involving strings with trailing spaces
          may differ for the two collations:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa33434406"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> utf8mb4_bin<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</span>

<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">'a'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.01 sec)</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> c <span class="token operator">=</span> <span class="token string">'a '</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> c    <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> 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">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">MODIFY</span> c <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> utf8mb4_0900_bin<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.02 sec)</span>
<span class="token output">Records: 0  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> c <span class="token operator">=</span> <span class="token string">'a '</span><span class="token punctuation">;</span>
<span class="token output">Empty set (0.00 sec)</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="charset-unicode-sets-language-specific-collations">
       </a>
       Language-Specific Collations
      </h4>
     </div>
    </div>
   </div>
   <p>
    MySQL implements language-specific Unicode collations if the
          ordering based only on the Unicode Collation Algorithm (UCA)
          does not work well for a language. Language-specific
          collations are UCA-based, with additional language tailoring
          rules. Examples of such rules appear later in this section.
          For questions about particular language orderings,
    <a class="ulink" href="http://unicode.org" target="_blank">
     http://unicode.org
    </a>
    provides Common Locale Data
          Repository (CLDR) collation charts at
    <a class="ulink" href="http://www.unicode.org/cldr/charts/30/collation/index.html" target="_blank">
     http://www.unicode.org/cldr/charts/30/collation/index.html
    </a>
    .
   </p>
   <a class="indexterm" name="idm46045216286608">
   </a>
   <p>
    For example, the nonlanguage-specific
    <code class="literal">
     utf8mb4_0900_ai_ci
    </code>
    and language-specific
    <code class="literal">
     utf8mb4_
     <em class="replaceable">
      <code>
       LOCALE
      </code>
     </em>
     _0900_ai_ci
    </code>
    Unicode collations each have these characteristics:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The collation is based on UCA 9.0.0 and CLDR v30, is
              accent-insensitive and case-insensitive. These
              characteristics are indicated by
       <code class="literal">
        _0900
       </code>
       ,
       <code class="literal">
        _ai
       </code>
       , and
       <code class="literal">
        _ci
       </code>
       in the
              collation name. Exception:
       <code class="literal">
        utf8mb4_la_0900_ai_ci
       </code>
       is not based on
              CLDR because Classical Latin is not defined in CLDR.
      </p>
     </li>
     <li class="listitem">
      <p>
       The collation works for all characters in the range [U+0,
              U+10FFFF].
      </p>
     </li>
     <li class="listitem">
      <p>
       If the collation is not language specific, it sorts all
              characters, including supplementary characters, in default
              order (described following). If the collation is language
              specific, it sorts characters of the language correctly
              according to language-specific rules, and characters not
              in the language in default order.
      </p>
     </li>
     <li class="listitem">
      <p>
       By default, the collation sorts characters having a code
              point listed in the DUCET table (Default Unicode Collation
              Element Table) according to the weight value assigned in
              the table. The collation sorts characters not having a
              code point listed in the DUCET table using their implicit
              weight value, which is constructed according to the UCA.
      </p>
     </li>
     <li class="listitem">
      <p>
       For non-language-specific collations, characters in
              contraction sequences are treated as separate characters.
              For language-specific collations, contractions might
              change character sorting order.
      </p>
     </li>
    </ul>
   </div>
   <p>
    A collation name that includes a locale code or language name
          shown in the following table is a language-specific collation.
          Unicode character sets may include collations for one or more
          of these languages.
   </p>
   <div class="table">
    <a name="charset-unicode-collation-language-specifiers">
    </a>
    <p class="title">
     <b>
      Table 12.3 Unicode Collation Language Specifiers
     </b>
    </p>
    <div class="table-contents">
     <table summary="Language specifiers for Unicode character set collations.">
      <colgroup>
       <col style="width: 40%"/>
       <col style="width: 60%"/>
      </colgroup>
      <thead>
       <tr>
        <th>
         Language
        </th>
        <th>
         Language Specifier
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <td>
         Bosnian
        </td>
        <td>
         <code class="literal">
          bs
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Bulgarian
        </td>
        <td>
         <code class="literal">
          bg
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Chinese
        </td>
        <td>
         <code class="literal">
          zh
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Classical Latin
        </td>
        <td>
         <code class="literal">
          la
         </code>
         or
         <code class="literal">
          roman
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Croatian
        </td>
        <td>
         <code class="literal">
          hr
         </code>
         or
         <code class="literal">
          croatian
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Czech
        </td>
        <td>
         <code class="literal">
          cs
         </code>
         or
         <code class="literal">
          czech
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Danish
        </td>
        <td>
         <code class="literal">
          da
         </code>
         or
         <code class="literal">
          danish
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Esperanto
        </td>
        <td>
         <code class="literal">
          eo
         </code>
         or
         <code class="literal">
          esperanto
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Estonian
        </td>
        <td>
         <code class="literal">
          et
         </code>
         or
         <code class="literal">
          estonian
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Galician
        </td>
        <td>
         <code class="literal">
          gl
         </code>
        </td>
       </tr>
       <tr>
        <td>
         German phone book order
        </td>
        <td>
         <code class="literal">
          de_pb
         </code>
         or
         <code class="literal">
          german2
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Hungarian
        </td>
        <td>
         <code class="literal">
          hu
         </code>
         or
         <code class="literal">
          hungarian
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Icelandic
        </td>
        <td>
         <code class="literal">
          is
         </code>
         or
         <code class="literal">
          icelandic
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Japanese
        </td>
        <td>
         <code class="literal">
          ja
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Latvian
        </td>
        <td>
         <code class="literal">
          lv
         </code>
         or
         <code class="literal">
          latvian
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Lithuanian
        </td>
        <td>
         <code class="literal">
          lt
         </code>
         or
         <code class="literal">
          lithuanian
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Mongolian
        </td>
        <td>
         <code class="literal">
          mn
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Norwegian / Bokmål
        </td>
        <td>
         <code class="literal">
          nb
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Norwegian / Nynorsk
        </td>
        <td>
         <code class="literal">
          nn
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Persian
        </td>
        <td>
         <code class="literal">
          persian
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Polish
        </td>
        <td>
         <code class="literal">
          pl
         </code>
         or
         <code class="literal">
          polish
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Romanian
        </td>
        <td>
         <code class="literal">
          ro
         </code>
         or
         <code class="literal">
          romanian
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Russian
        </td>
        <td>
         <code class="literal">
          ru
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Serbian
        </td>
        <td>
         <code class="literal">
          sr
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Sinhala
        </td>
        <td>
         <code class="literal">
          sinhala
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Slovak
        </td>
        <td>
         <code class="literal">
          sk
         </code>
         or
         <code class="literal">
          slovak
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Slovenian
        </td>
        <td>
         <code class="literal">
          sl
         </code>
         or
         <code class="literal">
          slovenian
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Modern Spanish
        </td>
        <td>
         <code class="literal">
          es
         </code>
         or
         <code class="literal">
          spanish
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Traditional Spanish
        </td>
        <td>
         <code class="literal">
          es_trad
         </code>
         or
         <code class="literal">
          spanish2
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Swedish
        </td>
        <td>
         <code class="literal">
          sv
         </code>
         or
         <code class="literal">
          swedish
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Turkish
        </td>
        <td>
         <code class="literal">
          tr
         </code>
         or
         <code class="literal">
          turkish
         </code>
        </td>
       </tr>
       <tr>
        <td>
         Vietnamese
        </td>
        <td>
         <code class="literal">
          vi
         </code>
         or
         <code class="literal">
          vietnamese
         </code>
        </td>
       </tr>
      </tbody>
     </table>
    </div>
    <div class="table-contents">
     <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 442px;">
      <thead>
       <tr>
        <th style="width: 176.281px;">
         Language
        </th>
        <th style="width: 264.422px;">
         Language Specifier
        </th>
       </tr>
      </thead>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <p>
    MySQL provides the Bulgarian collations
    <code class="literal">
     utf8mb4_bg_0900_ai_ci
    </code>
    and
    <code class="literal">
     utf8mb4_bg_0900_as_cs
    </code>
    .
   </p>
   <p>
    Croatian collations are tailored for these Croatian letters:
    <code class="literal">
     Č
    </code>
    ,
    <code class="literal">
     Ć
    </code>
    ,
    <code class="literal">
     Dž
    </code>
    ,
    <code class="literal">
     Đ
    </code>
    ,
    <code class="literal">
     Lj
    </code>
    ,
    <code class="literal">
     Nj
    </code>
    ,
    <code class="literal">
     Š
    </code>
    ,
    <code class="literal">
     Ž
    </code>
    .
   </p>
   <p>
    MySQL provides the
    <code class="literal">
     utf8mb4_sr_latn_0900_ai_ci
    </code>
    and
    <code class="literal">
     utf8mb4_sr_latn_0900_as_cs
    </code>
    collations for
          Serbian and the
    <code class="literal">
     utf8mb4_bs_0900_ai_ci
    </code>
    and
    <code class="literal">
     utf8mb4_bs_0900_as_cs
    </code>
    collations for
          Bosnian, when these languages are written with the Latin
          alphabet.
   </p>
   <p>
    MySQL provides collations for both major varieties of
          Norwegian: for Bokmål, you can use
    <code class="literal">
     utf8mb4_nb_0900_ai_ci
    </code>
    and
    <code class="literal">
     utf8mb4_nb_0900_as_cs
    </code>
    ; for Nynorsk, MySQL
          now provides
    <code class="literal">
     utf8mb4_nn_0900_ai_ci
    </code>
    and
    <code class="literal">
     utf8mb4_nn_0900_as_cs
    </code>
    .
   </p>
   <p>
    For Japanese, the
    <code class="literal">
     utf8mb4
    </code>
    character set
          includes
    <code class="literal">
     utf8mb4_ja_0900_as_cs
    </code>
    and
    <code class="literal">
     utf8mb4_ja_0900_as_cs_ks
    </code>
    collations. Both
          collations are accent-sensitive and case-sensitive.
    <code class="literal">
     utf8mb4_ja_0900_as_cs_ks
    </code>
    is also
          kana-sensitive and distinguishes Katakana characters from
          Hiragana characters, whereas
    <code class="literal">
     utf8mb4_ja_0900_as_cs
    </code>
    treats Katakana and
          Hiragana characters as equal for sorting. Applications that
          require a Japanese collation but not kana sensitivity may use
    <code class="literal">
     utf8mb4_ja_0900_as_cs
    </code>
    for better sort
          performance.
    <code class="literal">
     utf8mb4_ja_0900_as_cs
    </code>
    uses
          three weight levels for sorting;
    <code class="literal">
     utf8mb4_ja_0900_as_cs_ks
    </code>
    uses four.
   </p>
   <p>
    For Classical Latin collations that are accent-insensitive,
    <code class="literal">
     I
    </code>
    and
    <code class="literal">
     J
    </code>
    compare as
          equal, and
    <code class="literal">
     U
    </code>
    and
    <code class="literal">
     V
    </code>
    compare as equal.
    <code class="literal">
     I
    </code>
    and
    <code class="literal">
     J
    </code>
    , and
    <code class="literal">
     U
    </code>
    and
    <code class="literal">
     V
    </code>
    compare as equal on the base letter
          level. In other words,
    <code class="literal">
     J
    </code>
    is regarded as an
          accented
    <code class="literal">
     I
    </code>
    , and
    <code class="literal">
     U
    </code>
    is
          regarded as an accented
    <code class="literal">
     V
    </code>
    .
   </p>
   <p>
    MySQL provides collations for the Mongolian language when
          written with Cyrillic characters,
    <code class="literal">
     utf8mb4_mn_cyrl_0900_ai_ci
    </code>
    and
    <code class="literal">
     utf8mb4_mn_cyrl_0900_as_cs
    </code>
    .
   </p>
   <p>
    Spanish collations are available for modern and traditional
          Spanish. For both,
    <code class="literal">
     ñ
    </code>
    (n-tilde) is a
          separate letter between
    <code class="literal">
     n
    </code>
    and
    <code class="literal">
     o
    </code>
    . In addition, for traditional Spanish,
    <code class="literal">
     ch
    </code>
    is a separate letter between
    <code class="literal">
     c
    </code>
    and
    <code class="literal">
     d
    </code>
    , and
    <code class="literal">
     ll
    </code>
    is a separate letter between
    <code class="literal">
     l
    </code>
    and
    <code class="literal">
     m
    </code>
    .
   </p>
   <p>
    Traditional Spanish collations may also be used for Asturian
          and Galician. MySQL also provides
    <code class="literal">
     utf8mb4_gl_0900_ai_ci
    </code>
    and
    <code class="literal">
     utf8mb4_gl_0900_as_cs
    </code>
    collations for
          Galician. (These are the same collations as
    <code class="literal">
     utf8mb4_es_0900_ai_ci
    </code>
    and
    <code class="literal">
     utf8mb4_es_0900_as_cs
    </code>
    , respectively.)
   </p>
   <p>
    Swedish collations include Swedish rules. For example, in
          Swedish, the following relationship holds, which is not
          something expected by a German or French speaker:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa25434254"><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-none">Ü = Y &lt; Ö</code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="charset-unicode-sets-general-versus-unicode">
       </a>
       _general_ci Versus _unicode_ci Collations
      </h4>
     </div>
    </div>
   </div>
   <p>
    For any Unicode character set, operations performed using the
    <code class="literal">
     <em class="replaceable">
      <code>
       xxx
      </code>
     </em>
     _general_ci
    </code>
    collation are faster than those for the
    <code class="literal">
     <em class="replaceable">
      <code>
       xxx
      </code>
     </em>
     _unicode_ci
    </code>
    collation. For example, comparisons for the
    <code class="literal">
     utf8mb4_general_ci
    </code>
    collation are faster,
          but slightly less correct, than comparisons for
    <code class="literal">
     utf8mb4_unicode_ci
    </code>
    . The reason is that
    <code class="literal">
     utf8mb4_unicode_ci
    </code>
    supports mappings such
          as expansions; that is, when one character compares as equal
          to combinations of other characters. For example,
    <code class="literal">
     ß
    </code>
    is equal to
    <code class="literal">
     ss
    </code>
    in
          German and some other languages.
    <code class="literal">
     utf8mb4_unicode_ci
    </code>
    also supports
          contractions and ignorable characters.
    <code class="literal">
     utf8mb4_general_ci
    </code>
    is a legacy collation
          that does not support expansions, contractions, or ignorable
          characters. It can make only one-to-one comparisons between
          characters.
   </p>
   <p>
    To further illustrate, the following equalities hold in both
    <code class="literal">
     utf8mb4_general_ci
    </code>
    and
    <code class="literal">
     utf8mb4_unicode_ci
    </code>
    (for the effect of this
          in comparisons or searches, see
    <a class="xref" href="charset-collation-effect.html" title="12.8.6 Examples of the Effect of Collation">
     Section 12.8.6, “Examples of the Effect of Collation”
    </a>
    ):
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa90673352"><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-none">Ä = A
Ö = O
Ü = U</code></pre>
   </div>
   <p>
    A difference between the collations is that this is true for
    <code class="literal">
     utf8mb4_general_ci
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa77981180"><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-none">ß = s</code></pre>
   </div>
   <p>
    Whereas this is true for
    <code class="literal">
     utf8mb4_unicode_ci
    </code>
    , which supports the
          German DIN-1 ordering (also known as dictionary order):
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa81299890"><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-none">ß = ss</code></pre>
   </div>
   <p>
    MySQL implements language-specific Unicode collations if the
          ordering with
    <code class="literal">
     utf8mb4_unicode_ci
    </code>
    does not
          work well for a language. For example,
    <code class="literal">
     utf8mb4_unicode_ci
    </code>
    works fine for German
          dictionary order and French, so there is no need to create
          special
    <code class="literal">
     utf8mb4
    </code>
    collations.
   </p>
   <p>
    <code class="literal">
     utf8mb4_general_ci
    </code>
    also is satisfactory for
          both German and French, except that
    <code class="literal">
     ß
    </code>
    is
          equal to
    <code class="literal">
     s
    </code>
    , and not to
    <code class="literal">
     ss
    </code>
    . If this is acceptable for your
          application, you should use
    <code class="literal">
     utf8mb4_general_ci
    </code>
    because it is faster. If
          this is not acceptable (for example, if you require German
          dictionary order), use
    <code class="literal">
     utf8mb4_unicode_ci
    </code>
    because it is more accurate.
   </p>
   <p>
    If you require German DIN-2 (phone book) ordering, use the
    <code class="literal">
     utf8mb4_german2_ci
    </code>
    collation, which
          compares the following sets of characters equal:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa14616409"><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-none">Ä = Æ = AE
Ö = Œ = OE
Ü = UE
ß = ss</code></pre>
   </div>
   <p>
    <code class="literal">
     utf8mb4_german2_ci
    </code>
    is similar to
    <code class="literal">
     latin1_german2_ci
    </code>
    , but the latter does not
          compare
    <code class="literal">
     Æ
    </code>
    equal to
    <code class="literal">
     AE
    </code>
    or
    <code class="literal">
     Œ
    </code>
    equal to
    <code class="literal">
     OE
    </code>
    . There
          is no
    <code class="literal">
     utf8mb4_german_ci
    </code>
    corresponding to
    <code class="literal">
     latin1_german_ci
    </code>
    for German dictionary
          order because
    <code class="literal">
     utf8mb4_general_ci
    </code>
    suffices.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="charset-unicode-sets-collating-weights">
       </a>
       Character Collating Weights
      </h4>
     </div>
    </div>
   </div>
   <p>
    A character's collating weight is determined as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For all Unicode collations except the
       <code class="literal">
        _bin
       </code>
       (binary) collations, MySQL
              performs a table lookup to find a character's collating
              weight.
      </p>
     </li>
     <li class="listitem">
      <p>
       For
       <code class="literal">
        _bin
       </code>
       collations except
       <code class="literal">
        utf8mb4_0900_bin
       </code>
       , the weight is based
              on the code point, possibly with leading zero bytes added.
      </p>
     </li>
     <li class="listitem">
      <p>
       For
       <code class="literal">
        utf8mb4_0900_bin
       </code>
       , the weight is the
       <code class="literal">
        utf8mb4
       </code>
       encoding bytes. The sort order
              is the same as for
       <code class="literal">
        utf8mb4_bin
       </code>
       , but
              much faster.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Collating weights can be displayed using the
    <a class="link" href="string-functions.html#function_weight-string">
     <code class="literal">
      WEIGHT_STRING()
     </code>
    </a>
    function. (See
    <a class="xref" href="string-functions.html" title="14.8 String Functions and Operators">
     Section 14.8, “String Functions and Operators”
    </a>
    .) If a collation uses a
          weight lookup table, but a character is not in the table (for
          example, because it is a
    <span class="quote">
     “
     <span class="quote">
      new
     </span>
     ”
    </span>
    character),
          collating weight determination becomes more complex:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For BMP characters in general collations
              (
       <code class="literal">
        <em class="replaceable">
         <code>
          xxx
         </code>
        </em>
        _general_ci
       </code>
       ),
              the weight is the code point.
      </p>
     </li>
     <li class="listitem">
      <p>
       For BMP characters in UCA collations (for example,
       <code class="literal">
        <em class="replaceable">
         <code>
          xxx
         </code>
        </em>
        _unicode_ci
       </code>
       and language-specific collations), the following algorithm
              applies:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa25616583"><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-clike"><span class="token keyword">if</span> <span class="token punctuation">(</span>code <span class="token operator">&gt;=</span> <span class="token number">0x3400</span> <span class="token operator">&amp;&amp;</span> code <span class="token operator">&lt;=</span> <span class="token number">0x4DB5</span><span class="token punctuation">)</span>
  base<span class="token operator">=</span> <span class="token number">0xFB80</span><span class="token punctuation">;</span> <span class="token comment" spellcheck="true">/* CJK Ideograph Extension */</span>
<span class="token keyword">else</span> <span class="token keyword">if</span> <span class="token punctuation">(</span>code <span class="token operator">&gt;=</span> <span class="token number">0x4E00</span> <span class="token operator">&amp;&amp;</span> code <span class="token operator">&lt;=</span> <span class="token number">0x9FA5</span><span class="token punctuation">)</span>
  base<span class="token operator">=</span> <span class="token number">0xFB40</span><span class="token punctuation">;</span> <span class="token comment" spellcheck="true">/* CJK Ideograph */</span>
<span class="token keyword">else</span>
  base<span class="token operator">=</span> <span class="token number">0xFBC0</span><span class="token punctuation">;</span> <span class="token comment" spellcheck="true">/* All other characters */</span>
aaaa<span class="token operator">=</span> base <span class="token operator">+</span>  <span class="token punctuation">(</span>code <span class="token operator">&gt;</span><span class="token operator">&gt;</span> <span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
bbbb<span class="token operator">=</span> <span class="token punctuation">(</span>code <span class="token operator">&amp;</span> <span class="token number">0x7FFF</span><span class="token punctuation">)</span> <span class="token operator">|</span> <span class="token number">0x8000</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The result is a sequence of two collating elements,
       <code class="literal">
        aaaa
       </code>
       followed by
       <code class="literal">
        bbbb
       </code>
       . For example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa52462613"><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>_ucs2 <span class="token number">0x04CF</span> <span class="token keyword">COLLATE</span> ucs2_unicode_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 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(_ucs2 0x04CF COLLATE ucs2_unicode_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 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> FBC084CF                                                 <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
      </div>
      <p>
       Thus,
       <code class="literal">
        U+04cf CYRILLIC SMALL LETTER
              PALOCHKA
       </code>
       (
       <code class="literal">
        ӏ
       </code>
       ) is, with all
              UCA 4.0.0 collations, greater than
       <code class="literal">
        U+04c0
              CYRILLIC LETTER PALOCHKA
       </code>
       (
       <code class="literal">
        Ӏ
       </code>
       ). With UCA 5.2.0 collations, all
              palochkas sort together.
      </p>
     </li>
     <li class="listitem">
      <p>
       For supplementary characters in general collations, the
              weight is the weight for
       <code class="literal">
        0xfffd REPLACEMENT
              CHARACTER
       </code>
       . For supplementary characters in UCA
              4.0.0 collations, their collating weight is
       <code class="literal">
        0xfffd
       </code>
       . That is, to MySQL, all
              supplementary characters are equal to each other, and
              greater than almost all BMP characters.
      </p>
      <p>
       An example with Deseret characters and
       <code class="literal">
        COUNT(DISTINCT)
       </code>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40565749"><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> t <span class="token punctuation">(</span>s1 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf32 <span class="token keyword">COLLATE</span> utf32_unicode_ci<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span> <span class="token punctuation">(</span>0xfffd<span class="token punctuation">)</span><span class="token punctuation">;</span>   <span class="token comment" spellcheck="true">/* REPLACEMENT CHARACTER */</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">0x010412</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment" spellcheck="true">/* DESERET CAPITAL LETTER BEE */</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">0x010413</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment" spellcheck="true">/* DESERET CAPITAL LETTER TEE */</span>
<span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token keyword">DISTINCT</span> s1<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The result is 2 because in the MySQL
       <code class="literal">
        <em class="replaceable">
         <code>
          xxx
         </code>
        </em>
        _unicode_ci
       </code>
       collations, the replacement character has a weight of
       <code class="literal">
        0x0dc6
       </code>
       , whereas Deseret Bee and Deseret
              Tee both have a weight of
       <code class="literal">
        0xfffd
       </code>
       . (Were
              the
       <code class="literal">
        utf32_general_ci
       </code>
       collation used
              instead, the result is 1 because all three characters have
              a weight of
       <code class="literal">
        0xfffd
       </code>
       in that collation.)
      </p>
      <p>
       An example with cuneiform characters and
       <a class="link" href="string-functions.html#function_weight-string">
        <code class="literal">
         WEIGHT_STRING()
        </code>
       </a>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89447726"><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">/*
The four characters in the INSERT string are
00000041  # LATIN CAPITAL LETTER A
0001218F  # CUNEIFORM SIGN KAB
000121A7  # CUNEIFORM SIGN KISH
00000042  # LATIN CAPITAL LETTER B
*/</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>s1 <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf32 <span class="token keyword">COLLATE</span> utf32_unicode_ci<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">0x000000410001218f000121a700000042</span><span class="token punctuation">)</span><span class="token punctuation">;</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>s1<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> t<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The result is:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa50695193"><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-none">0E33 FFFD FFFD 0E4A</code></pre>
      </div>
      <p>
       <code class="literal">
        0E33
       </code>
       and
       <code class="literal">
        0E4A
       </code>
       are
              primary weights as in
       <a class="ulink" href="ftp://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt" target="_top">
        UCA
              4.0.0
       </a>
       .
       <code class="literal">
        FFFD
       </code>
       is the weight for
              KAB and also for KISH.
      </p>
      <p>
       The rule that all supplementary characters are equal to
              each other is nonoptimal but is not expected to cause
              trouble. These characters are very rare, so it is very
              rare that a multi-character string consists entirely of
              supplementary characters. In Japan, since the
              supplementary characters are obscure Kanji ideographs, the
              typical user does not care what order they are in, anyway.
              If you really want rows sorted by the MySQL rule and
              secondarily by code point value, it is easy:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa56729984"><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">ORDER</span> <span class="token keyword">BY</span> s1 <span class="token keyword">COLLATE</span> utf32_unicode_ci<span class="token punctuation">,</span> s1 <span class="token keyword">COLLATE</span> utf32_bin</code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       For supplementary characters based on UCA versions higher
              than 4.0.0 (for example,
       <code class="literal">
        <em class="replaceable">
         <code>
          xxx
         </code>
        </em>
        _unicode_520_ci
       </code>
       ),
              supplementary characters do not necessarily all have the
              same collating weight. Some have explicit weights from the
              UCA
       <code class="filename">
        allkeys.txt
       </code>
       file. Others have
              weights calculated from this algorithm:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa3934039"><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-clike">aaaa<span class="token operator">=</span> base <span class="token operator">+</span>  <span class="token punctuation">(</span>code <span class="token operator">&gt;</span><span class="token operator">&gt;</span> <span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
bbbb<span class="token operator">=</span> <span class="token punctuation">(</span>code <span class="token operator">&amp;</span> <span class="token number">0x7FFF</span><span class="token punctuation">)</span> <span class="token operator">|</span> <span class="token number">0x8000</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
    </ul>
   </div>
   <a class="indexterm" name="idm46045216043008">
   </a>
   <p>
    There is a difference between
    <span class="quote">
     “
     <span class="quote">
      ordering by the
          character's code value
     </span>
     ”
    </span>
    and
    <span class="quote">
     “
     <span class="quote">
      ordering by the
          character's binary representation,
     </span>
     ”
    </span>
    a difference that
          appears only with
    <code class="literal">
     utf16_bin
    </code>
    , because of
          surrogates.
   </p>
   <p>
    Suppose that
    <code class="literal">
     utf16_bin
    </code>
    (the binary
          collation for
    <code class="literal">
     utf16
    </code>
    ) was a binary
          comparison
    <span class="quote">
     “
     <span class="quote">
      byte by byte
     </span>
     ”
    </span>
    rather than
    <span class="quote">
     “
     <span class="quote">
      character by character.
     </span>
     ”
    </span>
    If that were so, the
          order of characters in
    <code class="literal">
     utf16_bin
    </code>
    would
          differ from the order in
    <code class="literal">
     utf8mb4_bin
    </code>
    . For
          example, the following chart shows two rare characters. The
          first character is in the range
    <code class="literal">
     E000
    </code>
    -
    <code class="literal">
     FFFF
    </code>
    , so it is
          greater than a surrogate but less than a supplementary. The
          second character is a supplementary.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa83409278"><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-none">Code point  Character                    utf8mb4      utf16
----------  ---------                    -------      -----
0FF9D       HALFWIDTH KATAKANA LETTER N  EF BE 9D     FF 9D
10384       UGARITIC LETTER DELTA        F0 90 8E 84  D8 00 DF 84</code></pre>
   </div>
   <p>
    The two characters in the chart are in order by code point
          value because
    <code class="literal">
     0xff9d
    </code>
    &lt;
    <code class="literal">
     0x10384
    </code>
    . And they are in order by
    <code class="literal">
     utf8mb4
    </code>
    value because
    <code class="literal">
     0xef
    </code>
    &lt;
    <code class="literal">
     0xf0
    </code>
    . But they
          are not in order by
    <code class="literal">
     utf16
    </code>
    value, if we use
          byte-by-byte comparison, because
    <code class="literal">
     0xff
    </code>
    &gt;
    <code class="literal">
     0xd8
    </code>
    .
   </p>
   <p>
    So MySQL's
    <code class="literal">
     utf16_bin
    </code>
    collation is not
    <span class="quote">
     “
     <span class="quote">
      byte by byte.
     </span>
     ”
    </span>
    It is
    <span class="quote">
     “
     <span class="quote">
      by code
          point.
     </span>
     ”
    </span>
    When MySQL sees a supplementary-character
          encoding in
    <code class="literal">
     utf16
    </code>
    , it converts to the
          character's code-point value, and then compares. Therefore,
    <code class="literal">
     utf8mb4_bin
    </code>
    and
    <code class="literal">
     utf16_bin
    </code>
    are the same ordering. This is
          consistent with the SQL:2008 standard requirement for a
          UCS_BASIC collation:
    <span class="quote">
     “
     <span class="quote">
      UCS_BASIC is a collation in which
          the ordering is determined entirely by the Unicode scalar
          values of the characters in the strings being sorted. It is
          applicable to the UCS character repertoire. Since every
          character repertoire is a subset of the UCS repertoire, the
          UCS_BASIC collation is potentially applicable to every
          character set. NOTE 11: The Unicode scalar value of a
          character is its code point treated as an unsigned
          integer.
     </span>
     ”
    </span>
   </p>
   <p>
    If the character set is
    <code class="literal">
     ucs2
    </code>
    , comparison is
          byte-by-byte, but
    <code class="literal">
     ucs2
    </code>
    strings should not
          contain surrogates, anyway.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="charset-unicode-sets-miscellaneous">
       </a>
       Miscellaneous Information
      </h4>
     </div>
    </div>
   </div>
   <p>
    The
    <code class="literal">
     <em class="replaceable">
      <code>
       xxx
      </code>
     </em>
     _general_mysql500_ci
    </code>
    collations preserve the pre-5.1.24 ordering of the original
    <code class="literal">
     <em class="replaceable">
      <code>
       xxx
      </code>
     </em>
     _general_ci
    </code>
    collations and permit upgrades for tables created before MySQL
          5.1.24 (Bug #27877).
   </p>
  </div>
 </div>
 <br/>
</div>
