<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="charset-collation-effect">
      </a>
      12.8.6 Examples of the Effect of Collation
     </h3>
    </div>
   </div>
  </div>
  <p>
   <span class="bold">
    <strong>
     Example 1: Sorting German
        Umlauts
    </strong>
   </span>
  </p>
  <p>
   Suppose that column
   <code class="literal">
    X
   </code>
   in table
   <code class="literal">
    T
   </code>
   has these
   <code class="literal">
    latin1
   </code>
   column
        values:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa37754468"><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">Muffler
Müller
MX Systems
MySQL</code></pre>
  </div>
  <p>
   Suppose also that the column values are retrieved using the
        following statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa71823285"><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">SELECT</span> X <span class="token keyword">FROM</span> T <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> X <span class="token keyword">COLLATE</span> <em class="replaceable">collation_name</em><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The following table shows the resulting order of the values if
        we use
   <code class="literal">
    ORDER BY
   </code>
   with different collations.
  </p>
  <div class="informaltable">
   <table summary="An example of the effect of collation, as described in the preceding text. The table shows the resulting order of values for three collations (latin1_swedish_ci, latin1_german1_ci, latin1_german2_ci) when using ORDER BY.">
    <colgroup>
     <col style="width: 30%"/>
     <col style="width: 30%"/>
     <col style="width: 30%"/>
    </colgroup>
    <thead>
     <tr>
      <th scope="col">
       <code class="literal">
        latin1_swedish_ci
       </code>
      </th>
      <th scope="col">
       <code class="literal">
        latin1_german1_ci
       </code>
      </th>
      <th scope="col">
       <code class="literal">
        latin1_german2_ci
       </code>
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <th scope="row">
       Muffler
      </th>
      <td>
       Muffler
      </td>
      <td>
       Müller
      </td>
     </tr>
     <tr>
      <th scope="row">
       MX Systems
      </th>
      <td>
       Müller
      </td>
      <td>
       Muffler
      </td>
     </tr>
     <tr>
      <th scope="row">
       Müller
      </th>
      <td>
       MX Systems
      </td>
      <td>
       MX Systems
      </td>
     </tr>
     <tr>
      <th scope="row">
       MySQL
      </th>
      <td>
       MySQL
      </td>
      <td>
       MySQL
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   The character that causes the different sort orders in this
        example is
   <code class="literal">
    ü
   </code>
   (German
   <span class="quote">
    “
    <span class="quote">
     U-umlaut
    </span>
    ”
   </span>
   ).
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The first column shows the result of the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      using the
            Swedish/Finnish collating rule, which says that U-umlaut
            sorts with Y.
     </p>
    </li>
    <li class="listitem">
     <p>
      The second column shows the result of the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      using the German DIN-1
            rule, which says that U-umlaut sorts with U.
     </p>
    </li>
    <li class="listitem">
     <p>
      The third column shows the result of the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      using the German DIN-2
            rule, which says that U-umlaut sorts with UE.
     </p>
    </li>
   </ul>
  </div>
  <p>
   <span class="bold">
    <strong>
     Example 2: Searching for German
        Umlauts
    </strong>
   </span>
  </p>
  <p>
   Suppose that you have three tables that differ only by the
        character set and collation used:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa52958409"><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">SET</span> <span class="token keyword">NAMES</span> utf8mb4<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> german1 <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 punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> latin1 <span class="token keyword">COLLATE</span> latin1_german1_ci<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> german2 <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 punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> latin1 <span class="token keyword">COLLATE</span> latin1_german2_ci<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> germanutf8 <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 punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_unicode_ci<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Each table contains two records:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22901894"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> german1 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'Bar'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">'Bär'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> german2 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'Bar'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">'Bär'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> germanutf8 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'Bar'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">'Bär'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Two of the above collations have an
   <code class="literal">
    A = Ä
   </code>
   equality, and one has no such equality
        (
   <code class="literal">
    latin1_german2_ci
   </code>
   ). For that reason,
        comparisons yield the results shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56464269"><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 operator">*</span> <span class="token keyword">FROM</span> german1 <span class="token keyword">WHERE</span> c <span class="token operator">=</span> <span class="token string">'Bär'</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> Bar  <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Bär  <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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> german2 <span class="token keyword">WHERE</span> c <span class="token operator">=</span> <span class="token string">'Bär'</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> Bär  <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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> germanutf8 <span class="token keyword">WHERE</span> c <span class="token operator">=</span> <span class="token string">'Bär'</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> Bar  <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Bär  <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></code></pre>
  </div>
  <p>
   This is not a bug but rather a consequence of the sorting
        properties of
   <code class="literal">
    latin1_german1_ci
   </code>
   and
   <code class="literal">
    utf8mb4_unicode_ci
   </code>
   (the sorting shown is done
        according to the German DIN 5007 standard).
  </p>
 </div>
 <br/>
</div>
