MySQL 9.0 Release Notes
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_CATALOGThe name of the catalog to which the table containing the column belongs. This value is always
def.TABLE_SCHEMAThe name of the schema (database) to which the table containing the column belongs.
TABLE_NAMEThe name of the table containing the column.
COLUMN_NAMEThe name of the column.
ORDINAL_POSITIONThe position of the column within the table.
ORDINAL_POSITIONis necessary because you might want to sayORDER BY ORDINAL_POSITION. UnlikeSHOW COLUMNS,SELECTfrom theCOLUMNStable does not have automatic ordering.COLUMN_DEFAULTThe default value for the column. This is
NULLif the column has an explicit default ofNULL, or if the column definition includes noDEFAULTclause.IS_NULLABLEThe column nullability. The value is
YESifNULLvalues can be stored in the column,NOif not.DATA_TYPEThe column data type.
The
DATA_TYPEvalue is the type name only with no other information. TheCOLUMN_TYPEvalue contains the type name and possibly other information such as the precision or length.CHARACTER_MAXIMUM_LENGTHFor string columns, the maximum length in characters.
CHARACTER_OCTET_LENGTHFor string columns, the maximum length in bytes.
NUMERIC_PRECISIONFor numeric columns, the numeric precision.
NUMERIC_SCALEFor numeric columns, the numeric scale.
DATETIME_PRECISIONFor temporal columns, the fractional seconds precision.
CHARACTER_SET_NAMEFor character string columns, the character set name.
COLLATION_NAMEFor character string columns, the collation name.
COLUMN_TYPEThe column data type.
The
DATA_TYPEvalue is the type name only with no other information. TheCOLUMN_TYPEvalue contains the type name and possibly other information such as the precision or length.COLUMN_KEYWhether the column is indexed:
If
COLUMN_KEYis empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.If
COLUMN_KEYisPRI, the column is aPRIMARY KEYor is one of the columns in a multiple-columnPRIMARY KEY.If
COLUMN_KEYisUNI, the column is the first column of aUNIQUEindex. (AUNIQUEindex permits multipleNULLvalues, but you can tell whether the column permitsNULLby checking theNullcolumn.)If
COLUMN_KEYisMUL, 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_KEYvalues applies to a given column of a table,COLUMN_KEYdisplays the one with the highest priority, in the orderPRI,UNI,MUL.A
UNIQUEindex may be displayed asPRIif it cannot containNULLvalues and there is noPRIMARY KEYin the table. AUNIQUEindex may display asMULif several columns form a compositeUNIQUEindex; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.EXTRAAny additional information that is available about a given column. The value is nonempty in these cases:
auto_incrementfor columns that have theAUTO_INCREMENTattribute.on update CURRENT_TIMESTAMPforTIMESTAMPorDATETIMEcolumns that have theON UPDATE CURRENT_TIMESTAMPattribute.STORED GENERATEDorVIRTUAL GENERATEDfor generated columns.DEFAULT_GENERATEDfor columns that have an expression default value.
PRIVILEGESThe privileges you have for the column.
COLUMN_COMMENTAny comment included in the column definition.
GENERATION_EXPRESSIONFor 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_IDThis value applies to spatial columns. It contains the column
SRIDvalue 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 isNULLfor nonspatial columns and spatial columns with noSRIDattribute.
Notes
In
SHOW COLUMNS, theTypedisplay includes values from several differentCOLUMNScolumns.CHARACTER_OCTET_LENGTHshould be the same asCHARACTER_MAXIMUM_LENGTH, except for multibyte character sets.CHARACTER_SET_NAMEcan be derived fromCOLLATION_NAME. For example, if you saySHOW FULL COLUMNS FROM t, and you see in theCOLLATION_NAMEcolumn 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:
SELECT 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”.