The COLUMNS
table provides
information about columns in tables.
The related ST_GEOMETRY_COLUMNS
table
is a view on COLUMNS
that provides
information about table columns that store spatial data. See
Section 24.24, “The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table”.
INFORMATION_SCHEMA Name |
SHOW Name |
Remarks |
---|---|---|
TABLE_CATALOG |
def |
|
TABLE_SCHEMA |
||
TABLE_NAME |
||
COLUMN_NAME |
Field |
|
ORDINAL_POSITION |
see notes | |
COLUMN_DEFAULT |
Default |
|
IS_NULLABLE |
Null |
|
DATA_TYPE |
Type |
|
CHARACTER_MAXIMUM_LENGTH |
Type |
|
CHARACTER_OCTET_LENGTH |
||
NUMERIC_PRECISION |
Type |
|
NUMERIC_SCALE |
Type |
|
DATETIME_PRECISION |
Type |
|
CHARACTER_SET_NAME |
||
COLLATION_NAME |
Collation |
|
COLUMN_TYPE |
Type |
MySQL extension |
COLUMN_KEY |
Key |
MySQL extension |
EXTRA |
Extra |
MySQL extension |
PRIVILEGES |
Privileges |
MySQL extension |
COLUMN_COMMENT |
Comment |
MySQL extension |
GENERATION_EXPRESSION |
MySQL extension | |
SRS_ID |
MySQL extension |
Notes:
In
SHOW
, theType
display includes values from several differentCOLUMNS
columns.ORDINAL_POSITION
is necessary because you might want to sayORDER BY ORDINAL_POSITION
. UnlikeSHOW
,SELECT
does not have automatic ordering.CHARACTER_OCTET_LENGTH
should be the same asCHARACTER_MAXIMUM_LENGTH
, except for multibyte character sets.CHARACTER_SET_NAME
can be derived fromCollation
. For example, if you saySHOW FULL COLUMNS FROM t
, and you see in theCollation
column a value ofutf8_swedish_ci
, the character set is what is before the first underscore:utf8
.GENERATION_EXPRESSION
is nonempty for generated columns and displays the expression used to compute column values. For information about generated columns, see Section 13.1.18.8, “CREATE TABLE and Generated Columns”.The
EXTRA
column containsVIRTUAL GENERATED
orVIRTUAL STORED
for generated columns.SRS_ID
applies to spatial columns. It contains the columnSRID
value that indicates the the spatial reference system for values stored in the column. See Section 11.5.1, “Spatial Data Types”, and Section 11.5.5, “Spatial Reference System Support”. The value isNULL
for nonspatial columns and spatial columns with noSRID
attribute.SRS_ID
was added in MySQL 8.0-3.
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']
-Gelomon