The COLUMNS
table provides
information about columns in tables. The related
ST_GEOMETRY_COLUMNS
table provides
information about table columns that store spatial data. See
Section 28.3.35, “The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table”.
The COLUMNS
table has these columns:
TABLE_CATALOG
The name of the catalog to which the table containing the column belongs. This value is always
def
.TABLE_SCHEMA
The name of the schema (database) to which the table containing the column belongs.
TABLE_NAME
The name of the table containing the column.
COLUMN_NAME
The name of the column.
ORDINAL_POSITION
The position of the column within the table.
ORDINAL_POSITION
is necessary because you might want to sayORDER BY ORDINAL_POSITION
. UnlikeSHOW COLUMNS
,SELECT
from theCOLUMNS
table does not have automatic ordering.COLUMN_DEFAULT
The default value for the column. This is
NULL
if the column has an explicit default ofNULL
, or if the column definition includes noDEFAULT
clause.IS_NULLABLE
The column nullability. The value is
YES
ifNULL
values can be stored in the column,NO
if not.DATA_TYPE
The column data type.
The
DATA_TYPE
value is the type name only with no other information. TheCOLUMN_TYPE
value contains the type name and possibly other information such as the precision or length.CHARACTER_MAXIMUM_LENGTH
For string columns, the maximum length in characters.
CHARACTER_OCTET_LENGTH
For string columns, the maximum length in bytes.
NUMERIC_PRECISION
For numeric columns, the numeric precision.
NUMERIC_SCALE
For numeric columns, the numeric scale.
DATETIME_PRECISION
For temporal columns, the fractional seconds precision.
CHARACTER_SET_NAME
For character string columns, the character set name.
COLLATION_NAME
For character string columns, the collation name.
COLUMN_TYPE
The column data type.
The
DATA_TYPE
value is the type name only with no other information. TheCOLUMN_TYPE
value contains the type name and possibly other information such as the precision or length.COLUMN_KEY
Whether the column is indexed:
If
COLUMN_KEY
is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.If
COLUMN_KEY
isPRI
, the column is aPRIMARY KEY
or is one of the columns in a multiple-columnPRIMARY KEY
.If
COLUMN_KEY
isUNI
, the column is the first column of aUNIQUE
index. (AUNIQUE
index permits multipleNULL
values, but you can tell whether the column permitsNULL
by checking theNull
column.)If
COLUMN_KEY
isMUL
, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.
If more than one of the
COLUMN_KEY
values applies to a given column of a table,COLUMN_KEY
displays the one with the highest priority, in the orderPRI
,UNI
,MUL
.A
UNIQUE
index may be displayed asPRI
if it cannot containNULL
values and there is noPRIMARY KEY
in the table. AUNIQUE
index may display asMUL
if several columns form a compositeUNIQUE
index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.EXTRA
Any additional information that is available about a given column. The value is nonempty in these cases:
auto_increment
for columns that have theAUTO_INCREMENT
attribute.on update CURRENT_TIMESTAMP
forTIMESTAMP
orDATETIME
columns that have theON UPDATE CURRENT_TIMESTAMP
attribute.STORED GENERATED
orVIRTUAL GENERATED
for generated columns.DEFAULT_GENERATED
for columns that have an expression default value.
PRIVILEGES
The privileges you have for the column.
COLUMN_COMMENT
Any comment included in the column definition.
GENERATION_EXPRESSION
For generated columns, displays the expression used to compute column values. Empty for nongenerated columns. For information about generated columns, see Section 15.1.20.8, “CREATE TABLE and Generated Columns”.
SRS_ID
This value applies to spatial columns. It contains the column
SRID
value that indicates the spatial reference system for values stored in the column. See Section 13.4.1, “Spatial Data Types”, and Section 13.4.5, “Spatial Reference System Support”. The value isNULL
for nonspatial columns and spatial columns with noSRID
attribute.
Notes
In
SHOW COLUMNS
, theType
display includes values from several differentCOLUMNS
columns.CHARACTER_OCTET_LENGTH
should be the same asCHARACTER_MAXIMUM_LENGTH
, except for multibyte character sets.CHARACTER_SET_NAME
can be derived fromCOLLATION_NAME
. For example, if you saySHOW FULL COLUMNS FROM t
, and you see in theCOLLATION_NAME
column a value ofutf8mb4_swedish_ci
, the character set is what appears before the first underscore:utf8mb4
.
Column information is also available from the
SHOW COLUMNS
statement. See
Section 15.7.7.6, “SHOW COLUMNS Statement”. The following statements are
nearly equivalent:
Press CTRL+C to copySELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name' [AND table_schema = 'db_name'] [AND column_name LIKE 'wild'] SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE 'wild']
Information about generated invisible primary key columns is
visible in this table by default. You can cause such information
to be hidden by setting
show_gipk_in_create_table_and_information_schema
= OFF
. For more information, see
Section 15.1.20.11, “Generated Invisible Primary Keys”.