<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="char">
      </a>
      13.3.2 The CHAR and VARCHAR Types
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045212759184">
  </a>
  <a class="indexterm" name="idm46045212757728">
  </a>
  <a class="indexterm" name="idm46045212756240">
  </a>
  <p>
   The
   <code class="literal">
    CHAR
   </code>
   and
   <code class="literal">
    VARCHAR
   </code>
   types
        are similar, but differ in the way they are stored and
        retrieved. They also differ in maximum length and in whether
        trailing spaces are retained.
  </p>
  <p>
   The
   <code class="literal">
    CHAR
   </code>
   and
   <code class="literal">
    VARCHAR
   </code>
   types
        are declared with a length that indicates the maximum number of
        characters you want to store. For example,
   <code class="literal">
    CHAR(30)
   </code>
   can hold up to 30 characters.
  </p>
  <p>
   The length of a
   <code class="literal">
    CHAR
   </code>
   column is fixed to the
        length that you declare when you create the table. The length
        can be any value from 0 to 255. When
   <code class="literal">
    CHAR
   </code>
   values are stored, they are right-padded with spaces to the
        specified length. When
   <code class="literal">
    CHAR
   </code>
   values are
        retrieved, trailing spaces are removed unless the
   <a class="link" href="sql-mode.html#sqlmode_pad_char_to_full_length">
    <code class="literal">
     PAD_CHAR_TO_FULL_LENGTH
    </code>
   </a>
   SQL
        mode is enabled.
  </p>
  <p>
   Values in
   <code class="literal">
    VARCHAR
   </code>
   columns are variable-length
        strings. The length can be specified as a value from 0 to
        65,535. The effective maximum length of a
   <code class="literal">
    VARCHAR
   </code>
   is subject to the maximum row size
        (65,535 bytes, which is shared among all columns) and the
        character set used. See
   <a class="xref" href="column-count-limit.html" title="10.4.7 Limits on Table Column Count and Row Size">
    Section 10.4.7, “Limits on Table Column Count and Row Size”
   </a>
   .
  </p>
  <p>
   In contrast to
   <code class="literal">
    CHAR
   </code>
   ,
   <code class="literal">
    VARCHAR
   </code>
   values are stored as a 1-byte or
        2-byte length prefix plus data. The length prefix indicates the
        number of bytes in the value. A column uses one length byte if
        values require no more than 255 bytes, two length bytes if
        values may require more than 255 bytes.
  </p>
  <p>
   If strict SQL mode is not enabled and you assign a value to a
   <code class="literal">
    CHAR
   </code>
   or
   <code class="literal">
    VARCHAR
   </code>
   column
        that exceeds the column's maximum length, the value is truncated
        to fit and a warning is generated. For truncation of nonspace
        characters, you can cause an error to occur (rather than a
        warning) and suppress insertion of the value by using strict SQL
        mode. See
   <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
    Section 7.1.11, “Server SQL Modes”
   </a>
   .
  </p>
  <p>
   For
   <code class="literal">
    VARCHAR
   </code>
   columns, trailing spaces in
        excess of the column length are truncated prior to insertion and
        a warning is generated, regardless of the SQL mode in use. For
   <code class="literal">
    CHAR
   </code>
   columns, truncation of excess trailing
        spaces from inserted values is performed silently regardless of
        the SQL mode.
  </p>
  <p>
   <code class="literal">
    VARCHAR
   </code>
   values are not padded when they are
        stored. Trailing spaces are retained when values are stored and
        retrieved, in conformance with standard SQL.
  </p>
  <p>
   The following table illustrates the differences between
   <code class="literal">
    CHAR
   </code>
   and
   <code class="literal">
    VARCHAR
   </code>
   by
        showing the result of storing various string values into
   <code class="literal">
    CHAR(4)
   </code>
   and
   <code class="literal">
    VARCHAR(4)
   </code>
   columns (assuming that the column uses a single-byte character
        set such as
   <code class="literal">
    latin1
   </code>
   ).
  </p>
  <div class="informaltable">
   <table summary="Illustration of the difference between CHAR and VARCHAR storage requirements by showing the required storage for various string values in CHAR(4) and VARCHAR(4) columns.">
    <colgroup>
     <col style="width: 15%"/>
     <col style="width: 15%"/>
     <col style="width: 20%"/>
     <col style="width: 15%"/>
     <col style="width: 20%"/>
    </colgroup>
    <thead>
     <tr>
      <th scope="col">
       Value
      </th>
      <th scope="col">
       <code class="literal">
        CHAR(4)
       </code>
      </th>
      <th scope="col">
       Storage Required
      </th>
      <th scope="col">
       <code class="literal">
        VARCHAR(4)
       </code>
      </th>
      <th scope="col">
       Storage Required
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <th scope="row">
       <code class="literal">
        ''
       </code>
      </th>
      <td>
       <code class="literal">
        '    '
       </code>
      </td>
      <td>
       4 bytes
      </td>
      <td>
       <code class="literal">
        ''
       </code>
      </td>
      <td>
       1 byte
      </td>
     </tr>
     <tr>
      <th scope="row">
       <code class="literal">
        'ab'
       </code>
      </th>
      <td>
       <code class="literal">
        'ab  '
       </code>
      </td>
      <td>
       4 bytes
      </td>
      <td>
       <code class="literal">
        'ab'
       </code>
      </td>
      <td>
       3 bytes
      </td>
     </tr>
     <tr>
      <th scope="row">
       <code class="literal">
        'abcd'
       </code>
      </th>
      <td>
       <code class="literal">
        'abcd'
       </code>
      </td>
      <td>
       4 bytes
      </td>
      <td>
       <code class="literal">
        'abcd'
       </code>
      </td>
      <td>
       5 bytes
      </td>
     </tr>
     <tr>
      <th scope="row">
       <code class="literal">
        'abcdefgh'
       </code>
      </th>
      <td>
       <code class="literal">
        'abcd'
       </code>
      </td>
      <td>
       4 bytes
      </td>
      <td>
       <code class="literal">
        'abcd'
       </code>
      </td>
      <td>
       5 bytes
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   The values shown as stored in the last row of the table apply
   <span class="emphasis">
    <em>
     only when not using strict SQL mode
    </em>
   </span>
   ; if
        strict mode is enabled, values that exceed the column length are
   <span class="emphasis">
    <em>
     not stored
    </em>
   </span>
   , and an error results.
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   encodes fixed-length fields greater
        than or equal to 768 bytes in length as variable-length fields,
        which can be stored off-page. For example, a
   <code class="literal">
    CHAR(255)
   </code>
   column can exceed 768 bytes if the
        maximum byte length of the character set is greater than 3, as
        it is with
   <code class="literal">
    utf8mb4
   </code>
   .
  </p>
  <p>
   If a given value is stored into the
   <code class="literal">
    CHAR(4)
   </code>
   and
   <code class="literal">
    VARCHAR(4)
   </code>
   columns, the values retrieved
        from the columns are not always the same because trailing spaces
        are removed from
   <code class="literal">
    CHAR
   </code>
   columns upon retrieval.
        The following example illustrates this difference:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa57018946"><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">CREATE</span> <span class="token keyword">TABLE</span> vc <span class="token punctuation">(</span>v <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">,</span> c <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> vc <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'ab  '</span><span class="token punctuation">,</span> <span class="token string">'ab  '</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span><span class="token string">'('</span><span class="token punctuation">,</span> v<span class="token punctuation">,</span> <span class="token string">')'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span><span class="token string">'('</span><span class="token punctuation">,</span> c<span class="token punctuation">,</span> <span class="token string">')'</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> vc<span class="token punctuation">;</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 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('(', v, ')') <span class="token punctuation">|</span> CONCAT('(', c, ')') <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 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> (ab  )              <span class="token punctuation">|</span> (ab)                <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 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">1 row in set (0.06 sec)</span></code></pre>
  </div>
  <p>
   Values in
   <code class="literal">
    CHAR
   </code>
   ,
   <code class="literal">
    VARCHAR
   </code>
   ,
        and
   <code class="literal">
    TEXT
   </code>
   columns are sorted and compared
        according to the character set collation assigned to the column.
  </p>
  <a class="indexterm" name="idm46045212678880">
  </a>
  <a class="indexterm" name="idm46045212677392">
  </a>
  <a class="indexterm" name="idm46045212675904">
  </a>
  <a class="indexterm" name="idm46045212674832">
  </a>
  <a class="indexterm" name="idm46045212673344">
  </a>
  <p>
   MySQL collations have a pad attribute of
   <code class="literal">
    PAD
        SPACE
   </code>
   , other than Unicode collations based on UCA
        9.0.0 and higher, which have a pad attribute of
   <code class="literal">
    NO
        PAD
   </code>
   . (see
   <a class="xref" href="charset-unicode-sets.html" title="12.10.1 Unicode Character Sets">
    Section 12.10.1, “Unicode Character Sets”
   </a>
   ).
  </p>
  <p>
   To determine the pad attribute for a collation, use the
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   <a class="link" href="information-schema-collations-table.html" title="28.3.6 The INFORMATION_SCHEMA COLLATIONS Table">
    <code class="literal">
     COLLATIONS
    </code>
   </a>
   table, which has a
   <code class="literal">
    PAD_ATTRIBUTE
   </code>
   column.
  </p>
  <p>
   For nonbinary strings (
   <code class="literal">
    CHAR
   </code>
   ,
   <code class="literal">
    VARCHAR
   </code>
   , and
   <code class="literal">
    TEXT
   </code>
   values),
        the string collation pad attribute determines treatment in
        comparisons of trailing spaces at the end of strings.
   <code class="literal">
    NO PAD
   </code>
   collations treat trailing spaces as
        significant in comparisons, like any other character.
   <code class="literal">
    PAD SPACE
   </code>
   collations treat trailing spaces as
        insignificant in comparisons; strings are compared without
        regard to trailing spaces. See
   <a class="xref" href="charset-binary-collations.html#charset-binary-collations-trailing-space-comparisons" title="Trailing Space Handling in Comparisons">
    Trailing Space Handling in Comparisons
   </a>
   .
        The server SQL mode has no effect on comparison behavior with
        respect to trailing spaces.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    For more information about MySQL character sets and
          collations, see
    <a class="xref" href="charset.html" title="Chapter 12 Character Sets, Collations, Unicode">
     Chapter 12,
     <i>
      Character Sets, Collations, Unicode
     </i>
    </a>
    . For additional
          information about storage requirements, see
    <a class="xref" href="storage-requirements.html" title="13.7 Data Type Storage Requirements">
     Section 13.7, “Data Type Storage Requirements”
    </a>
    .
   </p>
  </div>
  <p>
   For those cases where trailing pad characters are stripped or
        comparisons ignore them, if a column has an index that requires
        unique values, inserting into the column values that differ only
        in number of trailing pad characters results in a duplicate-key
        error. For example, if a table contains
   <code class="literal">
    'a'
   </code>
   ,
        an attempt to store
   <code class="literal">
    'a '
   </code>
   causes a
        duplicate-key error.
  </p>
 </div>
 <br/>
</div>
