<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="charset-repertoire">
      </a>
      12.2.1 Character Set Repertoire
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045218083200">
  </a>
  <a class="indexterm" name="idm46045218081744">
  </a>
  <a class="indexterm" name="idm46045218080256">
  </a>
  <a class="indexterm" name="idm46045218078768">
  </a>
  <p>
   The
   <span class="firstterm">
    repertoire
   </span>
   of a
        character set is the collection of characters in the set.
  </p>
  <p>
   String expressions have a repertoire attribute, which can have
        two values:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       ASCII
      </code>
      : The expression can contain only
            ASCII characters; that is, characters in the Unicode range
      <code class="literal">
       U+0000
      </code>
      to
      <code class="literal">
       U+007F
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       UNICODE
      </code>
      : The expression can contain
            characters in the Unicode range
      <code class="literal">
       U+0000
      </code>
      to
      <code class="literal">
       U+10FFFF
      </code>
      . This includes characters in the
            Basic Multilingual Plane (BMP) range
            (
      <code class="literal">
       U+0000
      </code>
      to
      <code class="literal">
       U+FFFF
      </code>
      ) and
            supplementary characters outside the BMP range
            (
      <code class="literal">
       U+10000
      </code>
      to
      <code class="literal">
       U+10FFFF
      </code>
      ).
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <code class="literal">
    ASCII
   </code>
   range is a subset of
   <code class="literal">
    UNICODE
   </code>
   range, so a string with
   <code class="literal">
    ASCII
   </code>
   repertoire can be converted safely
        without loss of information to the character set of any string
        with
   <code class="literal">
    UNICODE
   </code>
   repertoire. It can also be
        converted safely to any character set that is a superset of the
   <code class="literal">
    ascii
   </code>
   character set. (All MySQL character
        sets are supersets of
   <code class="literal">
    ascii
   </code>
   with the
        exception of
   <code class="literal">
    swe7
   </code>
   , which reuses some
        punctuation characters for Swedish accented characters.)
  </p>
  <p>
   The use of repertoire enables character set conversion in
        expressions for many cases where MySQL would otherwise return an
   <span class="quote">
    “
    <span class="quote">
     illegal mix of collations
    </span>
    ”
   </span>
   error when the rules
        for collation coercibility are insufficient to resolve
        ambiguities. (For information about coercibility, see
   <a class="xref" href="charset-collation-coercibility.html" title="12.8.4 Collation Coercibility in Expressions">
    Section 12.8.4, “Collation Coercibility in Expressions”
   </a>
   .)
  </p>
  <p>
   The following discussion provides examples of expressions and
        their repertoires, and describes how the use of repertoire
        changes string expression evaluation:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The repertoire for a string constant depends on string
            content and may differ from the repertoire of the string
            character set. Consider these statements:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa60377236"><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">SET</span> <span class="token keyword">NAMES</span> utf8mb4<span class="token punctuation">;</span> <span class="token keyword">SELECT</span> <span class="token string">'abc'</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> _utf8mb4<span class="token string">'def'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Although the character set is
      <code class="literal">
       utf8mb4
      </code>
      in
            each of the preceding cases, the strings do not actually
            contain any characters outside the ASCII range, so their
            repertoire is
      <code class="literal">
       ASCII
      </code>
      rather than
      <code class="literal">
       UNICODE
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      A column having the
      <code class="literal">
       ascii
      </code>
      character set
            has
      <code class="literal">
       ASCII
      </code>
      repertoire because of its
            character set. In the following table,
      <code class="literal">
       c1
      </code>
      has
      <code class="literal">
       ASCII
      </code>
      repertoire:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa65011247"><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> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> ascii<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The following example illustrates how repertoire enables a
            result to be determined in a case where an error occurs
            without repertoire:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40017395"><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> t1 <span class="token punctuation">(</span>
  c1 <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> latin1<span class="token punctuation">,</span>
  c2 <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> ascii
<span class="token punctuation">)</span><span class="token punctuation">;</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 string">'b'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span>c1<span class="token punctuation">,</span>c2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Without repertoire, this error occurs:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa97760120"><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">ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (ascii_general_ci,IMPLICIT) for operation 'concat'</code></pre>
     </div>
     <p>
      Using repertoire, subset to superset
            (
      <code class="literal">
       ascii
      </code>
      to
      <code class="literal">
       latin1
      </code>
      )
            conversion can occur and a result is returned:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa95460253"><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-simple"><span class="token operator">+</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">-</span><span class="token operator">+</span>
<span class="token operator">|</span> CONCAT<span class="token punctuation">(</span>c1<span class="token punctuation">,</span>c2<span class="token punctuation">)</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">-</span><span class="token operator">+</span>
<span class="token operator">|</span> ab            <span class="token operator">|</span>
<span class="token operator">+</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">-</span><span class="token operator">+</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Functions with one string argument inherit the repertoire of
            their argument. The result of
      <a class="link" href="string-functions.html#function_upper">
       <code class="literal">
        UPPER(_utf8mb4'abc')
       </code>
      </a>
      has
      <code class="literal">
       ASCII
      </code>
      repertoire because its argument has
      <code class="literal">
       ASCII
      </code>
      repertoire. (Despite the
      <code class="literal">
       _utf8mb4
      </code>
      introducer, the string
      <code class="literal">
       'abc'
      </code>
      contains no characters outside the
            ASCII range.)
     </p>
    </li>
    <li class="listitem">
     <p>
      For functions that return a string but do not have string
            arguments and use
      <a class="link" href="server-system-variables.html#sysvar_character_set_connection">
       <code class="literal">
        character_set_connection
       </code>
      </a>
      as
            the result character set, the result repertoire is
      <code class="literal">
       ASCII
      </code>
      if
      <a class="link" href="server-system-variables.html#sysvar_character_set_connection">
       <code class="literal">
        character_set_connection
       </code>
      </a>
      is
      <code class="literal">
       ascii
      </code>
      , and
      <code class="literal">
       UNICODE
      </code>
      otherwise:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa3123511"><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 function">FORMAT</span><span class="token punctuation">(</span><em class="replaceable">numeric_column</em><span class="token punctuation">,</span> <span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Use of repertoire changes how MySQL evaluates the following
            example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa39087744"><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">SET</span> <span class="token keyword">NAMES</span> ascii<span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>a <span class="token datatype">INT</span><span class="token punctuation">,</span> b <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> latin1<span class="token punctuation">)</span><span class="token punctuation">;</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 number">1</span><span class="token punctuation">,</span><span class="token string">'b'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span><span class="token function">FORMAT</span><span class="token punctuation">(</span>a<span class="token punctuation">,</span> <span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">,</span> b<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Without repertoire, this error occurs:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa16859809"><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">ERROR 1267 (HY000): Illegal mix of collations (ascii_general_ci,COERCIBLE)
and (latin1_swedish_ci,IMPLICIT) for operation 'concat'</code></pre>
     </div>
     <p>
      With repertoire, a result is returned:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56447398"><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 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>
<span class="token output"><span class="token punctuation">|</span> CONCAT(FORMAT(a, 4), b) <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>
<span class="token output"><span class="token punctuation">|</span> 1.0000b                 <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></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Functions with two or more string arguments use the
      <span class="quote">
       “
       <span class="quote">
        widest
       </span>
       ”
      </span>
      argument repertoire for the result
            repertoire, where
      <code class="literal">
       UNICODE
      </code>
      is wider than
      <code class="literal">
       ASCII
      </code>
      . Consider the following
      <a class="link" href="string-functions.html#function_concat">
       <code class="literal">
        CONCAT()
       </code>
      </a>
      calls:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa23933692"><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 function">CONCAT</span><span class="token punctuation">(</span>_ucs2 X<span class="token string">'0041'</span><span class="token punctuation">,</span> _ucs2 X<span class="token string">'0042'</span><span class="token punctuation">)</span>
<span class="token function">CONCAT</span><span class="token punctuation">(</span>_ucs2 X<span class="token string">'0041'</span><span class="token punctuation">,</span> _ucs2 X<span class="token string">'00C2'</span><span class="token punctuation">)</span></code></pre>
     </div>
     <p>
      For the first call, the repertoire is
      <code class="literal">
       ASCII
      </code>
      because both arguments are within
            the ASCII range. For the second call, the repertoire is
      <code class="literal">
       UNICODE
      </code>
      because the second argument is
            outside the ASCII range.
     </p>
    </li>
    <li class="listitem">
     <p>
      The repertoire for function return values is determined
            based on the repertoire of only those arguments that affect
            the result's character set and collation.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa67789889"><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 function">IF</span><span class="token punctuation">(</span>column1 <span class="token operator">&lt;</span> column2<span class="token punctuation">,</span> <span class="token string">'smaller'</span><span class="token punctuation">,</span> <span class="token string">'greater'</span><span class="token punctuation">)</span></code></pre>
     </div>
     <p>
      The result repertoire is
      <code class="literal">
       ASCII
      </code>
      because
            the two string arguments (the second argument and the third
            argument) both have
      <code class="literal">
       ASCII
      </code>
      repertoire. The
            first argument does not matter for the result repertoire,
            even if the expression uses string values.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
