SHOW [FULL] COLUMNS {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. 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 19.30, “Extensions to SHOW
Statements”.
SHOW COLUMNS
displays information
only for those columns that you have some privilege for.
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 MySQL sometimes changes data types when you create or alter
a table. The conditions under which this occurs are described in
Section 12.1.14.1, “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
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 allows multiple
NULL
values, but you can tell whether the
column allows 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 allowed 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 Section 12.8.1, “DESCRIBE
Syntax”.
The SHOW CREATE TABLE
,
SHOW TABLE STATUS
, and
SHOW INDEX
statements also
provide information about tables. See Section 12.4.5, “SHOW
Syntax”.
User Comments
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:
array([0],[Field],[1],[Type],[2],[Null],[3],[Key],[4],[Default],[5],[Extra])
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:
http://se2.php.net/manual/en/function.mysql-fetch-field.php ->read user comments
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: http://bugs.mysql.com/bug.php?id=10210
I would suggest query caching enabled also for this SHOW COLUMNS. Since the table is not gonna change very frequent.
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.
Add your own comment.