<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="charset-conversion">
      </a>
      12.7 Column Character Set Conversion
     </h2>
    </div>
   </div>
  </div>
  <p>
   To convert a binary or nonbinary string column to use a particular
      character set, use
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   . For
      successful conversion to occur, one of the following conditions
      must apply:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If the column has a binary data type
          (
      <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
       <code class="literal">
        BINARY
       </code>
      </a>
      ,
      <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
       <code class="literal">
        VARBINARY
       </code>
      </a>
      ,
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      ), all the values that it
          contains must be encoded using a single character set (the
          character set you're converting the column to). If you use a
          binary column to store information in multiple character sets,
          MySQL has no way to know which values use which character set
          and cannot convert the data properly.
     </p>
    </li>
    <li class="listitem">
     <p>
      If the column has a nonbinary data type
          (
      <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>
      ,
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      ), its contents should be
          encoded in the column character set, not some other character
          set. If the contents are encoded in a different character set,
          you can convert the column to use a binary data type first,
          and then to a nonbinary column with the desired character set.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Suppose that a table
   <code class="literal">
    t
   </code>
   has a binary column
      named
   <code class="literal">
    col1
   </code>
   defined as
   <code class="literal">
    VARBINARY(50)
   </code>
   . Assuming that the information in
      the column is encoded using a single character set, you can
      convert it to a nonbinary column that has that character set. For
      example, if
   <code class="literal">
    col1
   </code>
   contains binary data
      representing characters in the
   <code class="literal">
    greek
   </code>
   character
      set, you can convert it as follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa93937232"><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> t <span class="token keyword">MODIFY</span> col1 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> greek<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If your original column has a type of
   <code class="literal">
    BINARY(50)
   </code>
   , you could convert it to
   <code class="literal">
    CHAR(50)
   </code>
   , but the resulting values are padded
      with
   <code class="literal">
    0x00
   </code>
   bytes at the end, which may be
      undesirable. To remove these bytes, use the
   <a class="link" href="string-functions.html#function_trim">
    <code class="literal">
     TRIM()
    </code>
   </a>
   function:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa36837779"><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">UPDATE</span> t <span class="token keyword">SET</span> col1 <span class="token operator">=</span> <span class="token function">TRIM</span><span class="token punctuation">(</span><span class="token keyword">TRAILING</span> <span class="token number">0x00</span> <span class="token keyword">FROM</span> col1<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Suppose that table
   <code class="literal">
    t
   </code>
   has a nonbinary column
      named
   <code class="literal">
    col1
   </code>
   defined as
   <code class="literal">
    CHAR(50)
      CHARACTER SET latin1
   </code>
   but you want to convert it to use
   <code class="literal">
    utf8mb4
   </code>
   so that you can store values from many
      languages. The following statement accomplishes this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa59473875"><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> t <span class="token keyword">MODIFY</span> col1 <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Conversion may be lossy if the column contains characters that are
      not in both character sets.
  </p>
  <p>
   A special case occurs if you have old tables from before MySQL 4.1
      where a nonbinary column contains values that actually are encoded
      in a character set different from the server's default character
      set. For example, an application might have stored
   <code class="literal">
    sjis
   </code>
   values in a column, even though MySQL's
      default character set was different. It is possible to convert the
      column to use the proper character set but an additional step is
      required. Suppose that the server's default character set was
   <code class="literal">
    latin1
   </code>
   and
   <code class="literal">
    col1
   </code>
   is defined
      as
   <code class="literal">
    CHAR(50)
   </code>
   but its contents are
   <code class="literal">
    sjis
   </code>
   values. The first step is to convert the
      column to a binary data type, which removes the existing character
      set information without performing any character conversion:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa7305084"><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> t <span class="token keyword">MODIFY</span> col1 <span class="token datatype">BLOB</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The next step is to convert the column to a nonbinary data type
      with the proper character set:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa28683148"><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> t <span class="token keyword">MODIFY</span> col1 <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> sjis<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This procedure requires that the table not have been modified
      already with statements such as
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   or
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   after an upgrade to MySQL
      4.1 or higher. In that case, MySQL would store new values in the
      column using
   <code class="literal">
    latin1
   </code>
   , and the column would
      contain a mix of
   <code class="literal">
    sjis
   </code>
   and
   <code class="literal">
    latin1
   </code>
   values and cannot be converted properly.
  </p>
  <p>
   If you specified attributes when creating a column initially, you
      should also specify them when altering the table with
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   . For example, if you
      specified
   <code class="literal">
    NOT NULL
   </code>
   and an explicit
   <code class="literal">
    DEFAULT
   </code>
   value, you should also provide them in
      the
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement.
      Otherwise, the resulting column definition does not include those
      attributes.
  </p>
  <p>
   To convert all character columns in a table, the
   <code class="literal">
    ALTER
      TABLE ... CONVERT TO CHARACTER SET
    <em class="replaceable">
     <code>
      charset
     </code>
    </em>
   </code>
   statement may be
      useful. See
   <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    Section 15.1.9, “ALTER TABLE Statement”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
