Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual SHOW COLUMNS Syntax

SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

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 Section 20.31, “Extensions to SHOW Statements”.

SHOW COLUMNS displays information only for those columns for which you have some privilege.

| 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 MySQL sometimes changes data types when you create or alter a table. The conditions under which this occurs are described in Section, “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 tbl_name FROM db_name syntax. In other words, these two statements are equivalent:

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 is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.

  • If Key is UNI, the column is the first column of a UNIQUE index. (A UNIQUE index permits multiple NULL values, but you can tell whether the column permits NULL by checking the Null field.)

  • If Key is MUL, 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. This is NULL if the column has an explicit default of NULL, or if the column definition has no DEFAULT clause.

The Extra field contains any additional information that is available about a given column. The value is nonempty in these cases:

  • auto_increment for columns that have the AUTO_INCREMENT attribute

  • on update CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns that have the ON UPDATE CURRENT_TIMESTAMP attribute

  • VIRTUAL GENERATED or VIRTUAL STORED for generated columns

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 Section 13.8.1, “DESCRIBE Syntax”.

The SHOW CREATE TABLE, SHOW TABLE STATUS, and SHOW INDEX statements also provide information about tables. See Section 13.7.5, “SHOW Syntax”.

Download this Manual
User Comments
  Posted by Jeroen van den Eijkhof on November 20, 2004
When programming in PHP, ASP and the like I for example want to get the values from an "enum"

when querying like:
SHOW columns FROM table
if you load the result in an array it will look like this:
Where the number, [x], gives the same value as the name, [name].

Good to know when getting the values for a enum field.
If you want to do this in PHP here is a good example: ->read user comments
  Posted by Son Nguyen on July 7, 2008
It's convenient to display information about a table, running status, server configurations. However, it is expensive! For example, a simple "SHOW COLUMNS FROM SomeTable" create a disk-based temporary table. Read more about it here:

I would suggest query caching enabled also for this SHOW COLUMNS. Since the table is not gonna change very frequent.
  Posted by Baron Schwartz on April 30, 2009
Note that not all privileges are displayed when using FULL. For example, DELETE isn't shown. If you really need to know whether you have a given permission, the best way I know is to try the operation you want to do, and see if you get an error. For DELETE, for example, you can DELETE FROM tbl LIMIT 0.
Sign Up Login You must be logged in to post a comment.