<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="storage-requirements">
      </a>
      13.7 Data Type Storage Requirements
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045210772800">
  </a>
  <a class="indexterm" name="idm46045210771344">
  </a>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="storage-requirements.html#data-types-storage-reqs-innodb" title="InnoDB Table Storage Requirements">
       InnoDB Table Storage Requirements
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="storage-requirements.html#data-types-storage-reqs-ndb" title="NDB Table Storage Requirements">
       NDB Table Storage Requirements
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="storage-requirements.html#data-types-storage-reqs-numeric" title="Numeric Type Storage Requirements">
       Numeric Type Storage Requirements
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="storage-requirements.html#data-types-storage-reqs-date-time" title="Date and Time Type Storage Requirements">
       Date and Time Type Storage Requirements
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="storage-requirements.html#data-types-storage-reqs-strings" title="String Type Storage Requirements">
       String Type Storage Requirements
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="storage-requirements.html#data-types-storage-reqs-gis" title="Spatial Type Storage Requirements">
       Spatial Type Storage Requirements
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="storage-requirements.html#data-types-storage-reqs-json" title="JSON Storage Requirements">
       JSON Storage Requirements
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   The storage requirements for table data on disk depend on several
      factors. Different storage engines represent data types and store
      raw data differently. Table data might be compressed, either for a
      column or an entire row, complicating the calculation of storage
      requirements for a table or column.
  </p>
  <p>
   Despite differences in storage layout on disk, the internal MySQL
      APIs that communicate and exchange information about table rows
      use a consistent data structure that applies across all storage
      engines.
  </p>
  <p>
   This section includes guidelines and information for the storage
      requirements for each data type supported by MySQL, including the
      internal format and size for storage engines that use a fixed-size
      representation for data types. Information is listed by category
      or storage engine.
  </p>
  <a class="indexterm" name="idm46045210759904">
  </a>
  <p>
   The internal representation of a table has a maximum row size of
      65,535 bytes, even if the storage engine is capable of supporting
      larger rows. This figure excludes
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     BLOB
    </code>
   </a>
   or
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   columns, which contribute only
      9 to 12 bytes toward this size. For
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     BLOB
    </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, the information is
      stored internally in a different area of memory than the row
      buffer. Different storage engines handle the allocation and
      storage of this data in different ways, according to the method
      they use for handling the corresponding types. For more
      information, see
   <a class="xref" href="storage-engines.html" title="Chapter 18 Alternative Storage Engines">
    Chapter 18,
    <i>
     Alternative Storage Engines
    </i>
   </a>
   , and
   <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>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="data-types-storage-reqs-innodb">
       </a>
       InnoDB Table Storage Requirements
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045210750080">
   </a>
   <a class="indexterm" name="idm46045210748592">
   </a>
   <p>
    See
    <a class="xref" href="innodb-row-format.html" title="17.10 InnoDB Row Formats">
     Section 17.10, “InnoDB Row Formats”
    </a>
    for information about
        storage requirements for
    <code class="literal">
     InnoDB
    </code>
    tables.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="data-types-storage-reqs-ndb">
       </a>
       NDB Table Storage Requirements
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045210743968">
   </a>
   <a class="indexterm" name="idm46045210742480">
   </a>
   <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Important
    </div>
    <p>
     <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
      <code class="literal">
       NDB
      </code>
     </a>
     tables use
     <span class="firstterm">
      4-byte alignment
     </span>
     ; all
     <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
      <code class="literal">
       NDB
      </code>
     </a>
     data storage is done in
          multiples of 4 bytes. Thus, a column value that would
          typically take 15 bytes requires 16 bytes in an
     <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
      <code class="literal">
       NDB
      </code>
     </a>
     table. For example, in
     <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
      <code class="literal">
       NDB
      </code>
     </a>
     tables, the
     <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
      <code class="literal">
       TINYINT
      </code>
     </a>
     ,
     <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
      <code class="literal">
       SMALLINT
      </code>
     </a>
     ,
     <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
      <code class="literal">
       MEDIUMINT
      </code>
     </a>
     , and
     <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
      <code class="literal">
       INTEGER
      </code>
     </a>
     (
     <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
      <code class="literal">
       INT
      </code>
     </a>
     ) column types each require
          4 bytes storage per record due to the alignment factor.
    </p>
    <p>
     Each
     <code class="literal">
      BIT(
      <em class="replaceable">
       <code>
        M
       </code>
      </em>
      )
     </code>
     column takes
     <em class="replaceable">
      <code>
       M
      </code>
     </em>
     bits of storage
          space. Although an individual
     <a class="link" href="bit-type.html" title="13.1.5 Bit-Value Type - BIT">
      <code class="literal">
       BIT
      </code>
     </a>
     column is
     <span class="emphasis">
      <em>
       not
      </em>
     </span>
     4-byte aligned,
     <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
      <code class="literal">
       NDB
      </code>
     </a>
     reserves 4 bytes (32 bits)
          per row for the first 1-32 bits needed for
     <code class="literal">
      BIT
     </code>
     columns, then another 4 bytes for bits
          33-64, and so on.
    </p>
    <p>
     While a
     <code class="literal">
      NULL
     </code>
     itself does not require any
          storage space,
     <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
      <code class="literal">
       NDB
      </code>
     </a>
     reserves 4
          bytes per row if the table definition contains any columns
          allowing
     <code class="literal">
      NULL
     </code>
     , up to 32
     <code class="literal">
      NULL
     </code>
     columns. (If an NDB Cluster table is
          defined with more than 32
     <code class="literal">
      NULL
     </code>
     columns up
          to 64
     <code class="literal">
      NULL
     </code>
     columns, then 8 bytes per row
          are reserved.)
    </p>
   </div>
   <p>
    Every table using the
    <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
     <code class="literal">
      NDB
     </code>
    </a>
    storage
        engine requires a primary key; if you do not define a primary
        key, a
    <span class="quote">
     “
     <span class="quote">
      hidden
     </span>
     ”
    </span>
    primary key is created by
    <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
     <code class="literal">
      NDB
     </code>
    </a>
    . This hidden primary key
        consumes 31-35 bytes per table record.
   </p>
   <a class="indexterm" name="idm46045210713776">
   </a>
   <p>
    You can use the
    <a class="link" href="mysql-cluster-programs-ndb-size-pl.html" title="25.5.29 ndb_size.pl — NDBCLUSTER Size Requirement Estimator">
     <span class="command">
      <strong>
       ndb_size.pl
      </strong>
     </span>
    </a>
    Perl script to
        estimate
    <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
     <code class="literal">
      NDB
     </code>
    </a>
    storage requirements.
        It connects to a current MySQL (not NDB Cluster) database and
        creates a report on how much space that database would require
        if it used the
    <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
     <code class="literal">
      NDB
     </code>
    </a>
    storage engine.
        See
    <a class="xref" href="mysql-cluster-programs-ndb-size-pl.html" title="25.5.29 ndb_size.pl — NDBCLUSTER Size Requirement Estimator">
     Section 25.5.29, “ndb_size.pl — NDBCLUSTER Size Requirement Estimator”
    </a>
    for
        more information.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="data-types-storage-reqs-numeric">
       </a>
       Numeric Type Storage Requirements
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045210706288">
   </a>
   <a class="indexterm" name="idm46045210704800">
   </a>
   <div class="informaltable">
    <table summary="Storage required for numeric data types.">
     <colgroup>
      <col style="width: 40%"/>
      <col style="width: 60%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Data Type
       </th>
       <th>
        Storage Required
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
         <code class="literal">
          TINYINT
         </code>
        </a>
       </td>
       <td>
        1 byte
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
         <code class="literal">
          SMALLINT
         </code>
        </a>
       </td>
       <td>
        2 bytes
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
         <code class="literal">
          MEDIUMINT
         </code>
        </a>
       </td>
       <td>
        3 bytes
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
         <code class="literal">
          INT
         </code>
        </a>
        ,
        <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
         <code class="literal">
          INTEGER
         </code>
        </a>
       </td>
       <td>
        4 bytes
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
         <code class="literal">
          BIGINT
         </code>
        </a>
       </td>
       <td>
        8 bytes
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         FLOAT(
         <em class="replaceable">
          <code>
           p
          </code>
         </em>
         )
        </code>
       </td>
       <td>
        4 bytes if 0 &lt;=
        <em class="replaceable">
         <code>
          p
         </code>
        </em>
        &lt;= 24, 8 bytes if 25
              &lt;=
        <em class="replaceable">
         <code>
          p
         </code>
        </em>
        &lt;= 53
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
         <code class="literal">
          FLOAT
         </code>
        </a>
       </td>
       <td>
        4 bytes
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         DOUBLE [PRECISION]
        </code>
        ,
        <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
         <code class="literal">
          REAL
         </code>
        </a>
       </td>
       <td>
        8 bytes
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         DECIMAL(
         <em class="replaceable">
          <code>
           M
          </code>
         </em>
         ,
         <em class="replaceable">
          <code>
           D
          </code>
         </em>
         )
        </code>
        ,
        <code class="literal">
         NUMERIC(
         <em class="replaceable">
          <code>
           M
          </code>
         </em>
         ,
         <em class="replaceable">
          <code>
           D
          </code>
         </em>
         )
        </code>
       </td>
       <td>
        Varies; see following discussion
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         BIT(
         <em class="replaceable">
          <code>
           M
          </code>
         </em>
         )
        </code>
       </td>
       <td>
        approximately (
        <em class="replaceable">
         <code>
          M
         </code>
        </em>
        +7)/8 bytes
       </td>
      </tr>
     </tbody>
    </table>
   </div>
   <div class="informaltable">
    <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 539px;">
     <thead>
      <tr>
       <th style="width: 215.312px;">
        Data Type
       </th>
       <th style="width: 322.969px;">
        Storage Required
       </th>
      </tr>
     </thead>
    </table>
   </div>
   <p>
    Values for
    <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
     <code class="literal">
      DECIMAL
     </code>
    </a>
    (and
    <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
     <code class="literal">
      NUMERIC
     </code>
    </a>
    ) columns are represented
        using a binary format that packs nine decimal (base 10) digits
        into four bytes. Storage for the integer and fractional parts of
        each value are determined separately. Each multiple of nine
        digits requires four bytes, and the
    <span class="quote">
     “
     <span class="quote">
      leftover
     </span>
     ”
    </span>
    digits require some fraction of four bytes. The storage required
        for excess digits is given by the following table.
   </p>
   <div class="informaltable">
    <table summary="Storage required by excess/leftover digits in DECIMAL values.">
     <colgroup>
      <col style="width: 25%"/>
      <col style="width: 25%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Leftover Digits
       </th>
       <th>
        Number of Bytes
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        0
       </td>
       <td>
        0
       </td>
      </tr>
      <tr>
       <td>
        1
       </td>
       <td>
        1
       </td>
      </tr>
      <tr>
       <td>
        2
       </td>
       <td>
        1
       </td>
      </tr>
      <tr>
       <td>
        3
       </td>
       <td>
        2
       </td>
      </tr>
      <tr>
       <td>
        4
       </td>
       <td>
        2
       </td>
      </tr>
      <tr>
       <td>
        5
       </td>
       <td>
        3
       </td>
      </tr>
      <tr>
       <td>
        6
       </td>
       <td>
        3
       </td>
      </tr>
      <tr>
       <td>
        7
       </td>
       <td>
        4
       </td>
      </tr>
      <tr>
       <td>
        8
       </td>
       <td>
        4
       </td>
      </tr>
     </tbody>
    </table>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="data-types-storage-reqs-date-time">
       </a>
       Date and Time Type Storage Requirements
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045210633312">
   </a>
   <a class="indexterm" name="idm46045210631824">
   </a>
   <a class="indexterm" name="idm46045210630336">
   </a>
   <a class="indexterm" name="idm46045210628848">
   </a>
   <p>
    For
    <a class="link" href="time.html" title="13.2.3 The TIME Type">
     <code class="literal">
      TIME
     </code>
    </a>
    ,
    <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
     <code class="literal">
      DATETIME
     </code>
    </a>
    , and
    <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
     <code class="literal">
      TIMESTAMP
     </code>
    </a>
    columns, the storage
        required for tables created before MySQL 5.6.4 differs from
        tables created from 5.6.4 on. This is due to a change in 5.6.4
        that permits these types to have a fractional part, which
        requires from 0 to 3 bytes.
   </p>
   <div class="informaltable">
    <table summary="Storage required for date and time data types before MySQL 5.6.4 and as of MySQL 5.6.4.">
     <colgroup>
      <col style="width: 20%"/>
      <col style="width: 40%"/>
      <col style="width: 40%"/>
     </colgroup>
     <thead>
      <tr>
       <th scope="col">
        Data Type
       </th>
       <th scope="col">
        Storage Required Before MySQL 5.6.4
       </th>
       <th scope="col">
        Storage Required as of MySQL 5.6.4
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <th scope="row">
        <a class="link" href="year.html" title="13.2.4 The YEAR Type">
         <code class="literal">
          YEAR
         </code>
        </a>
       </th>
       <td>
        1 byte
       </td>
       <td>
        1 byte
       </td>
      </tr>
      <tr>
       <th scope="row">
        <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
         <code class="literal">
          DATE
         </code>
        </a>
       </th>
       <td>
        3 bytes
       </td>
       <td>
        3 bytes
       </td>
      </tr>
      <tr>
       <th scope="row">
        <a class="link" href="time.html" title="13.2.3 The TIME Type">
         <code class="literal">
          TIME
         </code>
        </a>
       </th>
       <td>
        3 bytes
       </td>
       <td>
        3 bytes + fractional seconds storage
       </td>
      </tr>
      <tr>
       <th scope="row">
        <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
         <code class="literal">
          DATETIME
         </code>
        </a>
       </th>
       <td>
        8 bytes
       </td>
       <td>
        5 bytes + fractional seconds storage
       </td>
      </tr>
      <tr>
       <th scope="row">
        <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
         <code class="literal">
          TIMESTAMP
         </code>
        </a>
       </th>
       <td>
        4 bytes
       </td>
       <td>
        4 bytes + fractional seconds storage
       </td>
      </tr>
     </tbody>
    </table>
   </div>
   <p>
    As of MySQL 5.6.4, storage for
    <a class="link" href="year.html" title="13.2.4 The YEAR Type">
     <code class="literal">
      YEAR
     </code>
    </a>
    and
    <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
     <code class="literal">
      DATE
     </code>
    </a>
    remains unchanged. However,
    <a class="link" href="time.html" title="13.2.3 The TIME Type">
     <code class="literal">
      TIME
     </code>
    </a>
    ,
    <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
     <code class="literal">
      DATETIME
     </code>
    </a>
    , and
    <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
     <code class="literal">
      TIMESTAMP
     </code>
    </a>
    are represented
        differently.
    <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
     <code class="literal">
      DATETIME
     </code>
    </a>
    is packed
        more efficiently, requiring 5 rather than 8 bytes for the
        nonfractional part, and all three parts have a fractional part
        that requires from 0 to 3 bytes, depending on the fractional
        seconds precision of stored values.
   </p>
   <div class="informaltable">
    <table summary="Required storage for fractional seconds precision.">
     <colgroup>
      <col style="width: 50%"/>
      <col style="width: 50%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Fractional Seconds Precision
       </th>
       <th>
        Storage Required
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        0
       </td>
       <td>
        0 bytes
       </td>
      </tr>
      <tr>
       <td>
        1, 2
       </td>
       <td>
        1 byte
       </td>
      </tr>
      <tr>
       <td>
        3, 4
       </td>
       <td>
        2 bytes
       </td>
      </tr>
      <tr>
       <td>
        5, 6
       </td>
       <td>
        3 bytes
       </td>
      </tr>
     </tbody>
    </table>
   </div>
   <p>
    For example,
    <a class="link" href="time.html" title="13.2.3 The TIME Type">
     <code class="literal">
      TIME(0)
     </code>
    </a>
    ,
    <a class="link" href="time.html" title="13.2.3 The TIME Type">
     <code class="literal">
      TIME(2)
     </code>
    </a>
    ,
    <a class="link" href="time.html" title="13.2.3 The TIME Type">
     <code class="literal">
      TIME(4)
     </code>
    </a>
    , and
    <a class="link" href="time.html" title="13.2.3 The TIME Type">
     <code class="literal">
      TIME(6)
     </code>
    </a>
    use 3, 4, 5, and 6 bytes,
        respectively.
    <a class="link" href="time.html" title="13.2.3 The TIME Type">
     <code class="literal">
      TIME
     </code>
    </a>
    and
    <a class="link" href="time.html" title="13.2.3 The TIME Type">
     <code class="literal">
      TIME(0)
     </code>
    </a>
    are equivalent and
        require the same storage.
   </p>
   <p>
    For details about internal representation of temporal values,
        see
    <a class="ulink" href="/doc/internals/en/algorithms.html" target="_top">
     MySQL
        Internals: Important Algorithms and Structures
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="data-types-storage-reqs-strings">
       </a>
       String Type Storage Requirements
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045210561776">
   </a>
   <a class="indexterm" name="idm46045210560288">
   </a>
   <p>
    In the following table,
    <em class="replaceable">
     <code>
      M
     </code>
    </em>
    represents
        the declared column length in characters for nonbinary string
        types and bytes for binary string types.
    <em class="replaceable">
     <code>
      L
     </code>
    </em>
    represents the actual length in
        bytes of a given string value.
   </p>
   <div class="informaltable">
    <table summary="Storage required for string types.">
     <colgroup>
      <col style="width: 40%"/>
      <col style="width: 60%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Data Type
       </th>
       <th>
        Storage Required
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        <code class="literal">
         CHAR(
         <em class="replaceable">
          <code>
           M
          </code>
         </em>
         )
        </code>
       </td>
       <td>
        The compact family of InnoDB row formats optimize storage for
              variable-length character sets. See
        <a class="xref" href="innodb-row-format.html#innodb-compact-row-format-characteristics" title="COMPACT Row Format Storage Characteristics">
         COMPACT Row Format Storage Characteristics
        </a>
        .
              Otherwise,
        <em class="replaceable">
         <code>
          M
         </code>
        </em>
        ×
        <em class="replaceable">
         <code>
          w
         </code>
        </em>
        bytes,
        <code class="literal">
         &lt;=
         <em class="replaceable">
          <code>
           M
          </code>
         </em>
         &lt;=
        </code>
        255, where
        <em class="replaceable">
         <code>
          w
         </code>
        </em>
        is the number of bytes
              required for the maximum-length character in the character
              set.
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         BINARY(
         <em class="replaceable">
          <code>
           M
          </code>
         </em>
         )
        </code>
       </td>
       <td>
        <em class="replaceable">
         <code>
          M
         </code>
        </em>
        bytes, 0
        <code class="literal">
         &lt;=
         <em class="replaceable">
          <code>
           M
          </code>
         </em>
         &lt;=
        </code>
        255
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         VARCHAR(
         <em class="replaceable">
          <code>
           M
          </code>
         </em>
         )
        </code>
        ,
        <code class="literal">
         VARBINARY(
         <em class="replaceable">
          <code>
           M
          </code>
         </em>
         )
        </code>
       </td>
       <td>
        <em class="replaceable">
         <code>
          L
         </code>
        </em>
        + 1 bytes if column values require 0
              − 255 bytes,
        <em class="replaceable">
         <code>
          L
         </code>
        </em>
        + 2 bytes
              if values may require more than 255 bytes
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          TINYBLOB
         </code>
        </a>
        ,
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          TINYTEXT
         </code>
        </a>
       </td>
       <td>
        <em class="replaceable">
         <code>
          L
         </code>
        </em>
        + 1 bytes, where
        <em class="replaceable">
         <code>
          L
         </code>
        </em>
        &lt;
              2
        <sup>
         8
        </sup>
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          BLOB
         </code>
        </a>
        ,
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          TEXT
         </code>
        </a>
       </td>
       <td>
        <em class="replaceable">
         <code>
          L
         </code>
        </em>
        + 2 bytes, where
        <em class="replaceable">
         <code>
          L
         </code>
        </em>
        &lt;
              2
        <sup>
         16
        </sup>
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          MEDIUMBLOB
         </code>
        </a>
        ,
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          MEDIUMTEXT
         </code>
        </a>
       </td>
       <td>
        <em class="replaceable">
         <code>
          L
         </code>
        </em>
        + 3 bytes, where
        <em class="replaceable">
         <code>
          L
         </code>
        </em>
        &lt;
              2
        <sup>
         24
        </sup>
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          LONGBLOB
         </code>
        </a>
        ,
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          LONGTEXT
         </code>
        </a>
       </td>
       <td>
        <em class="replaceable">
         <code>
          L
         </code>
        </em>
        + 4 bytes, where
        <em class="replaceable">
         <code>
          L
         </code>
        </em>
        &lt;
              2
        <sup>
         32
        </sup>
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         ENUM('
         <em class="replaceable">
          <code>
           value1
          </code>
         </em>
         ','
         <em class="replaceable">
          <code>
           value2
          </code>
         </em>
         ',...)
        </code>
       </td>
       <td>
        1 or 2 bytes, depending on the number of enumeration values (65,535
              values maximum)
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         SET('
         <em class="replaceable">
          <code>
           value1
          </code>
         </em>
         ','
         <em class="replaceable">
          <code>
           value2
          </code>
         </em>
         ',...)
        </code>
       </td>
       <td>
        1, 2, 3, 4, or 8 bytes, depending on the number of set members (64
              members maximum)
       </td>
      </tr>
     </tbody>
    </table>
   </div>
   <p>
    Variable-length string types are stored using a length prefix
        plus data. The length prefix requires from one to four bytes
        depending on the data type, and the value of the prefix is
    <em class="replaceable">
     <code>
      L
     </code>
    </em>
    (the byte length of the string).
        For example, storage for a
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      MEDIUMTEXT
     </code>
    </a>
    value requires
    <em class="replaceable">
     <code>
      L
     </code>
    </em>
    bytes to store the value plus three
        bytes to store the length of the value.
   </p>
   <p>
    To calculate the number of bytes used to store a particular
    <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>
    , or
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      TEXT
     </code>
    </a>
    column value, you must take
        into account the character set used for that column and whether
        the value contains multibyte characters. In particular, when
        using a UTF-8 Unicode character set, you must keep in mind that
        not all characters use the same number of bytes.
    <code class="literal">
     utf8mb3
    </code>
    and
    <code class="literal">
     utf8mb4
    </code>
    character sets can require up to three and four bytes per
        character, respectively. For a breakdown of the storage used for
        different categories of
    <code class="literal">
     utf8mb3
    </code>
    or
    <code class="literal">
     utf8mb4
    </code>
    characters, see
    <a class="xref" href="charset-unicode.html" title="12.9 Unicode Support">
     Section 12.9, “Unicode Support”
    </a>
    .
   </p>
   <a class="indexterm" name="idm46045210497120">
   </a>
   <a class="indexterm" name="idm46045210495632">
   </a>
   <a class="indexterm" name="idm46045210494144">
   </a>
   <p>
    <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="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
     <code class="literal">
      VARBINARY
     </code>
    </a>
    , and the
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      BLOB
     </code>
    </a>
    and
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      TEXT
     </code>
    </a>
    types are variable-length
        types. For each, the storage requirements depend on these
        factors:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The actual length of the column value
      </p>
     </li>
     <li class="listitem">
      <p>
       The column's maximum possible length
      </p>
     </li>
     <li class="listitem">
      <p>
       The character set used for the column, because some
            character sets contain multibyte characters
      </p>
     </li>
    </ul>
   </div>
   <p>
    For example, a
    <code class="literal">
     VARCHAR(255)
    </code>
    column can hold a
        string with a maximum length of 255 characters. Assuming that
        the column uses the
    <code class="literal">
     latin1
    </code>
    character set (one
        byte per character), the actual storage required is the length
        of the string (
    <em class="replaceable">
     <code>
      L
     </code>
    </em>
    ), plus one byte to
        record the length of the string. For the string
    <code class="literal">
     'abcd'
    </code>
    ,
    <em class="replaceable">
     <code>
      L
     </code>
    </em>
    is 4 and
        the storage requirement is five bytes. If the same column is
        instead declared to use the
    <code class="literal">
     ucs2
    </code>
    double-byte
        character set, the storage requirement is 10 bytes: The length
        of
    <code class="literal">
     'abcd'
    </code>
    is eight bytes and the column
        requires two bytes to store lengths because the maximum length
        is greater than 255 (up to 510 bytes).
   </p>
   <p>
    The effective maximum number of
    <span class="emphasis">
     <em>
      bytes
     </em>
    </span>
    that
        can be stored in a
    <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
     <code class="literal">
      VARCHAR
     </code>
    </a>
    or
    <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
     <code class="literal">
      VARBINARY
     </code>
    </a>
    column is subject to
        the maximum row size of 65,535 bytes, which is shared among all
        columns. For a
    <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
     <code class="literal">
      VARCHAR
     </code>
    </a>
    column
        that stores multibyte characters, the effective maximum number
        of
    <span class="emphasis">
     <em>
      characters
     </em>
    </span>
    is less. For example,
    <code class="literal">
     utf8mb4
    </code>
    characters can require up to four
        bytes per character, so a
    <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
     <code class="literal">
      VARCHAR
     </code>
    </a>
    column that uses the
    <code class="literal">
     utf8mb4
    </code>
    character set
        can be declared to be a maximum of 16,383 characters. 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>
    <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>
    The
    <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
     <code class="literal">
      NDB
     </code>
    </a>
    storage engine supports
        variable-width columns. This means that a
    <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
     <code class="literal">
      VARCHAR
     </code>
    </a>
    column in an NDB Cluster
        table requires the same amount of storage as would any other
        storage engine, with the exception that such values are 4-byte
        aligned. Thus, the string
    <code class="literal">
     'abcd'
    </code>
    stored in a
    <code class="literal">
     VARCHAR(50)
    </code>
    column using the
    <code class="literal">
     latin1
    </code>
    character set requires 8 bytes (rather
        than 5 bytes for the same column value in a
    <code class="literal">
     MyISAM
    </code>
    table).
   </p>
   <p>
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      TEXT
     </code>
    </a>
    ,
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      BLOB
     </code>
    </a>
    , and
    <a class="link" href="json.html" title="13.5 The JSON Data Type">
     <code class="literal">
      JSON
     </code>
    </a>
    columns are implemented
        differently in the
    <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
     <code class="literal">
      NDB
     </code>
    </a>
    storage
        engine, wherein each row in the column is made up of two
        separate parts. One of these is of fixed size (256 bytes for
    <code class="literal">
     TEXT
    </code>
    and
    <code class="literal">
     BLOB
    </code>
    , 4000 bytes
        for
    <code class="literal">
     JSON
    </code>
    ), and is actually stored in the
        original table. The other consists of any data in excess of 256
        bytes, which is stored in a hidden blob parts table. The size of
        the rows in this second table are determined by the exact type
        of the column, as shown in the following table:
   </p>
   <div class="informaltable">
    <table summary="NDB blob table row lengths for TEXT and BLOB types.">
     <colgroup>
      <col style="width: 40%"/>
      <col style="width: 60%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Type
       </th>
       <th>
        Blob Part Size
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          BLOB
         </code>
        </a>
        ,
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          TEXT
         </code>
        </a>
       </td>
       <td>
        2000
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          MEDIUMBLOB
         </code>
        </a>
        ,
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          MEDIUMTEXT
         </code>
        </a>
       </td>
       <td>
        4000
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          LONGBLOB
         </code>
        </a>
        ,
        <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
         <code class="literal">
          LONGTEXT
         </code>
        </a>
       </td>
       <td>
        13948
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="json.html" title="13.5 The JSON Data Type">
         <code class="literal">
          JSON
         </code>
        </a>
       </td>
       <td>
        8100
       </td>
      </tr>
     </tbody>
    </table>
   </div>
   <p>
    This means that the size of a
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      TEXT
     </code>
    </a>
    column is 256 if
    <em class="replaceable">
     <code>
      size
     </code>
    </em>
    &lt;= 256 (where
    <em class="replaceable">
     <code>
      size
     </code>
    </em>
    represents the size of the row);
        otherwise, the size is 256 +
    <em class="replaceable">
     <code>
      size
     </code>
    </em>
    + (2000 ×
        (
    <em class="replaceable">
     <code>
      size
     </code>
    </em>
    − 256) % 2000).
   </p>
   <p>
    No blob parts are stored separately by
    <code class="literal">
     NDB
    </code>
    for
    <code class="literal">
     TINYBLOB
    </code>
    or
    <code class="literal">
     TINYTEXT
    </code>
    column values.
   </p>
   <p>
    You can increase the size of an
    <code class="literal">
     NDB
    </code>
    blob
        column's blob part to the maximum of 13948 using
    <code class="literal">
     NDB_COLUMN
    </code>
    in a column comment when creating
        or altering the parent table.
    <code class="literal">
     NDB
    </code>
    also
        supports setting the inline size for a
    <code class="literal">
     TEXT
    </code>
    ,
    <code class="literal">
     BLOB
    </code>
    , or
    <code class="literal">
     JSON
    </code>
    column,
        using
    <code class="literal">
     NDB_TABLE
    </code>
    in a column comment. See
    <a class="xref" href="create-table-ndb-comment-options.html#create-table-ndb-comment-column-options" title="NDB_COLUMN Options">
     NDB_COLUMN Options
    </a>
    , for
        more information.
   </p>
   <a class="indexterm" name="idm46045210419472">
   </a>
   <p>
    The size of an
    <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
     <code class="literal">
      ENUM
     </code>
    </a>
    object is
        determined by the number of different enumeration values. One
        byte is used for enumerations with up to 255 possible values.
        Two bytes are used for enumerations having between 256 and
        65,535 possible values. See
    <a class="xref" href="enum.html" title="13.3.5 The ENUM Type">
     Section 13.3.5, “The ENUM Type”
    </a>
    .
   </p>
   <a class="indexterm" name="idm46045210415408">
   </a>
   <p>
    The size of a
    <a class="link" href="set.html" title="13.3.6 The SET Type">
     <code class="literal">
      SET
     </code>
    </a>
    object is
        determined by the number of different set members. If the set
        size is
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    , the object occupies
    <code class="literal">
     (
     <em class="replaceable">
      <code>
       N
      </code>
     </em>
     +7)/8
    </code>
    bytes,
        rounded up to 1, 2, 3, 4, or 8 bytes. A
    <a class="link" href="set.html" title="13.3.6 The SET Type">
     <code class="literal">
      SET
     </code>
    </a>
    can have a maximum of 64
        members. See
    <a class="xref" href="set.html" title="13.3.6 The SET Type">
     Section 13.3.6, “The SET Type”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="data-types-storage-reqs-gis">
       </a>
       Spatial Type Storage Requirements
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045210407584">
   </a>
   <a class="indexterm" name="idm46045210406096">
   </a>
   <a class="indexterm" name="idm46045210404608">
   </a>
   <p>
    MySQL stores geometry values using 4 bytes to indicate the SRID
        followed by the WKB representation of the value. The
    <a class="link" href="string-functions.html#function_length">
     <code class="literal">
      LENGTH()
     </code>
    </a>
    function returns the
        space in bytes required for value storage.
   </p>
   <p>
    For descriptions of WKB and internal storage formats for spatial
        values, see
    <a class="xref" href="gis-data-formats.html" title="13.4.3 Supported Spatial Data Formats">
     Section 13.4.3, “Supported Spatial Data Formats”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="data-types-storage-reqs-json">
       </a>
       JSON Storage Requirements
      </h3>
     </div>
    </div>
   </div>
   <p>
    In general, the storage requirement for a
    <a class="link" href="json.html" title="13.5 The JSON Data Type">
     <code class="literal">
      JSON
     </code>
    </a>
    column is approximately the
        same as for a
    <code class="literal">
     LONGBLOB
    </code>
    or
    <code class="literal">
     LONGTEXT
    </code>
    column; that is, the space consumed
        by a JSON document is roughly the same as it would be for the
        document's string representation stored in a column of one
        of these types. However, there is an overhead imposed by the
        binary encoding, including metadata and dictionaries needed for
        lookup, of the individual values stored in the JSON document.
        For example, a string stored in a JSON document requires 4 to 10
        bytes additional storage, depending on the length of the string
        and the size of the object or array in which it is stored.
   </p>
   <p>
    In addition, MySQL imposes a limit on the size of any JSON
        document stored in a
    <code class="literal">
     JSON
    </code>
    column such that it
        cannot be any larger than the value of
    <a class="link" href="server-system-variables.html#sysvar_max_allowed_packet">
     <code class="literal">
      max_allowed_packet
     </code>
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
