<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="using-innodb-tables">
      </a>
      17.6.1.1 Creating InnoDB Tables
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045166868464">
  </a>
  <p>
   <code class="literal">
    InnoDB
   </code>
   tables are created using the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE 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="sa80760855"><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>a <span class="token datatype">INT</span><span class="token punctuation">,</span> b <span class="token datatype">CHAR</span> <span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>a<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    ENGINE=InnoDB
   </code>
   clause is not required when
   <code class="literal">
    InnoDB
   </code>
   is defined as the default storage
      engine, which it is by default. However, the
   <code class="literal">
    ENGINE
   </code>
   clause is useful if the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement is to be
      replayed on a different MySQL Server instance where the default
      storage engine is not
   <code class="literal">
    InnoDB
   </code>
   or is unknown. You
      can determine the default storage engine on a MySQL Server
      instance by issuing the following statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8755225"><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">@@default_storage_engine</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>
<span class="token output"><span class="token punctuation">|</span> @@default_storage_engine <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>
<span class="token output"><span class="token punctuation">|</span> InnoDB                   <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></code></pre>
  </div>
  <p>
   <code class="literal">
    InnoDB
   </code>
   tables are created in file-per-table
      tablespaces by default. To create an
   <code class="literal">
    InnoDB
   </code>
   table in the
   <code class="literal">
    InnoDB
   </code>
   system tablespace, disable
      the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
    <code class="literal">
     innodb_file_per_table
    </code>
   </a>
   variable before creating the table. To create an
   <code class="literal">
    InnoDB
   </code>
   table in a general tablespace, use
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE ...
      TABLESPACE
    </code>
   </a>
   syntax. For more information, see
   <a class="xref" href="innodb-tablespace.html" title="17.6.3 Tablespaces">
    Section 17.6.3, “Tablespaces”
   </a>
   .
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-tables-row-format">
       </a>
       Row Formats
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166848928">
   </a>
   <p>
    The row format of an
    <code class="literal">
     InnoDB
    </code>
    table determines
        how its rows are physically stored on disk.
    <code class="literal">
     InnoDB
    </code>
    supports four row formats, each with
        different storage characteristics. Supported row formats include
    <code class="literal">
     REDUNDANT
    </code>
    ,
    <code class="literal">
     COMPACT
    </code>
    ,
    <code class="literal">
     DYNAMIC
    </code>
    , and
    <code class="literal">
     COMPRESSED
    </code>
    .
        The
    <code class="literal">
     DYNAMIC
    </code>
    row format is the default. For
        information about row format characteristics, see
    <a class="xref" href="innodb-row-format.html" title="17.10 InnoDB Row Formats">
     Section 17.10, “InnoDB Row Formats”
    </a>
    .
   </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 defines the default row format. The row format of a
        table can also be defined explicitly using the
    <code class="literal">
     ROW_FORMAT
    </code>
    table option in a
    <code class="literal">
     CREATE
        TABLE
    </code>
    or
    <code class="literal">
     ALTER TABLE
    </code>
    statement. See
    <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>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-tables-primary-key">
       </a>
       Primary Keys
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166835136">
   </a>
   <p>
    It is recommended that you define a primary key for each table
        that you create. When selecting primary key columns, choose
        columns with the following characteristics:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Columns that are referenced by the most important queries.
      </p>
     </li>
     <li class="listitem">
      <p>
       Columns that are never left blank.
      </p>
     </li>
     <li class="listitem">
      <p>
       Columns that never have duplicate values.
      </p>
     </li>
     <li class="listitem">
      <p>
       Columns that rarely if ever change value once inserted.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For example, in a table containing information about people, you
        would not create a primary key on
    <code class="literal">
     (firstname,
        lastname)
    </code>
    because more than one person can have the
        same name, a name column may be left blank, and sometimes people
        change their names. With so many constraints, often there is not
        an obvious set of columns to use as a primary key, so you create
        a new column with a numeric ID to serve as all or part of the
        primary key. You can declare an
    <a class="link" href="glossary.html#glos_auto_increment" title="auto-increment">
     auto-increment
    </a>
    column
        so that ascending values are filled in automatically as rows are
        inserted:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa10341892"><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 comment" spellcheck="true"># The value of ID can act like a pointer between related items in different tables.</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t5 <span class="token punctuation">(</span>id <span class="token datatype">INT</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span> b <span class="token datatype">CHAR</span> <span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># The primary key can consist of more than one column. Any autoinc column must come first.</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t6 <span class="token punctuation">(</span>id <span class="token datatype">INT</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span> a <span class="token datatype">INT</span><span class="token punctuation">,</span> b <span class="token datatype">CHAR</span> <span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">,</span>a<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    For more information about auto-increment columns, see
    <a class="xref" href="innodb-auto-increment-handling.html" title="17.6.1.6 AUTO_INCREMENT Handling in InnoDB">
     Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”
    </a>
    .
   </p>
   <p>
    Although a table works correctly without defining a primary key,
        the primary key is involved with many aspects of performance and
        is a crucial design aspect for any large or frequently used
        table. It is recommended that you always specify a primary key
        in the
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    statement. If
        you create the table, load data, and then run
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    to add a primary key
        later, that operation is much slower than defining the primary
        key when creating the table. For more information about primary
        keys, see
    <a class="xref" href="innodb-index-types.html" title="17.6.2.1 Clustered and Secondary Indexes">
     Section 17.6.2.1, “Clustered and Secondary Indexes”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-viewing-table-properties">
       </a>
       Viewing InnoDB Table Properties
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166818752">
   </a>
   <p>
    To view the properties of an
    <code class="literal">
     InnoDB
    </code>
    table,
        issue a
    <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>
    statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43713873"><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">FROM</span> test <span class="token operator">LIKE</span> <span class="token string">'t%'</span> \G<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> 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> 0
      Data_free<span class="token punctuation">:</span> 0
 Auto_increment<span class="token punctuation">:</span> NULL
    Create_time<span class="token punctuation">:</span> 2021-02-18 12<span class="token punctuation">:</span>18<span class="token punctuation">:</span>28
    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>
    For information about
    <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>
    output, see
    <a class="xref" href="show-table-status.html" title="15.7.7.38 SHOW TABLE STATUS Statement">
     Section 15.7.7.38, “SHOW TABLE STATUS Statement”
    </a>
    .
   </p>
   <p>
    You can also access
    <code class="literal">
     InnoDB
    </code>
    table properties
        by querying the
    <code class="literal">
     InnoDB
    </code>
    Information Schema
        system tables:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46805907"><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 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">=</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> 1144
         NAME<span class="token punctuation">:</span> test/t1
         FLAG<span class="token punctuation">:</span> 33
       N_COLS<span class="token punctuation">:</span> 5
        SPACE<span class="token punctuation">:</span> 30
   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
 INSTANT_COLS<span class="token punctuation">:</span> 0</span></code></pre>
   </div>
   <p>
    For more information, see
    <a class="xref" href="innodb-information-schema-system-tables.html" title="17.15.3 InnoDB INFORMATION_SCHEMA Schema Object Tables">
     Section 17.15.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
