SHOW COLUMNS
Syntax
SHOW COLUMNS
displays information about the columns in a given table. It also works for views. The LIKE
clause, if present, indicates which column names to match. The WHERE
clause can be given to select rows using more general conditions, as discussed in , "Extensions to SHOW
Statements".
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 | | | |
| Country | char(3) | NO | UNI | | |
| District | char(20) | YES | MUL | | |
| Population | int(11) | NO | | 0 | |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
If the data types differ from what you expect them to be based on a CREATE TABLE
statement, note that MariaDB sometimes changes data types when you create or alter a table. The conditions under which this occurs are described in , "Silent Column Specification Changes".
The FULL
keyword causes the output to include the column collation and comments, as well as the privileges you have for each column.
You can use db_name.tbl_name
as an alternative to the
syntax. In other words, these two statements are equivalent:
tbl_name
FROM db_name
mysql>SHOW COLUMNS FROM mytable FROM mydb;
mysql>SHOW COLUMNS FROM mydb.mytable;
SHOW COLUMNS
displays the following values for each table column:
Field
indicates the column name.
Type
indicates the column data type.
Collation
indicates the collation for nonbinary string columns, or NULL
for other columns. This value is displayed only if you use the FULL
keyword.
The Null
field contains YES
if NULL
values can be stored in the column, NO
if not.
The Key
field indicates whether the column is indexed:
- If
Key
is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index. - If
Key
isPRI
, the column is aPRIMARY KEY
or is one of the columns in a multiple-columnPRIMARY KEY
. - If
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
field.) - If
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 Key
values applies to a given column of a table, Key
displays the one with the highest priority, in the order PRI
, UNI
, MUL
.
A UNIQUE
index may be displayed as PRI
if it cannot contain NULL
values and there is no PRIMARY KEY
in the table. A UNIQUE
index may display as MUL
if several columns form a composite UNIQUE
index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.
The Default
field indicates the default value that is assigned to the column.
The Extra
field contains any additional information that is available about a given column. The value is auto_increment
if the column was created with the AUTO_INCREMENT
keyword and empty otherwise.
Privileges
indicates the privileges you have for the column. This value is displayed only if you use the FULL
keyword.
Comment
indicates any comment the column has. This value is displayed only if you use the FULL
keyword.
SHOW FIELDS
is a synonym for SHOW COLUMNS
. You can also list a table's columns with the mysqlshow db_name
tbl_name
command.
The DESCRIBE
statement provides information similar to SHOW COLUMNS
. See , "DESCRIBE
Syntax".
The SHOW CREATE TABLE
, SHOW TABLE STATUS
, and SHOW INDEX
statements also provide information about tables. See , "SHOW
Syntax".