<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-innodb-columns-table">
      </a>
      28.4.9 The INFORMATION_SCHEMA INNODB_COLUMNS Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045077149184">
  </a>
  <p>
   The
   <a class="link" href="information-schema-innodb-columns-table.html" title="28.4.9 The INFORMATION_SCHEMA INNODB_COLUMNS Table">
    <code class="literal">
     INNODB_COLUMNS
    </code>
   </a>
   table provides
      metadata about
   <code class="literal">
    InnoDB
   </code>
   table columns.
  </p>
  <p>
   For related usage information and examples, 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>
  <p>
   The
   <a class="link" href="information-schema-innodb-columns-table.html" title="28.4.9 The INFORMATION_SCHEMA INNODB_COLUMNS Table">
    <code class="literal">
     INNODB_COLUMNS
    </code>
   </a>
   table has these
      columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_ID
      </code>
     </p>
     <p>
      An identifier representing the table associated with the
          column; the same value as
      <code class="literal">
       INNODB_TABLES.TABLE_ID
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NAME
      </code>
     </p>
     <p>
      The name of the column. These names can be uppercase or
          lowercase depending on the
      <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
       <code class="literal">
        lower_case_table_names
       </code>
      </a>
      setting. There are no special system-reserved names for
          columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       POS
      </code>
     </p>
     <p>
      The ordinal position of the column within the table, starting
          from 0 and incrementing sequentially. When a column is
          dropped, the remaining columns are reordered so that the
          sequence has no gaps. The
      <code class="literal">
       POS
      </code>
      value for a
          virtual generated column encodes the column sequence number
          and ordinal position of the column. For more information, see
          the
      <code class="literal">
       POS
      </code>
      column description in
      <a class="xref" href="information-schema-innodb-virtual-table.html" title="28.4.29 The INFORMATION_SCHEMA INNODB_VIRTUAL Table">
       Section 28.4.29, “The INFORMATION_SCHEMA INNODB_VIRTUAL Table”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MTYPE
      </code>
     </p>
     <p>
      Stands for
      <span class="quote">
       “
       <span class="quote">
        main type
       </span>
       ”
      </span>
      . A numeric identifier for
          the column type. 1 =
      <code class="literal">
       VARCHAR
      </code>
      , 2 =
      <code class="literal">
       CHAR
      </code>
      , 3 =
      <code class="literal">
       FIXBINARY
      </code>
      , 4 =
      <code class="literal">
       BINARY
      </code>
      , 5 =
      <code class="literal">
       BLOB
      </code>
      , 6 =
      <code class="literal">
       INT
      </code>
      , 7 =
      <code class="literal">
       SYS_CHILD
      </code>
      , 8 =
      <code class="literal">
       SYS
      </code>
      , 9 =
      <code class="literal">
       FLOAT
      </code>
      , 10 =
      <code class="literal">
       DOUBLE
      </code>
      , 11 =
      <code class="literal">
       DECIMAL
      </code>
      , 12
          =
      <code class="literal">
       VARMYSQL
      </code>
      , 13 =
      <code class="literal">
       MYSQL
      </code>
      ,
          14 =
      <code class="literal">
       GEOMETRY
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PRTYPE
      </code>
     </p>
     <p>
      The
      <code class="literal">
       InnoDB
      </code>
      <span class="quote">
       “
       <span class="quote">
        precise type
       </span>
       ”
      </span>
      , a
          binary value with bits representing MySQL data type, character
          set code, and nullability.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LEN
      </code>
     </p>
     <p>
      The column length, for example 4 for
      <code class="literal">
       INT
      </code>
      and 8 for
      <code class="literal">
       BIGINT
      </code>
      . For character columns in
          multibyte character sets, this length value is the maximum
          length in bytes needed to represent a definition such as
      <code class="literal">
       VARCHAR(
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       )
      </code>
      ; that
          is, it might be
      <code class="literal">
       2*
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
      </code>
      ,
      <code class="literal">
       3*
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
      </code>
      , and so on
          depending on the character encoding.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       HAS_DEFAULT
      </code>
     </p>
     <p>
      A boolean value indicating whether a column that was added
          instantly using
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE ...
          ADD COLUMN
       </code>
      </a>
      with
      <code class="literal">
       ALGORITHM=INSTANT
      </code>
      has a default value. All columns added instantly have a
          default value, which makes this column an indicator of whether
          the column was added instantly.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DEFAULT_VALUE
      </code>
     </p>
     <p>
      The initial default value of a column that was added instantly
          using
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
          ... ADD COLUMN
       </code>
      </a>
      with
      <code class="literal">
       ALGORITHM=INSTANT
      </code>
      . If the default value is
      <code class="literal">
       NULL
      </code>
      or was not specified, this column
          reports
      <code class="literal">
       NULL
      </code>
      . An explicitly specified
          non-
      <code class="literal">
       NULL
      </code>
      default value is shown in an
          internal binary format. Subsequent modifications of the column
          default value do not change the value reported by this column.
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045077099136">
   </a>
   Example
  </h4>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa86344422"><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_COLUMNS <span class="token keyword">where</span> TABLE_ID <span class="token operator">=</span> <span class="token number">71</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> 71
         NAME<span class="token punctuation">:</span> col1
          POS<span class="token punctuation">:</span> 0
        MTYPE<span class="token punctuation">:</span> 6
       PRTYPE<span class="token punctuation">:</span> 1027
          LEN<span class="token punctuation">:</span> 4
  HAS_DEFAULT<span class="token punctuation">:</span> 0
DEFAULT_VALUE<span class="token punctuation">:</span> NULL
<span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 2. 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> 71
         NAME<span class="token punctuation">:</span> col2
          POS<span class="token punctuation">:</span> 1
        MTYPE<span class="token punctuation">:</span> 2
       PRTYPE<span class="token punctuation">:</span> 524542
          LEN<span class="token punctuation">:</span> 10
  HAS_DEFAULT<span class="token punctuation">:</span> 0
DEFAULT_VALUE<span class="token punctuation">:</span> NULL
<span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 3. 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> 71
         NAME<span class="token punctuation">:</span> col3
          POS<span class="token punctuation">:</span> 2
        MTYPE<span class="token punctuation">:</span> 1
       PRTYPE<span class="token punctuation">:</span> 524303
          LEN<span class="token punctuation">:</span> 10
  HAS_DEFAULT<span class="token punctuation">:</span> 0
DEFAULT_VALUE<span class="token punctuation">:</span> NULL</span></code></pre>
  </div>
  <h4>
   <a name="idm46045077096128">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      You must have the
      <a class="link" href="privileges-provided.html#priv_process">
       <code class="literal">
        PROCESS
       </code>
      </a>
      privilege to query this table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      <a class="link" href="information-schema-columns-table.html" title="28.3.8 The INFORMATION_SCHEMA COLUMNS Table">
       <code class="literal">
        COLUMNS
       </code>
      </a>
      table or the
      <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
       <code class="literal">
        SHOW COLUMNS
       </code>
      </a>
      statement to view
          additional information about the columns of this table,
          including data types and default values.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
