Movatterモバイル変換

MySQL 9.5 Reference Manual

15.7.7.6 SHOW COLUMNS Statement

SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}    {FROM | IN}tbl_name    [{FROM | IN}db_name]    [LIKE 'pattern' | WHEREexpr]

SHOW COLUMNS displays information about the columns in a given table. It also works for views.SHOW COLUMNS displays information only for those columns for which you have some privilege.

mysql> SHOW COLUMNS FROM City;+-------------+----------+------+-----+---------+----------------+| Field       | Type     | Null | Key | Default | Extra          |+-------------+----------+------+-----+---------+----------------+| ID          | int(11)  | NO   | PRI | NULL    | auto_increment || Name        | char(35) | NO   |     |         |                || CountryCode | char(3)  | NO   | MUL |         |                || District    | char(20) | NO   |     |         |                || Population  | int(11)  | NO   |     | 0       |                |+-------------+----------+------+-----+---------+----------------+

An alternative totbl_name FROMdb_name syntax isdb_name.tbl_name. These two statements are equivalent:

SHOW COLUMNS FROM mytable FROM mydb;SHOW COLUMNS FROM mydb.mytable;

The optionalEXTENDED keyword causes the output to include information about hidden columns that MySQL uses internally and are not accessible by users.

The optionalFULL keyword causes the output to include the column collation and comments, as well as the privileges you have for each column.

TheLIKE clause, if present, indicates which column names to match. TheWHERE clause can be given to select rows using more general conditions, as discussed inSection 28.8, “Extensions to SHOW Statements”.

The data types may differ from what you expect them to be based on aCREATE TABLE statement because MySQL sometimes changes data types when you create or alter a table. The conditions under which this occurs are described inSection 15.1.24.7, “Silent Column Specification Changes”.

SHOW COLUMNS displays the following values for each table column:

Field
The name of the column.
Type
The column data type.
Collation
The collation for nonbinary string columns, orNULL for other columns. This value is displayed only if you use theFULL keyword.
Null
The column nullability. The value isYES ifNULL values can be stored in the column,NO if not.
Key
Whether the column is indexed:
- IfKey is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.
- IfKey isPRI, the column is aPRIMARY KEY or is one of the columns in a multiple-columnPRIMARY KEY.
- IfKey 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 field.)
- IfKey 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 theKey values applies to a given column of a table,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.
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.
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.
- VIRTUAL GENERATED orSTORED GENERATED for generated columns.
- DEFAULT_GENERATED for columns that have an expression default value.
Privileges
The privileges you have for the column. This value is displayed only if you use theFULL keyword.
Comment
Any comment included in the column definition. This value is displayed only if you use theFULL keyword.

Table column information is also available from theINFORMATION_SCHEMACOLUMNS table. SeeSection 28.3.8, “The INFORMATION_SCHEMA COLUMNS Table”. The extended information about hidden columns is available only usingSHOW EXTENDED COLUMNS; it cannot be obtained from theCOLUMNS table.

You can list a table's columns with themysqlshowdb_nametbl_name command.

TheDESCRIBE statement provides information similar toSHOW COLUMNS. SeeSection 15.8.1, “DESCRIBE Statement”.

TheSHOW CREATE TABLE,SHOW TABLE STATUS, andSHOW INDEX statements also provide information about tables. SeeSection 15.7.7, “SHOW Statements”.

SHOW COLUMNS includes the table's generated invisible primary key, if it has one, by default. You can cause this information to be suppressed in the statement's output by settingshow_gipk_in_create_table_and_information_schema = OFF. For more information, seeSection 15.1.24.11, “Generated Invisible Primary Keys”.

PREV HOME UP NEXT