Documentation Home
MySQL 9.1 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.4Mb
PDF (A4) - 40.5Mb
Man Pages (TGZ) - 259.5Kb
Man Pages (Zip) - 366.7Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


MySQL 9.1 Reference Manual  / ...  / INFORMATION_SCHEMA Tables  / INFORMATION_SCHEMA General Tables  /  The INFORMATION_SCHEMA COLUMNS Table

28.3.8 The INFORMATION_SCHEMA COLUMNS Table

TheCOLUMNS table provides information about columns in tables. The relatedST_GEOMETRY_COLUMNS table provides information about table columns that store spatial data. SeeSection 28.3.35, “The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table”.

TheCOLUMNS table has these columns:

  • TABLE_CATALOG

    The name of the catalog to which the table containing the column belongs. This value is alwaysdef.

  • 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 isNULL if the column has an explicit default ofNULL, or if the column definition includes noDEFAULT clause.

  • IS_NULLABLE

    The column nullability. The value isYES ifNULL values can be stored in the column,NO if not.

  • DATA_TYPE

    The column data type.

    TheDATA_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.

    TheDATA_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:

    • IfCOLUMN_KEY is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.

    • IfCOLUMN_KEY isPRI, the column is aPRIMARY KEY or is one of the columns in a multiple-columnPRIMARY KEY.

    • IfCOLUMN_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.)

    • IfCOLUMN_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 theCOLUMN_KEY values applies to a given column of a table,COLUMN_KEY displays the one with the highest priority, in the orderPRI,UNI,MUL.

    AUNIQUE 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, seeSection 15.1.20.8, “CREATE TABLE and Generated Columns”.

  • SRS_ID

    This value applies to spatial columns. It contains the columnSRID value that indicates the spatial reference system for values stored in the column. SeeSection 13.4.1, “Spatial Data Types”, andSection 13.4.5, “Spatial Reference System Support”. The value isNULL for nonspatial columns and spatial columns with noSRID attribute.

Notes

  • InSHOW 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 theSHOW COLUMNS statement. SeeSection 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  FROMtbl_name  [FROMdb_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 settingshow_gipk_in_create_table_and_information_schema = OFF. For more information, seeSection 15.1.20.11, “Generated Invisible Primary Keys”.