<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="charset-unicode-conversion">
      </a>
      12.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets
     </h3>
    </div>
   </div>
  </div>
  <p>
   This section describes issues that you may face when converting
        character data between the
   <code class="literal">
    utf8mb3
   </code>
   and
   <code class="literal">
    utf8mb4
   </code>
   character sets.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    This discussion focuses primarily on converting between
    <code class="literal">
     utf8mb3
    </code>
    and
    <code class="literal">
     utf8mb4
    </code>
    , but
          similar principles apply to converting between the
    <code class="literal">
     ucs2
    </code>
    character set and character sets such
          as
    <code class="literal">
     utf16
    </code>
    or
    <code class="literal">
     utf32
    </code>
    .
   </p>
  </div>
  <p>
   The
   <code class="literal">
    utf8mb3
   </code>
   and
   <code class="literal">
    utf8mb4
   </code>
   character sets differ as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       utf8mb3
      </code>
      supports only characters in the
            Basic Multilingual Plane (BMP).
      <code class="literal">
       utf8mb4
      </code>
      additionally supports supplementary characters that lie
            outside the BMP.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       utf8mb3
      </code>
      uses a maximum of three bytes per
            character.
      <code class="literal">
       utf8mb4
      </code>
      uses a maximum of 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>
    This discussion refers to the
    <code class="literal">
     utf8mb3
    </code>
    and
    <code class="literal">
     utf8mb4
    </code>
    character set names to be explicit
          about referring to 3-byte and 4-byte UTF-8 character set data.
   </p>
  </div>
  <p>
   One advantage of converting from
   <code class="literal">
    utf8mb3
   </code>
   to
   <code class="literal">
    utf8mb4
   </code>
   is that this enables applications to
        use supplementary characters. One tradeoff is that this may
        increase data storage space requirements.
  </p>
  <p>
   In terms of table content, conversion from
   <code class="literal">
    utf8mb3
   </code>
   to
   <code class="literal">
    utf8mb4
   </code>
   presents no problems:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For a BMP character,
      <code class="literal">
       utf8mb4
      </code>
      and
      <code class="literal">
       utf8mb3
      </code>
      have identical storage
            characteristics: same code values, same encoding, same
            length.
     </p>
    </li>
    <li class="listitem">
     <p>
      For a supplementary character,
      <code class="literal">
       utf8mb4
      </code>
      requires four bytes to store it, whereas
      <code class="literal">
       utf8mb3
      </code>
      cannot store the character at
            all. When converting
      <code class="literal">
       utf8mb3
      </code>
      columns to
      <code class="literal">
       utf8mb4
      </code>
      , you need not worry about
            converting supplementary characters because there are none.
     </p>
    </li>
   </ul>
  </div>
  <p>
   In terms of table structure, these are the primary potential
        incompatibilities:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For the variable-length character data types
            (
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      and the
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      types), the maximum
            permitted length in characters is less for
      <code class="literal">
       utf8mb4
      </code>
      columns than for
      <code class="literal">
       utf8mb3
      </code>
      columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      For all character data types
            (
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        CHAR
       </code>
      </a>
      ,
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      , and the
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      types), the maximum
            number of characters that can be indexed is less for
      <code class="literal">
       utf8mb4
      </code>
      columns than for
      <code class="literal">
       utf8mb3
      </code>
      columns.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Consequently, to convert tables from
   <code class="literal">
    utf8mb3
   </code>
   to
   <code class="literal">
    utf8mb4
   </code>
   , it may be necessary to change
        some column or index definitions.
  </p>
  <p>
   Tables can be converted from
   <code class="literal">
    utf8mb3
   </code>
   to
   <code class="literal">
    utf8mb4
   </code>
   by using
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
        TABLE
    </code>
   </a>
   . Suppose that a table has this definition:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93841230"><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>
  col1 <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">CHARACTER</span> <span class="token keyword">SET</span> utf8mb3 <span class="token keyword">COLLATE</span> utf8mb3_unicode_ci <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  col2 <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">CHARACTER</span> <span class="token keyword">SET</span> utf8mb3 <span class="token keyword">COLLATE</span> utf8mb3_bin <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb3<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The following statement converts
   <code class="literal">
    t1
   </code>
   to use
   <code class="literal">
    utf8mb4
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13249982"><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">ALTER</span> <span class="token keyword">TABLE</span> t1
  <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4<span class="token punctuation">,</span>
  <span class="token keyword">MODIFY</span> col1 <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">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_unicode_ci <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  <span class="token keyword">MODIFY</span> col2 <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">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_bin <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The catch when converting from
   <code class="literal">
    utf8mb3
   </code>
   to
   <code class="literal">
    utf8mb4
   </code>
   is that the maximum length of a
        column or index key is unchanged in terms of
   <span class="emphasis">
    <em>
     bytes
    </em>
   </span>
   . Therefore, it is smaller in terms of
   <span class="emphasis">
    <em>
     characters
    </em>
   </span>
   because the maximum length of a
        character is four bytes instead of three. For the
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     CHAR
    </code>
   </a>
   ,
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   , and
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   data types, watch for these
        issues when converting your MySQL tables:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Check all definitions of
      <code class="literal">
       utf8mb3
      </code>
      columns
            and make sure they do not exceed the maximum length for the
            storage engine.
     </p>
    </li>
    <li class="listitem">
     <p>
      Check all indexes on
      <code class="literal">
       utf8mb3
      </code>
      columns and
            make sure they do not exceed the maximum length for the
            storage engine. Sometimes the maximum can change due to
            storage engine enhancements.
     </p>
    </li>
   </ul>
  </div>
  <p>
   If the preceding conditions apply, you must either reduce the
        defined length of columns or indexes, or continue to use
   <code class="literal">
    utf8mb3
   </code>
   rather than
   <code class="literal">
    utf8mb4
   </code>
   .
  </p>
  <p>
   Here are some examples where structural changes may be needed:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TINYTEXT
       </code>
      </a>
      column can hold up
            to 255 bytes, so it can hold up to 85 3-byte or 63 4-byte
            characters. Suppose that you have a
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TINYTEXT
       </code>
      </a>
      column that uses
      <code class="literal">
       utf8mb3
      </code>
      but must be able to contain more
            than 63 characters. You cannot convert it to
      <code class="literal">
       utf8mb4
      </code>
      unless you also change the data
            type to a longer type such as
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      .
     </p>
     <p>
      Similarly, a very long
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      column may need to be
            changed to one of the longer
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      types if you want to
            convert it from
      <code class="literal">
       utf8mb3
      </code>
      to
      <code class="literal">
       utf8mb4
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       InnoDB
      </code>
      has a maximum index length of 767
            bytes for tables that use
      <code class="literal">
       <a class="link" href="glossary.html#glos_compact_row_format" title="compact row format">
        COMPACT
       </a>
      </code>
      or
      <code class="literal">
       <a class="link" href="glossary.html#glos_redundant_row_format" title="redundant row format">
        REDUNDANT
       </a>
      </code>
      row format, so for
      <code class="literal">
       utf8mb3
      </code>
      or
      <code class="literal">
       utf8mb4
      </code>
      columns, you can index a maximum
            of 255 or 191 characters, respectively. If you currently
            have
      <code class="literal">
       utf8mb3
      </code>
      columns with indexes longer
            than 191 characters, you must index a smaller number of
            characters.
     </p>
     <p>
      In an
      <code class="literal">
       InnoDB
      </code>
      table that uses
      <code class="literal">
       <a class="link" href="glossary.html#glos_compact_row_format" title="compact row format">
        COMPACT
       </a>
      </code>
      or
      <code class="literal">
       <a class="link" href="glossary.html#glos_redundant_row_format" title="redundant row format">
        REDUNDANT
       </a>
      </code>
      row format, these column and index definitions are legal:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa28037365"><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">col1 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">500</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb3<span class="token punctuation">,</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>col1<span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span><span class="token punctuation">)</span></code></pre>
     </div>
     <p>
      To use
      <code class="literal">
       utf8mb4
      </code>
      instead, the index must be
            smaller:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa69221948"><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">col1 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">500</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4<span class="token punctuation">,</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>col1<span class="token punctuation">(</span><span class="token number">191</span><span class="token punctuation">)</span><span class="token punctuation">)</span></code></pre>
     </div>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       For
       <code class="literal">
        InnoDB
       </code>
       tables that use
       <code class="literal">
        <a class="link" href="glossary.html#glos_compressed_row_format" title="compressed row format">
         COMPRESSED
        </a>
       </code>
       or
       <code class="literal">
        <a class="link" href="glossary.html#glos_dynamic_row_format" title="dynamic row format">
         DYNAMIC
        </a>
       </code>
       row format,
       <a class="link" href="glossary.html#glos_index_prefix" title="index prefix">
        index key
              prefixes
       </a>
       longer than 767 bytes (up to 3072 bytes)
              are permitted. Tables created with these row formats
              enable you to index a maximum of 1024 or 768 characters
              for
       <code class="literal">
        utf8mb3
       </code>
       or
       <code class="literal">
        utf8mb4
       </code>
       columns, respectively. For
              related information, see
       <a class="xref" href="innodb-limits.html" title="17.21 InnoDB Limits">
        Section 17.21, “InnoDB Limits”
       </a>
       ,
              and
       <a class="xref" href="innodb-row-format.html#innodb-row-format-dynamic" title="DYNAMIC Row Format">
        DYNAMIC Row Format
       </a>
       .
      </p>
     </div>
    </li>
   </ul>
  </div>
  <p>
   The preceding types of changes are most likely to be required
        only if you have very long columns or indexes. Otherwise, you
        should be able to convert your tables from
   <code class="literal">
    utf8mb3
   </code>
   to
   <code class="literal">
    utf8mb4
   </code>
   without
        problems, using
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   as
        described previously.
  </p>
  <p>
   The following items summarize other potential incompatibilities:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       SET NAMES 'utf8mb4'
      </code>
      causes use of the
            4-byte character set for connection character sets. As long
            as no 4-byte characters are sent from the server, there
            should be no problems. Otherwise, applications that expect
            to receive a maximum of three bytes per character may have
            problems. Conversely, applications that expect to send
            4-byte characters must ensure that the server understands
            them.
     </p>
    </li>
    <li class="listitem">
     <p>
      For replication, if character sets that support
            supplementary characters are to be used on the source, all
            replicas must understand them as well.
     </p>
     <p>
      Also, keep in mind the general principle that if a table has
            different definitions on the source and replica, this can
            lead to unexpected results. For example, the differences in
            maximum index key length make it risky to use
      <code class="literal">
       utf8mb3
      </code>
      on the source and
      <code class="literal">
       utf8mb4
      </code>
      on the replica.
     </p>
    </li>
   </ul>
  </div>
  <p>
   If you have converted to
   <code class="literal">
    utf8mb4
   </code>
   ,
   <code class="literal">
    utf16
   </code>
   ,
   <code class="literal">
    utf16le
   </code>
   , or
   <code class="literal">
    utf32
   </code>
   , and then decide to convert back to
   <code class="literal">
    utf8mb3
   </code>
   or
   <code class="literal">
    ucs2
   </code>
   (for
        example, to downgrade to an older version of MySQL), these
        considerations apply:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       utf8mb3
      </code>
      and
      <code class="literal">
       ucs2
      </code>
      data
            should present no problems.
     </p>
    </li>
    <li class="listitem">
     <p>
      The server must be recent enough to recognize definitions
            referring to the character set from which you are
            converting.
     </p>
    </li>
    <li class="listitem">
     <p>
      For object definitions that refer to the
      <code class="literal">
       utf8mb4
      </code>
      character set, you can dump them
            with
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      prior to downgrading, edit
            the dump file to change instances of
      <code class="literal">
       utf8mb4
      </code>
      to
      <code class="literal">
       utf8
      </code>
      , and
            reload the file in the older server, as long as there are no
            4-byte characters in the data. The older server sees
      <code class="literal">
       utf8
      </code>
      in the dump file object definitions
            and create new objects that use the (3-byte)
      <code class="literal">
       utf8
      </code>
      character set.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
