<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="charset-column">
      </a>
      12.3.5 Column Character Set and Collation
     </h3>
    </div>
   </div>
  </div>
  <p>
   Every
   <span class="quote">
    “
    <span class="quote">
     character
    </span>
    ”
   </span>
   column (that is, a column of 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
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   type, or any synonym) has a
        column character set and a column collation. Column definition
        syntax for
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   and
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   has optional clauses
        for specifying the column character set and collation:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97122512"><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"><em class="replaceable">col_name</em> {<span class="token datatype">CHAR</span> <span class="token operator">|</span> <span class="token datatype">VARCHAR</span> <span class="token operator">|</span> <span class="token datatype">TEXT</span>} <span class="token punctuation">(</span><em class="replaceable">col_length</em><span class="token punctuation">)</span>
    <span class="token punctuation">[</span><span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <em class="replaceable">charset_name</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">COLLATE</span> <em class="replaceable">collation_name</em><span class="token punctuation">]</span></code></pre>
  </div>
  <p>
   These clauses can also be used for
   <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
    <code class="literal">
     ENUM
    </code>
   </a>
   and
   <a class="link" href="set.html" title="13.3.6 The SET Type">
    <code class="literal">
     SET
    </code>
   </a>
   columns:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72996852"><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"><em class="replaceable">col_name</em> {<span class="token datatype">ENUM</span> <span class="token operator">|</span> <span class="token keyword">SET</span>} <span class="token punctuation">(</span><em class="replaceable">val_list</em><span class="token punctuation">)</span>
    <span class="token punctuation">[</span><span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <em class="replaceable">charset_name</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">COLLATE</span> <em class="replaceable">collation_name</em><span class="token punctuation">]</span></code></pre>
  </div>
  <p>
   Examples:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa57541583"><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">VARCHAR</span><span class="token punctuation">(</span><span class="token number">5</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 punctuation">;</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">MODIFY</span>
    col1 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">5</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_swedish_ci<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   MySQL chooses the column character set and collation in the
        following manner:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If both
      <code class="literal">
       CHARACTER SET
       <em class="replaceable">
        <code>
         charset_name
        </code>
       </em>
      </code>
      and
      <code class="literal">
       COLLATE
       <em class="replaceable">
        <code>
         collation_name
        </code>
       </em>
      </code>
      are
            specified, character set
      <em class="replaceable">
       <code>
        charset_name
       </code>
      </em>
      and collation
      <em class="replaceable">
       <code>
        collation_name
       </code>
      </em>
      are used.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11732331"><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> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_unicode_ci
<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_bin<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The character set and collation are specified for the
            column, so they are used. The column has character set
      <code class="literal">
       utf8mb4
      </code>
      and collation
      <code class="literal">
       utf8mb4_unicode_ci
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      If
      <code class="literal">
       CHARACTER SET
       <em class="replaceable">
        <code>
         charset_name
        </code>
       </em>
      </code>
      is
            specified without
      <code class="literal">
       COLLATE
      </code>
      , character set
      <em class="replaceable">
       <code>
        charset_name
       </code>
      </em>
      and its default
            collation are used.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa39689946"><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> utf8mb4
<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_bin<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The character set is specified for the column, but the
            collation is not. The column has character set
      <code class="literal">
       utf8mb4
      </code>
      and the default collation for
      <code class="literal">
       utf8mb4
      </code>
      , which is
      <code class="literal">
       utf8mb4_0900_ai_ci
      </code>
      . To see the default
            collation for each character set, use the
      <a class="link" href="show-character-set.html" title="15.7.7.4 SHOW CHARACTER SET Statement">
       <code class="literal">
        SHOW CHARACTER SET
       </code>
      </a>
      statement
            or query the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      <a class="link" href="information-schema-character-sets-table.html" title="28.3.4 The INFORMATION_SCHEMA CHARACTER_SETS Table">
       <code class="literal">
        CHARACTER_SETS
       </code>
      </a>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      If
      <code class="literal">
       COLLATE
       <em class="replaceable">
        <code>
         collation_name
        </code>
       </em>
      </code>
      is
            specified without
      <code class="literal">
       CHARACTER SET
      </code>
      , the
            character set associated with
      <em class="replaceable">
       <code>
        collation_name
       </code>
      </em>
      and collation
      <em class="replaceable">
       <code>
        collation_name
       </code>
      </em>
      are used.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76361105"><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">COLLATE</span> utf8mb4_polish_ci
<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_bin<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The collation is specified for the column, but the character
            set is not. The column has collation
      <code class="literal">
       utf8mb4_polish_ci
      </code>
      and the character set
            is the one associated with the collation, which is
      <code class="literal">
       utf8mb4
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Otherwise (neither
      <code class="literal">
       CHARACTER SET
      </code>
      nor
      <code class="literal">
       COLLATE
      </code>
      is specified), the table
            character set and collation are used.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa82328898"><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 punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> latin1 <span class="token keyword">COLLATE</span> latin1_bin<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Neither the character set nor collation is specified for the
            column, so the table defaults are used. The column has
            character set
      <code class="literal">
       latin1
      </code>
      and collation
      <code class="literal">
       latin1_bin
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <code class="literal">
    CHARACTER SET
   </code>
   and
   <code class="literal">
    COLLATE
   </code>
   clauses are standard SQL.
  </p>
  <p>
   If you use
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   to convert
        a column from one character set to another, MySQL attempts to
        map the data values, but if the character sets are incompatible,
        there may be data loss.
  </p>
 </div>
 <br/>
</div>
