<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="charset-collation-coercibility">
      </a>
      12.8.4 Collation Coercibility in Expressions
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045217092592">
  </a>
  <a class="indexterm" name="idm46045217091104">
  </a>
  <p>
   In the great majority of statements, it is obvious what
        collation MySQL uses to resolve a comparison operation. For
        example, in the following cases, it should be clear that the
        collation is the collation of column
   <code class="literal">
    x
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92754765"><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 punctuation">;</span>
<span class="token keyword">SELECT</span> x <span class="token keyword">FROM</span> T <span class="token keyword">WHERE</span> x <span class="token operator">=</span> x<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> x <span class="token keyword">FROM</span> T<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   However, with multiple operands, there can be ambiguity. For
        example, this statement performs a comparison between the column
   <code class="literal">
    x
   </code>
   and the string literal
   <code class="literal">
    'Y'
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa92406400"><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">WHERE</span> x <span class="token operator">=</span> <span class="token string">'Y'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If
   <code class="literal">
    x
   </code>
   and
   <code class="literal">
    'Y'
   </code>
   have the same
        collation, there is no ambiguity about the collation to use for
        the comparison. But if they have different collations, should
        the comparison use the collation of
   <code class="literal">
    x
   </code>
   , or of
   <code class="literal">
    'Y'
   </code>
   ? Both
   <code class="literal">
    x
   </code>
   and
   <code class="literal">
    'Y'
   </code>
   have collations, so which collation takes
        precedence?
  </p>
  <p>
   A mix of collations may also occur in contexts other than
        comparison. For example, a multiple-argument concatenation
        operation such as
   <a class="link" href="string-functions.html#function_concat">
    <code class="literal">
     CONCAT(x,'Y')
    </code>
   </a>
   combines its arguments to produce a single string. What
        collation should the result have?
  </p>
  <p>
   To resolve questions like these, MySQL checks whether the
        collation of one item can be coerced to the collation of the
        other. MySQL assigns coercibility values as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      An explicit
      <code class="literal">
       COLLATE
      </code>
      clause has a
            coercibility of 0 (not coercible at all).
     </p>
    </li>
    <li class="listitem">
     <p>
      The concatenation of two strings with different collations
            has a coercibility of 1.
     </p>
    </li>
    <li class="listitem">
     <p>
      The collation of a column or a stored routine parameter or
            local variable has a coercibility of 2.
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <span class="quote">
       “
       <span class="quote">
        system constant
       </span>
       ”
      </span>
      (the string returned by
            functions such as
      <a class="link" href="information-functions.html#function_user">
       <code class="literal">
        USER()
       </code>
      </a>
      or
      <a class="link" href="information-functions.html#function_version">
       <code class="literal">
        VERSION()
       </code>
      </a>
      ) has a coercibility
            of 3.
     </p>
    </li>
    <li class="listitem">
     <p>
      The collation of a literal has a coercibility of 4.
     </p>
    </li>
    <li class="listitem">
     <p>
      The collation of a numeric or temporal value has a
            coercibility of 5.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NULL
      </code>
      or an expression that is derived
            from
      <code class="literal">
       NULL
      </code>
      has a coercibility of 6.
     </p>
    </li>
   </ul>
  </div>
  <p>
   MySQL uses coercibility values with the following rules to
        resolve ambiguities:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Use the collation with the lowest coercibility value.
     </p>
    </li>
    <li class="listitem">
     <p>
      If both sides have the same coercibility, then:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         If both sides are Unicode, or both sides are not
                Unicode, it is an error.
        </p>
       </li>
       <li class="listitem">
        <p>
         If one of the sides has a Unicode character set, and
                another side has a non-Unicode character set, the side
                with Unicode character set wins, and automatic character
                set conversion is applied to the non-Unicode side. For
                example, the following statement does not return an
                error:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa73424587"><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> <span class="token function">CONCAT</span><span class="token punctuation">(</span>utf8mb4_column<span class="token punctuation">,</span> latin1_column<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
        </div>
        <p>
         It returns a result that has a character set of
         <code class="literal">
          utf8mb4
         </code>
         and the same collation as
         <code class="literal">
          utf8mb4_column
         </code>
         . Values of
         <code class="literal">
          latin1_column
         </code>
         are automatically
                converted to
         <code class="literal">
          utf8mb4
         </code>
         before
                concatenating.
        </p>
       </li>
       <li class="listitem">
        <p>
         For an operation with operands from the same character
                set but that mix a
         <code class="literal">
          _bin
         </code>
         collation and
                a
         <code class="literal">
          _ci
         </code>
         or
         <code class="literal">
          _cs
         </code>
         collation, the
         <code class="literal">
          _bin
         </code>
         collation is
                used. This is similar to how operations that mix
                nonbinary and binary strings evaluate the operands as
                binary strings, applied to collations rather than data
                types.
        </p>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Although automatic conversion is not in the SQL standard, the
        standard does say that every character set is (in terms of
        supported characters) a
   <span class="quote">
    “
    <span class="quote">
     subset
    </span>
    ”
   </span>
   of Unicode.
        Because it is a well-known principle that
   <span class="quote">
    “
    <span class="quote">
     what applies to
        a superset can apply to a subset,
    </span>
    ”
   </span>
   we believe that a
        collation for Unicode can apply for comparisons with non-Unicode
        strings. More generally, MySQL uses the concept of character set
        repertoire, which can sometimes be used to determine subset
        relationships among character sets and enable conversion of
        operands in operations that would otherwise produce an error.
        See
   <a class="xref" href="charset-repertoire.html" title="12.2.1 Character Set Repertoire">
    Section 12.2.1, “Character Set Repertoire”
   </a>
   .
  </p>
  <p>
   The following table illustrates some applications of the
        preceding rules.
  </p>
  <div class="informaltable">
   <table summary="Comparisons and the collation used for each comparison.">
    <colgroup>
     <col style="width: 50%"/>
     <col style="width: 50%"/>
    </colgroup>
    <thead>
     <tr>
      <th>
       Comparison
      </th>
      <th>
       Collation Used
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <td>
       <code class="literal">
        column1 = 'A'
       </code>
      </td>
      <td>
       Use collation of
       <code class="literal">
        column1
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        column1 = 'A' COLLATE x
       </code>
      </td>
      <td>
       Use collation of
       <code class="literal">
        'A' COLLATE x
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        column1 COLLATE x = 'A' COLLATE y
       </code>
      </td>
      <td>
       Error
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   To determine the coercibility of a string expression, use the
   <a class="link" href="information-functions.html#function_coercibility">
    <code class="literal">
     COERCIBILITY()
    </code>
   </a>
   function (see
   <a class="xref" href="information-functions.html" title="14.15 Information Functions">
    Section 14.15, “Information Functions”
   </a>
   ):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa24199754"><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">COERCIBILITY</span><span class="token punctuation">(</span>_utf8mb4<span class="token string">'A'</span> <span class="token keyword">COLLATE</span> utf8mb4_bin<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">0</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">COERCIBILITY</span><span class="token punctuation">(</span><span class="token function">VERSION</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">3</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">COERCIBILITY</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 prompt">        -&gt;</span> <span class="token number">4</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">COERCIBILITY</span><span class="token punctuation">(</span><span class="token number">1000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">5</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">COERCIBILITY</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">6</span></code></pre>
  </div>
  <p>
   For implicit conversion of a numeric or temporal value to a
        string, such as occurs for the argument
   <code class="literal">
    1
   </code>
   in
        the expression
   <a class="link" href="string-functions.html#function_concat">
    <code class="literal">
     CONCAT(1, 'abc')
    </code>
   </a>
   ,
        the result is a character (nonbinary) string that has a
        character set and collation determined by the
   <a class="link" href="server-system-variables.html#sysvar_character_set_connection">
    <code class="literal">
     character_set_connection
    </code>
   </a>
   and
   <a class="link" href="server-system-variables.html#sysvar_collation_connection">
    <code class="literal">
     collation_connection
    </code>
   </a>
   system
        variables. See
   <a class="xref" href="type-conversion.html" title="14.3 Type Conversion in Expression Evaluation">
    Section 14.3, “Type Conversion in Expression Evaluation”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
