<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="innodb-row-format">
      </a>
      17.10 InnoDB Row Formats
     </h2>
    </div>
   </div>
  </div>
  <p>
   The row format of a table determines how its rows are physically
    stored, which in turn can affect the performance of queries and DML
    operations. As more rows fit into a single disk page, queries and
    index lookups can work faster, less cache memory is required in the
    buffer pool, and less I/O is required to write out updated values.
  </p>
  <p>
   The data in each table is divided into pages. The pages that make up
    each table are arranged in a tree data structure called a B-tree
    index. Table data and secondary indexes both use this type of
    structure. The B-tree index that represents an entire table is known
    as the clustered index, which is organized according to the primary
    key columns. The nodes of a clustered index data structure contain
    the values of all columns in the row. The nodes of a secondary index
    structure contain the values of index columns and primary key
    columns.
  </p>
  <p>
   Variable-length columns are an exception to the rule that column
    values are stored in B-tree index nodes. Variable-length columns
    that are too long to fit on a B-tree page are stored on separately
    allocated disk pages called overflow pages. Such columns are
    referred to as off-page columns. The values of off-page columns are
    stored in singly-linked lists of overflow pages, with each such
    column having its own list of one or more overflow pages. Depending
    on column length, all or a prefix of variable-length column values
    are stored in the B-tree to avoid wasting storage and having to read
    a separate page.
  </p>
  <p>
   The
   <code class="literal">
    InnoDB
   </code>
   storage engine supports four row
    formats:
   <code class="literal">
    REDUNDANT
   </code>
   ,
   <code class="literal">
    COMPACT
   </code>
   ,
   <code class="literal">
    DYNAMIC
   </code>
   , and
   <code class="literal">
    COMPRESSED
   </code>
   .
  </p>
  <div class="table">
   <a name="innodb-row-format-overview">
   </a>
   <p class="title">
    <b>
     Table 17.14 InnoDB Row Format Overview
    </b>
   </p>
   <div class="table-contents">
    <table summary="Overview of InnoDB row formats incuding a description, supported features, and supported tablespace types.">
     <colgroup>
      <col style="width: 10%"/>
      <col style="width: 18%"/>
      <col style="width: 18%"/>
      <col style="width: 18%"/>
      <col style="width: 18%"/>
      <col style="width: 18%"/>
     </colgroup>
     <thead>
      <tr>
       <th scope="col">
        Row Format
       </th>
       <th scope="col">
        Compact Storage Characteristics
       </th>
       <th scope="col">
        Enhanced Variable-Length Column Storage
       </th>
       <th scope="col">
        Large Index Key Prefix Support
       </th>
       <th scope="col">
        Compression Support
       </th>
       <th scope="col">
        Supported Tablespace Types
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <th scope="row">
        <code class="literal">
         REDUNDANT
        </code>
       </th>
       <td>
        No
       </td>
       <td>
        No
       </td>
       <td>
        No
       </td>
       <td>
        No
       </td>
       <td>
        system, file-per-table, general
       </td>
      </tr>
      <tr>
       <th scope="row">
        <code class="literal">
         COMPACT
        </code>
       </th>
       <td>
        Yes
       </td>
       <td>
        No
       </td>
       <td>
        No
       </td>
       <td>
        No
       </td>
       <td>
        system, file-per-table, general
       </td>
      </tr>
      <tr>
       <th scope="row">
        <code class="literal">
         DYNAMIC
        </code>
       </th>
       <td>
        Yes
       </td>
       <td>
        Yes
       </td>
       <td>
        Yes
       </td>
       <td>
        No
       </td>
       <td>
        system, file-per-table, general
       </td>
      </tr>
      <tr>
       <th scope="row">
        <code class="literal">
         COMPRESSED
        </code>
       </th>
       <td>
        Yes
       </td>
       <td>
        Yes
       </td>
       <td>
        Yes
       </td>
       <td>
        Yes
       </td>
       <td>
        file-per-table, general
       </td>
      </tr>
     </tbody>
    </table>
   </div>
  </div>
  <br class="table-break"/>
  <p>
   The topics that follow describe row format storage characteristics
    and how to define and determine the row format of a table.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-row-format.html#innodb-row-format-redundant" title="REDUNDANT Row Format">
       REDUNDANT Row Format
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-row-format.html#innodb-row-format-compact" title="COMPACT Row Format">
       COMPACT Row Format
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-row-format.html#innodb-row-format-dynamic" title="DYNAMIC Row Format">
       DYNAMIC Row Format
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-row-format.html#innodb-row-format-compressed" title="COMPRESSED Row Format">
       COMPRESSED Row Format
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-row-format.html#innodb-row-format-defining" title="Defining the Row Format of a Table">
       Defining the Row Format of a Table
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-row-format.html#innodb-row-format-detrmining" title="Determining the Row Format of a Table">
       Determining the Row Format of a Table
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="innodb-row-format-redundant">
       </a>
       REDUNDANT Row Format
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045160971984">
   </a>
   <a class="indexterm" name="idm46045160970496">
   </a>
   <p>
    The
    <code class="literal">
     REDUNDANT
    </code>
    format provides compatibility
      with older versions of MySQL.
   </p>
   <p>
    Tables that use the
    <code class="literal">
     REDUNDANT
    </code>
    row format store
      the first 768 bytes of variable-length column values
      (
    <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
    <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) in the index record
      within the B-tree node, with the remainder stored on overflow
      pages. Fixed-length columns greater than or equal to 768 bytes are
      encoded as variable-length columns, 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 the value of a column is 768 bytes or less, an overflow page is
      not used, and some savings in I/O may result, since the value is
      stored entirely in the B-tree node. This works well for relatively
      short
    <code class="literal">
     BLOB
    </code>
    column values, but may cause B-tree
      nodes to fill with data rather than key values, reducing their
      efficiency. Tables with many
    <code class="literal">
     BLOB
    </code>
    columns could
      cause B-tree nodes to become too full, and contain too few rows,
      making the entire index less efficient than if rows were shorter
      or column values were stored off-page.
   </p>
   <h4>
    <a name="innodb-redundant-row-format-characteristics">
    </a>
    REDUNDANT Row Format Storage Characteristics
   </h4>
   <a class="indexterm" name="idm46045160956736">
   </a>
   <p>
    The
    <code class="literal">
     REDUNDANT
    </code>
    row format has the following
      storage characteristics:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Each index record contains a 6-byte header. The header is used
          to link together consecutive records, and for row-level
          locking.
      </p>
     </li>
     <li class="listitem">
      <p>
       Records in the clustered index contain fields for all
          user-defined columns. In addition, there is a 6-byte
          transaction ID field and a 7-byte roll pointer field.
      </p>
     </li>
     <li class="listitem">
      <p>
       If no primary key is defined for a table, each clustered index
          record also contains a 6-byte row ID field.
      </p>
     </li>
     <li class="listitem">
      <p>
       Each secondary index record contains all the primary key
          columns defined for the clustered index key that are not in
          the secondary index.
      </p>
     </li>
     <li class="listitem">
      <p>
       A record contains a pointer to each field of the record. If
          the total length of the fields in a record is less than 128
          bytes, the pointer is one byte; otherwise, two bytes. The
          array of pointers is called the record directory. The area
          where the pointers point is the data part of the record.
      </p>
     </li>
     <li class="listitem">
      <p>
       Internally, fixed-length character columns such as
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR(10)
        </code>
       </a>
       in stored in
          fixed-length format. Trailing spaces are not truncated from
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       columns.
      </p>
     </li>
     <li class="listitem">
      <p>
       Fixed-length columns greater than or equal to 768 bytes are
          encoded as variable-length columns, 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>
     </li>
     <li class="listitem">
      <p>
       An SQL
       <code class="literal">
        NULL
       </code>
       value reserves one or two bytes
          in the record directory. An SQL
       <code class="literal">
        NULL
       </code>
       value
          reserves zero bytes in the data part of the record if stored
          in a variable-length column. For a fixed-length column, the
          fixed length of the column is reserved in the data part of the
          record. Reserving fixed space for
       <code class="literal">
        NULL
       </code>
       values permits columns to be updated in place from
       <code class="literal">
        NULL
       </code>
       to non-
       <code class="literal">
        NULL
       </code>
       values
          without causing index page fragmentation.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="innodb-row-format-compact">
       </a>
       COMPACT Row Format
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045160936640">
   </a>
   <a class="indexterm" name="idm46045160935152">
   </a>
   <p>
    The
    <code class="literal">
     COMPACT
    </code>
    row format reduces row storage
      space by about 20% compared to the
    <code class="literal">
     REDUNDANT
    </code>
    row format, at the cost of increasing CPU use for some operations.
      If your workload is a typical one that is limited by cache hit
      rates and disk speed,
    <code class="literal">
     COMPACT
    </code>
    format is likely
      to be faster. If the workload is limited by CPU speed, compact
      format might be slower.
   </p>
   <p>
    Tables that use the
    <code class="literal">
     COMPACT
    </code>
    row format store
      the first 768 bytes of variable-length column values
      (
    <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
    <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) in the index record
      within the
    <a class="link" href="glossary.html#glos_b_tree" title="B-tree">
     B-tree
    </a>
    node, with
      the remainder stored on overflow pages. Fixed-length columns
      greater than or equal to 768 bytes are encoded as variable-length
      columns, 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 the value of a column is 768 bytes or less, an overflow page is
      not used, and some savings in I/O may result, since the value is
      stored entirely in the B-tree node. This works well for relatively
      short
    <code class="literal">
     BLOB
    </code>
    column values, but may cause B-tree
      nodes to fill with data rather than key values, reducing their
      efficiency. Tables with many
    <code class="literal">
     BLOB
    </code>
    columns could
      cause B-tree nodes to become too full, and contain too few rows,
      making the entire index less efficient than if rows were shorter
      or column values were stored off-page.
   </p>
   <h4>
    <a name="innodb-compact-row-format-characteristics">
    </a>
    COMPACT Row Format Storage Characteristics
   </h4>
   <a class="indexterm" name="idm46045160918816">
   </a>
   <p>
    The
    <code class="literal">
     COMPACT
    </code>
    row format has the following
      storage characteristics:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Each index record contains a 5-byte header that may be
          preceded by a variable-length header. The header is used to
          link together consecutive records, and for row-level locking.
      </p>
     </li>
     <li class="listitem">
      <p>
       The variable-length part of the record header contains a bit
          vector for indicating
       <code class="literal">
        NULL
       </code>
       columns. If the
          number of columns in the index that can be
       <code class="literal">
        NULL
       </code>
       is
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       , the
          bit vector occupies
       <code class="literal">
        CEILING(
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        /8)
       </code>
       bytes. (For example, if there are anywhere from 9 to 16
          columns that can be
       <code class="literal">
        NULL
       </code>
       , the bit vector
          uses two bytes.) Columns that are
       <code class="literal">
        NULL
       </code>
       do
          not occupy space other than the bit in this vector. The
          variable-length part of the header also contains the lengths
          of variable-length columns. Each length takes one or two
          bytes, depending on the maximum length of the column. If all
          columns in the index are
       <code class="literal">
        NOT NULL
       </code>
       and have
          a fixed length, the record header has no variable-length part.
      </p>
     </li>
     <li class="listitem">
      <p>
       For each non-
       <code class="literal">
        NULL
       </code>
       variable-length field,
          the record header contains the length of the column in one or
          two bytes. Two bytes are only needed if part of the column is
          stored externally in overflow pages or the maximum length
          exceeds 255 bytes and the actual length exceeds 127 bytes. For
          an externally stored column, the 2-byte length indicates the
          length of the internally stored part plus the 20-byte pointer
          to the externally stored part. The internal part is 768 bytes,
          so the length is 768+20. The 20-byte pointer stores the true
          length of the column.
      </p>
     </li>
     <li class="listitem">
      <p>
       The record header is followed by the data contents of
          non-
       <code class="literal">
        NULL
       </code>
       columns.
      </p>
     </li>
     <li class="listitem">
      <p>
       Records in the clustered index contain fields for all
          user-defined columns. In addition, there is a 6-byte
          transaction ID field and a 7-byte roll pointer field.
      </p>
     </li>
     <li class="listitem">
      <p>
       If no primary key is defined for a table, each clustered index
          record also contains a 6-byte row ID field.
      </p>
     </li>
     <li class="listitem">
      <p>
       Each secondary index record contains all the primary key
          columns defined for the clustered index key that are not in
          the secondary index. If any of the primary key columns are
          variable length, the record header for each secondary index
          has a variable-length part to record their lengths, even if
          the secondary index is defined on fixed-length columns.
      </p>
     </li>
     <li class="listitem">
      <p>
       Internally, for nonvariable-length character sets,
          fixed-length character columns such as
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR(10)
        </code>
       </a>
       are stored in a
          fixed-length format.
      </p>
      <p>
       Trailing spaces are not truncated from
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       columns.
      </p>
     </li>
     <li class="listitem">
      <p>
       Internally, for variable-length character sets such as
       <code class="literal">
        utf8mb3
       </code>
       and
       <code class="literal">
        utf8mb4
       </code>
       ,
       <code class="literal">
        InnoDB
       </code>
       attempts to store
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR(
         <em class="replaceable">
          <code>
           N
          </code>
         </em>
         )
        </code>
       </a>
       in
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       bytes by trimming trailing
          spaces. If the byte length of a
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR(
         <em class="replaceable">
          <code>
           N
          </code>
         </em>
         )
        </code>
       </a>
       column value exceeds
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       bytes,
          trailing spaces are trimmed to a maximum of the column value
          byte length. The maximum length of a
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR(
         <em class="replaceable">
          <code>
           N
          </code>
         </em>
         )
        </code>
       </a>
       column is the maximum character byte length ×
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       .
      </p>
      <p>
       A minimum of
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       bytes is reserved
          for
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR(
         <em class="replaceable">
          <code>
           N
          </code>
         </em>
         )
        </code>
       </a>
       .
          Reserving the minimum space
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       in
          many cases enables column updates to be done in place without
          causing index page fragmentation. By comparison,
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR(
         <em class="replaceable">
          <code>
           N
          </code>
         </em>
         )
        </code>
       </a>
       columns occupy the maximum character byte length ×
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       when using the
       <code class="literal">
        REDUNDANT
       </code>
       row format.
      </p>
      <p>
       Fixed-length columns greater than or equal to 768 bytes are
          encoded 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>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="innodb-row-format-dynamic">
       </a>
       DYNAMIC Row Format
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045160878432">
   </a>
   <a class="indexterm" name="idm46045160876944">
   </a>
   <p>
    The
    <code class="literal">
     DYNAMIC
    </code>
    row format offers the same storage
      characteristics as the
    <code class="literal">
     COMPACT
    </code>
    row format but
      adds enhanced storage capabilities for long variable-length
      columns and supports large index key prefixes.
   </p>
   <p>
    When a table is created with
    <code class="literal">
     ROW_FORMAT=DYNAMIC
    </code>
    ,
    <code class="literal">
     InnoDB
    </code>
    can store long variable-length column values (for
    <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
    <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) fully off-page, with
      the clustered index record containing only a 20-byte pointer to
      the overflow page. Fixed-length fields greater than or equal to
      768 bytes are encoded as variable-length fields. 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>
    Whether columns are stored off-page depends on the page size and
      the total size of the row. When a row is too long, the longest
      columns are chosen for off-page storage until the clustered index
      record fits on the
    <a class="link" href="glossary.html#glos_b_tree" title="B-tree">
     B-tree
    </a>
    page.
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      TEXT
     </code>
    </a>
    and
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      BLOB
     </code>
    </a>
    columns that are less than or
      equal to 40 bytes are stored in line.
   </p>
   <p>
    The
    <code class="literal">
     DYNAMIC
    </code>
    row format maintains the efficiency
      of storing the entire row in the index node if it fits (as do the
    <code class="literal">
     COMPACT
    </code>
    and
    <code class="literal">
     REDUNDANT
    </code>
    formats), but the
    <code class="literal">
     DYNAMIC
    </code>
    row format avoids the
      problem of filling B-tree nodes with a large number of data bytes
      of long columns. The
    <code class="literal">
     DYNAMIC
    </code>
    row format is
      based on the idea that if a portion of a long data value is stored
      off-page, it is usually most efficient to store the entire value
      off-page. With
    <code class="literal">
     DYNAMIC
    </code>
    format, shorter columns
      are likely to remain in the B-tree node, minimizing the number of
      overflow pages required for a given row.
   </p>
   <p>
    The
    <code class="literal">
     DYNAMIC
    </code>
    row format supports index key
      prefixes up to 3072 bytes.
   </p>
   <p>
    Tables that use the
    <code class="literal">
     DYNAMIC
    </code>
    row format can be
      stored in the system tablespace, file-per-table tablespaces, and
      general tablespaces. To store
    <code class="literal">
     DYNAMIC
    </code>
    tables in
      the system tablespace, either disable
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    and use a
      regular
    <code class="literal">
     CREATE TABLE
    </code>
    or
    <code class="literal">
     ALTER
      TABLE
    </code>
    statement, or use the
    <code class="literal">
     TABLESPACE [=]
      innodb_system
    </code>
    table option with
    <code class="literal">
     CREATE
      TABLE
    </code>
    or
    <code class="literal">
     ALTER TABLE
    </code>
    . The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    variable is
      not applicable to general tablespaces, nor is it applicable when
      using the
    <code class="literal">
     TABLESPACE [=] innodb_system
    </code>
    table
      option to store
    <code class="literal">
     DYNAMIC
    </code>
    tables in the system
      tablespace.
   </p>
   <h4>
    <a name="innodb-dynamic-row-format-characteristics">
    </a>
    DYNAMIC Row Format Storage Characteristics
   </h4>
   <a class="indexterm" name="idm46045160843776">
   </a>
   <p>
    The
    <code class="literal">
     DYNAMIC
    </code>
    row format is a variation of the
    <code class="literal">
     COMPACT
    </code>
    row format. For storage
      characteristics, 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>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="innodb-row-format-compressed">
       </a>
       COMPRESSED Row Format
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045160838368">
   </a>
   <a class="indexterm" name="idm46045160836880">
   </a>
   <p>
    The
    <code class="literal">
     COMPRESSED
    </code>
    row format offers the same
      storage characteristics and capabilities as the
    <code class="literal">
     DYNAMIC
    </code>
    row format but adds support for table
      and index data compression.
   </p>
   <p>
    The
    <code class="literal">
     COMPRESSED
    </code>
    row format uses similar internal
      details for off-page storage as the
    <code class="literal">
     DYNAMIC
    </code>
    row
      format, with additional storage and performance considerations
      from the table and index data being compressed and using smaller
      page sizes. With the
    <code class="literal">
     COMPRESSED
    </code>
    row format, the
    <code class="literal">
     KEY_BLOCK_SIZE
    </code>
    option controls how much column
      data is stored in the clustered index, and how much is placed on
      overflow pages. For more information about the
    <code class="literal">
     COMPRESSED
    </code>
    row format, see
    <a class="xref" href="innodb-compression.html" title="17.9 InnoDB Table and Page Compression">
     Section 17.9, “InnoDB Table and Page Compression”
    </a>
    .
   </p>
   <p>
    The
    <code class="literal">
     COMPRESSED
    </code>
    row format supports index key
      prefixes up to 3072 bytes.
   </p>
   <p>
    Tables that use the
    <code class="literal">
     COMPRESSED
    </code>
    row format can
      be created in file-per-table tablespaces or general tablespaces.
      The system tablespace does not support the
    <code class="literal">
     COMPRESSED
    </code>
    row format. To store a
    <code class="literal">
     COMPRESSED
    </code>
    table in a file-per-table
      tablespace, the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    variable
      must be enabled. The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    variable is
      not applicable to general tablespaces. General tablespaces support
      all row formats with the caveat that compressed and uncompressed
      tables cannot coexist in the same general tablespace due to
      different physical page sizes. For more information, see
    <a class="xref" href="general-tablespaces.html" title="17.6.3.3 General Tablespaces">
     Section 17.6.3.3, “General Tablespaces”
    </a>
    .
   </p>
   <h4>
    <a name="innodb-compressed-row-format-characteristics">
    </a>
    Compressed Row Format Storage Characteristics
   </h4>
   <a class="indexterm" name="idm46045160820048">
   </a>
   <p>
    The
    <code class="literal">
     COMPRESSED
    </code>
    row format is a variation of the
    <code class="literal">
     COMPACT
    </code>
    row format. For storage
      characteristics, 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>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="innodb-row-format-defining">
       </a>
       Defining the Row Format of a Table
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045160814608">
   </a>
   <a class="indexterm" name="idm46045160813120">
   </a>
   <a class="indexterm" name="idm46045160811632">
   </a>
   <p>
    The default row format for
    <code class="literal">
     InnoDB
    </code>
    tables is
      defined by
    <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format">
     <code class="literal">
      innodb_default_row_format
     </code>
    </a>
    variable, which has a default value of
    <code class="literal">
     DYNAMIC
    </code>
    .
      The default row format is used when the
    <code class="literal">
     ROW_FORMAT
    </code>
    table option is not defined
      explicitly or when
    <code class="literal">
     ROW_FORMAT=DEFAULT
    </code>
    is
      specified.
   </p>
   <p>
    The row format of a table can be defined explicitly using the
    <code class="literal">
     ROW_FORMAT
    </code>
    table option in a
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    or
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    statement. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa23120995"><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>c1 <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">ROW_FORMAT</span><span class="token operator">=</span><span class="token keyword">DYNAMIC</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    An explicitly defined
    <code class="literal">
     ROW_FORMAT
    </code>
    setting
      overrides the default row format. Specifying
    <code class="literal">
     ROW_FORMAT=DEFAULT
    </code>
    is equivalent to using the
      implicit default.
   </p>
   <p>
    The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format">
     <code class="literal">
      innodb_default_row_format
     </code>
    </a>
    variable can be set dynamically:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa3748118"><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">SET</span> <span class="token keyword">GLOBAL</span> innodb_default_row_format<span class="token operator">=</span><span class="token keyword">DYNAMIC</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Valid
    <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format">
     <code class="literal">
      innodb_default_row_format
     </code>
    </a>
    options include
    <code class="literal">
     DYNAMIC
    </code>
    ,
    <code class="literal">
     COMPACT
    </code>
    , and
    <code class="literal">
     REDUNDANT
    </code>
    . The
    <code class="literal">
     COMPRESSED
    </code>
    row format, which is not supported
      for use in the system tablespace, cannot be defined as the
      default. It can only be specified explicitly in a
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    or
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    statement. Attempting
      to set the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format">
     <code class="literal">
      innodb_default_row_format
     </code>
    </a>
    variable to
    <code class="literal">
     COMPRESSED
    </code>
    returns an error:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13488640"><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">SET</span> <span class="token keyword">GLOBAL</span> innodb_default_row_format<span class="token operator">=</span><span class="token keyword">COMPRESSED</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1231 (42000)<span class="token punctuation">:</span> Variable 'innodb_default_row_format'
can't be set to the value of 'COMPRESSED'</span></code></pre>
   </div>
   <p>
    Newly created tables use the row format defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format">
     <code class="literal">
      innodb_default_row_format
     </code>
    </a>
    variable when a
    <code class="literal">
     ROW_FORMAT
    </code>
    option is not
      specified explicitly, or when
    <code class="literal">
     ROW_FORMAT=DEFAULT
    </code>
    is used. For example, the
      following
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    statements
      use the row format defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format">
     <code class="literal">
      innodb_default_row_format
     </code>
    </a>
    variable.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa85205161"><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>c1 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa10274326"><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> t2 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">ROW_FORMAT</span><span class="token operator">=</span><span class="token keyword">DEFAULT</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    When a
    <code class="literal">
     ROW_FORMAT
    </code>
    option is not specified
      explicitly, or when
    <code class="literal">
     ROW_FORMAT=DEFAULT
    </code>
    is used,
      an operation that rebuilds a table silently changes the row format
      of the table to the format defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format">
     <code class="literal">
      innodb_default_row_format
     </code>
    </a>
    variable.
   </p>
   <p>
    Table-rebuilding operations include
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER
      TABLE
     </code>
    </a>
    operations that use
    <code class="literal">
     ALGORITHM=COPY
    </code>
    or
    <code class="literal">
     ALGORITHM=INPLACE
    </code>
    where table rebuilding is
      required. See
    <a class="xref" href="innodb-online-ddl-operations.html" title="17.12.1 Online DDL Operations">
     Section 17.12.1, “Online DDL Operations”
    </a>
    for
      more information.
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE TABLE
     </code>
    </a>
    is
      also a table-rebuilding operation.
   </p>
   <p>
    The following example demonstrates a table-rebuilding operation
      that silently changes the row format of a table created without an
      explicitly defined row format.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90701201"><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">SELECT</span> <span class="token variable">@@innodb_default_row_format</span><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>
<span class="token output"><span class="token punctuation">|</span> @@innodb_default_row_format <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>
<span class="token output"><span class="token punctuation">|</span> dynamic                     <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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLES <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">'test/t1'</span> \G
<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> 1. row <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>
     TABLE_ID<span class="token punctuation">:</span> 54
         NAME<span class="token punctuation">:</span> test/t1
         FLAG<span class="token punctuation">:</span> 33
       N_COLS<span class="token punctuation">:</span> 4
        SPACE<span class="token punctuation">:</span> 35
   ROW_FORMAT<span class="token punctuation">:</span> Dynamic
ZIP_PAGE_SIZE<span class="token punctuation">:</span> 0
   SPACE_TYPE<span class="token punctuation">:</span> Single
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> innodb_default_row_format<span class="token operator">=</span><span class="token keyword">COMPACT</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> <span class="token punctuation">(</span>c2 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLES <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">'test/t1'</span> \G
<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> 1. row <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>
     TABLE_ID<span class="token punctuation">:</span> 55
         NAME<span class="token punctuation">:</span> test/t1
         FLAG<span class="token punctuation">:</span> 1
       N_COLS<span class="token punctuation">:</span> 5
        SPACE<span class="token punctuation">:</span> 36
   ROW_FORMAT<span class="token punctuation">:</span> Compact
ZIP_PAGE_SIZE<span class="token punctuation">:</span> 0
   SPACE_TYPE<span class="token punctuation">:</span> Single</span></code></pre>
   </div>
   <p>
    Consider the following potential issues before changing the row
      format of existing tables from
    <code class="literal">
     REDUNDANT
    </code>
    or
    <code class="literal">
     COMPACT
    </code>
    to
    <code class="literal">
     DYNAMIC
    </code>
    .
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The
       <code class="literal">
        REDUNDANT
       </code>
       and
       <code class="literal">
        COMPACT
       </code>
       row formats support a maximum index
          key prefix length of 767 bytes whereas
       <code class="literal">
        DYNAMIC
       </code>
       and
       <code class="literal">
        COMPRESSED
       </code>
       row formats support an index key prefix length of 3072 bytes.
          In a replication environment, if the
       <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format">
        <code class="literal">
         innodb_default_row_format
        </code>
       </a>
       variable is set to
       <code class="literal">
        DYNAMIC
       </code>
       on the source,
          and set to
       <code class="literal">
        COMPACT
       </code>
       on the replica, the
          following DDL statement, which does not explicitly define a
          row format, succeeds on the source but fails on the replica:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa56106502"><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>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span> c2 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">5000</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">KEY</span> i1<span class="token punctuation">(</span>c2<span class="token punctuation">(</span><span class="token number">3070</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       For related information, see
       <a class="xref" href="innodb-limits.html" title="17.21 InnoDB Limits">
        Section 17.21, “InnoDB Limits”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Importing a table that does not explicitly define a row format
          results in a schema mismatch error if the
       <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format">
        <code class="literal">
         innodb_default_row_format
        </code>
       </a>
       setting on the source server differs from the setting on the
          destination server. For more information, see
       <a class="xref" href="innodb-table-import.html" title="17.6.1.3 Importing InnoDB Tables">
        Section 17.6.1.3, “Importing InnoDB Tables”
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="innodb-row-format-detrmining">
       </a>
       Determining the Row Format of a Table
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045160745488">
   </a>
   <p>
    To determine the row format of a table, use
    <a class="link" href="show-table-status.html" title="15.7.7.38 SHOW TABLE STATUS Statement">
     <code class="literal">
      SHOW TABLE STATUS
     </code>
    </a>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50201761"><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">SHOW</span> <span class="token keyword">TABLE</span> <span class="token keyword">STATUS</span> <span class="token keyword">IN</span> test1\G
<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> 1. row <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>
           Name<span class="token punctuation">:</span> t1
         Engine<span class="token punctuation">:</span> InnoDB
        Version<span class="token punctuation">:</span> 10
     Row_format<span class="token punctuation">:</span> Dynamic
           Rows<span class="token punctuation">:</span> 0
 Avg_row_length<span class="token punctuation">:</span> 0
    Data_length<span class="token punctuation">:</span> 16384
Max_data_length<span class="token punctuation">:</span> 0
   Index_length<span class="token punctuation">:</span> 16384
      Data_free<span class="token punctuation">:</span> 0
 Auto_increment<span class="token punctuation">:</span> 1
    Create_time<span class="token punctuation">:</span> 2016-09-14 16<span class="token punctuation">:</span>29<span class="token punctuation">:</span>38
    Update_time<span class="token punctuation">:</span> NULL
     Check_time<span class="token punctuation">:</span> NULL
      Collation<span class="token punctuation">:</span> utf8mb4_0900_ai_ci
       Checksum<span class="token punctuation">:</span> NULL
 Create_options<span class="token punctuation">:</span>
        Comment<span class="token punctuation">:</span></span></code></pre>
   </div>
   <p>
    Alternatively, query the Information Schema
    <a class="link" href="information-schema-innodb-tables-table.html" title="28.4.23 The INFORMATION_SCHEMA INNODB_TABLES Table">
     <code class="literal">
      INNODB_TABLES
     </code>
    </a>
    table:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28360740"><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">SELECT</span> <span class="token keyword">NAME</span><span class="token punctuation">,</span> <span class="token keyword">ROW_FORMAT</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLES <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span><span class="token operator">=</span><span class="token string">'test1/t1'</span><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>
<span class="token output"><span class="token punctuation">|</span> NAME     <span class="token punctuation">|</span> ROW_FORMAT <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>
<span class="token output"><span class="token punctuation">|</span> test1/t1 <span class="token punctuation">|</span> Dynamic    <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></code></pre>
   </div>
  </div>
 </div>
 <br/>
</div>
