<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-columns-table">
      </a>
      28.3.8 The INFORMATION_SCHEMA COLUMNS Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045080255664">
  </a>
  <p>
   The
   <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 provides
      information about columns in tables. The related
   <a class="link" href="information-schema-st-geometry-columns-table.html" title="28.3.35 The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table">
    <code class="literal">
     ST_GEOMETRY_COLUMNS
    </code>
   </a>
   table provides
      information about table columns that store spatial data. See
   <a class="xref" href="information-schema-st-geometry-columns-table.html" title="28.3.35 The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table">
    Section 28.3.35, “The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table”
   </a>
   .
  </p>
  <p>
   The
   <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 has these columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_CATALOG
      </code>
     </p>
     <p>
      The name of the catalog to which the table containing the
          column belongs. This value is always
      <code class="literal">
       def
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_SCHEMA
      </code>
     </p>
     <p>
      The name of the schema (database) to which the table
          containing the column belongs.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_NAME
      </code>
     </p>
     <p>
      The name of the table containing the column.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COLUMN_NAME
      </code>
     </p>
     <p>
      The name of the column.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ORDINAL_POSITION
      </code>
     </p>
     <p>
      The position of the column within the table.
      <code class="literal">
       ORDINAL_POSITION
      </code>
      is necessary because you
          might want to say
      <code class="literal">
       ORDER BY
          ORDINAL_POSITION
      </code>
      . Unlike
      <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
       <code class="literal">
        SHOW
          COLUMNS
       </code>
      </a>
      ,
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      from
          the
      <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 does not have
          automatic ordering.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COLUMN_DEFAULT
      </code>
     </p>
     <p>
      The default value for the column. This is
      <code class="literal">
       NULL
      </code>
      if the column has an explicit default
          of
      <code class="literal">
       NULL
      </code>
      , or if the column definition
          includes no
      <code class="literal">
       DEFAULT
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       IS_NULLABLE
      </code>
     </p>
     <p>
      The column nullability. The value is
      <code class="literal">
       YES
      </code>
      if
      <code class="literal">
       NULL
      </code>
      values can be stored in the column,
      <code class="literal">
       NO
      </code>
      if not.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DATA_TYPE
      </code>
     </p>
     <p>
      The column data type.
     </p>
     <p>
      The
      <code class="literal">
       DATA_TYPE
      </code>
      value is the type name only
          with no other information. The
      <code class="literal">
       COLUMN_TYPE
      </code>
      value contains the type name and possibly other information
          such as the precision or length.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHARACTER_MAXIMUM_LENGTH
      </code>
     </p>
     <p>
      For string columns, the maximum length in characters.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHARACTER_OCTET_LENGTH
      </code>
     </p>
     <p>
      For string columns, the maximum length in bytes.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NUMERIC_PRECISION
      </code>
     </p>
     <p>
      For numeric columns, the numeric precision.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NUMERIC_SCALE
      </code>
     </p>
     <p>
      For numeric columns, the numeric scale.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DATETIME_PRECISION
      </code>
     </p>
     <p>
      For temporal columns, the fractional seconds precision.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHARACTER_SET_NAME
      </code>
     </p>
     <p>
      For character string columns, the character set name.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COLLATION_NAME
      </code>
     </p>
     <p>
      For character string columns, the collation name.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COLUMN_TYPE
      </code>
     </p>
     <p>
      The column data type.
     </p>
     <p>
      The
      <code class="literal">
       DATA_TYPE
      </code>
      value is the type name only
          with no other information. The
      <code class="literal">
       COLUMN_TYPE
      </code>
      value contains the type name and possibly other information
          such as the precision or length.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COLUMN_KEY
      </code>
     </p>
     <p>
      Whether the column is indexed:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         If
         <code class="literal">
          COLUMN_KEY
         </code>
         is empty, the column
              either is not indexed or is indexed only as a secondary
              column in a multiple-column, nonunique index.
        </p>
       </li>
       <li class="listitem">
        <p>
         If
         <code class="literal">
          COLUMN_KEY
         </code>
         is
         <code class="literal">
          PRI
         </code>
         , the column is a
         <code class="literal">
          PRIMARY
              KEY
         </code>
         or is one of the columns in a
              multiple-column
         <code class="literal">
          PRIMARY KEY
         </code>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         If
         <code class="literal">
          COLUMN_KEY
         </code>
         is
         <code class="literal">
          UNI
         </code>
         , the column is the first column of
              a
         <code class="literal">
          UNIQUE
         </code>
         index. (A
         <code class="literal">
          UNIQUE
         </code>
         index permits multiple
         <code class="literal">
          NULL
         </code>
         values, but you can tell whether
              the column permits
         <code class="literal">
          NULL
         </code>
         by checking the
         <code class="literal">
          Null
         </code>
         column.)
        </p>
       </li>
       <li class="listitem">
        <p>
         If
         <code class="literal">
          COLUMN_KEY
         </code>
         is
         <code class="literal">
          MUL
         </code>
         , the column is the first column of
              a nonunique index in which multiple occurrences of a given
              value are permitted within the column.
        </p>
       </li>
      </ul>
     </div>
     <p>
      If more than one of the
      <code class="literal">
       COLUMN_KEY
      </code>
      values
          applies to a given column of a table,
      <code class="literal">
       COLUMN_KEY
      </code>
      displays the one with the
          highest priority, in the order
      <code class="literal">
       PRI
      </code>
      ,
      <code class="literal">
       UNI
      </code>
      ,
      <code class="literal">
       MUL
      </code>
      .
     </p>
     <p>
      A
      <code class="literal">
       UNIQUE
      </code>
      index may be displayed as
      <code class="literal">
       PRI
      </code>
      if it cannot contain
      <code class="literal">
       NULL
      </code>
      values and there is no
      <code class="literal">
       PRIMARY KEY
      </code>
      in the table. A
      <code class="literal">
       UNIQUE
      </code>
      index may display as
      <code class="literal">
       MUL
      </code>
      if several columns form a composite
      <code class="literal">
       UNIQUE
      </code>
      index; although the combination of
          the columns is unique, each column can still hold multiple
          occurrences of a given value.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       EXTRA
      </code>
     </p>
     <p>
      Any additional information that is available about a given
          column. The value is nonempty in these cases:
     </p>
     <a class="indexterm" name="idm46045080175840">
     </a>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <code class="literal">
          auto_increment
         </code>
         for columns that have
              the
         <code class="literal">
          AUTO_INCREMENT
         </code>
         attribute.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          on update CURRENT_TIMESTAMP
         </code>
         for
         <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
          <code class="literal">
           TIMESTAMP
          </code>
         </a>
         or
         <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
          <code class="literal">
           DATETIME
          </code>
         </a>
         columns that have
              the
         <code class="literal">
          ON UPDATE CURRENT_TIMESTAMP
         </code>
         attribute.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          STORED GENERATED
         </code>
         or
         <code class="literal">
          VIRTUAL
              GENERATED
         </code>
         for generated columns.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          DEFAULT_GENERATED
         </code>
         for columns that have
              an expression default value.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PRIVILEGES
      </code>
     </p>
     <p>
      The privileges you have for the column.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COLUMN_COMMENT
      </code>
     </p>
     <p>
      Any comment included in the column definition.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       GENERATION_EXPRESSION
      </code>
     </p>
     <a class="indexterm" name="idm46045080157888">
     </a>
     <p>
      For generated columns, displays the expression used to compute
          column values. Empty for nongenerated columns. For information
          about generated columns, see
      <a class="xref" href="create-table-generated-columns.html" title="15.1.20.8 CREATE TABLE and Generated Columns">
       Section 15.1.20.8, “CREATE TABLE and Generated Columns”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SRS_ID
      </code>
     </p>
     <p>
      This value applies to spatial columns. It contains the column
      <code class="literal">
       SRID
      </code>
      value that indicates the spatial
          reference system for values stored in the column. See
      <a class="xref" href="spatial-type-overview.html" title="13.4.1 Spatial Data Types">
       Section 13.4.1, “Spatial Data Types”
      </a>
      , and
      <a class="xref" href="spatial-reference-systems.html" title="13.4.5 Spatial Reference System Support">
       Section 13.4.5, “Spatial Reference System Support”
      </a>
      . The value is
      <code class="literal">
       NULL
      </code>
      for nonspatial columns and spatial
          columns with no
      <code class="literal">
       SRID
      </code>
      attribute.
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045080149200">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      In
      <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
       <code class="literal">
        SHOW COLUMNS
       </code>
      </a>
      , the
      <code class="literal">
       Type
      </code>
      display includes values from several
          different
      <a class="link" href="information-schema-columns-table.html" title="28.3.8 The INFORMATION_SCHEMA COLUMNS Table">
       <code class="literal">
        COLUMNS
       </code>
      </a>
      columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHARACTER_OCTET_LENGTH
      </code>
      should be the same
          as
      <code class="literal">
       CHARACTER_MAXIMUM_LENGTH
      </code>
      , except for
          multibyte character sets.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHARACTER_SET_NAME
      </code>
      can be derived from
      <code class="literal">
       COLLATION_NAME
      </code>
      . For example, if you say
      <code class="literal">
       SHOW FULL COLUMNS FROM t
      </code>
      , and you see in
          the
      <code class="literal">
       COLLATION_NAME
      </code>
      column a value of
      <code class="literal">
       utf8mb4_swedish_ci
      </code>
      , the character set is
          what appears before the first underscore:
      <code class="literal">
       utf8mb4
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   Column information is also available from the
   <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
    <code class="literal">
     SHOW COLUMNS
    </code>
   </a>
   statement. See
   <a class="xref" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
    Section 15.7.7.6, “SHOW COLUMNS Statement”
   </a>
   . The following statements are
      nearly equivalent:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa16191902"><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">SELECT</span> <span class="token keyword">COLUMN_NAME</span><span class="token punctuation">,</span> DATA_TYPE<span class="token punctuation">,</span> IS_NULLABLE<span class="token punctuation">,</span> COLUMN_DEFAULT
  <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">COLUMNS</span>
  <span class="token keyword">WHERE</span> <span class="token keyword">table_name</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">tbl_name</em>'</span>
  <span class="token punctuation">[</span><span class="token operator">AND</span> table_schema <span class="token operator">=</span> <span class="token string">'<em class="replaceable">db_name</em>'</span><span class="token punctuation">]</span>
  <span class="token punctuation">[</span><span class="token operator">AND</span> <span class="token keyword">column_name</span> <span class="token operator">LIKE</span> <span class="token string">'<em class="replaceable">wild</em>'</span><span class="token punctuation">]</span>

<span class="token keyword">SHOW</span> <span class="token keyword">COLUMNS</span>
  <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token punctuation">[</span><span class="token keyword">FROM</span> <em class="replaceable">db_name</em><span class="token punctuation">]</span>
  <span class="token punctuation">[</span><span class="token operator">LIKE</span> <span class="token string">'<em class="replaceable">wild</em>'</span><span class="token punctuation">]</span></code></pre>
  </div>
  <p>
   Information about generated invisible primary key columns is
      visible in this table by default. You can cause such information
      to be hidden by setting
   <a class="link" href="server-system-variables.html#sysvar_show_gipk_in_create_table_and_information_schema">
    <code class="literal">
     show_gipk_in_create_table_and_information_schema
      = OFF
    </code>
   </a>
   . For more information, see
   <a class="xref" href="create-table-gipks.html" title="15.1.20.11 Generated Invisible Primary Keys">
    Section 15.1.20.11, “Generated Invisible Primary Keys”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
