DESCRIBE

Description

DESCRIBE displays information about columns in a specified table. It is also applicable to views.

The displayed column information includes:

Field: column name.
Type: column data type.
Null: specifies whether a column value can be NULL. YES indicates that the value can be NULL. NO indicates that the value cannot be NULL.
Key: specifies whether a column is indexed. The options are as follows:
- PRI: specifies that the column is indexed by a primary key.
- UNI: specifies that the column is the first column in a unique index.
- MUL: specifies that the column is the first column in a non-unique index.
- NULL: specifies that the column is not indexed or is not the first column of a unique or non-unique index.
Default: default value of a column.
Extra: additional information about a column. The options are as follows:
- auto_increment: specifies that the column is an auto-increment column.
- on update CURRENT_TIMESTAMP: specifies that the column has the ON UPDATE constraint.
- VIRTUAL GENERATED: indicates a virtual generated column.
- STORED GENERATED: indicates a stored generated column.

Precautions

DESCRIBE is the shortcut key of SHOW COLUMNS. In addition, DESCRIBE can be used to display view information.
By default, DESCRIBE is used to display all columns in the table. If the name of a column in the table is given, the statement is used to display only the information about the specified column. If a wildcard string (% or _) is given, the statement is used to display only the output of the column whose name matches the string. You do not need to enclose a character string in quotation marks (") unless the character string contains spaces or other special characters.

Syntax

{DESCRIBE | DESC}
  tbl_name [col_name | wild]

Parameters

wild

A pattern string that can contain wildcard characters (% and _).

Examples

-- Create the t01 table.
m_db=# CREATE TABLE t01 (
	c1 int AUTO_INCREMENT COMMENT 'this is c1',
	c2 int NOT NULL CHECK (c2 > 0),
	c3 text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT 'this is c3',
	c4 int DEFAULT 5 COMMENT 'this is c4',
	c5 datetime ON UPDATE current_timestamp COMMENT 'this is c5',
	c6 int GENERATED ALWAYS AS (c2 + c4) STORED COMMENT 'this is c6',
	c7 int UNIQUE COMMENT 'this is c7',
	c8 int,
	c9 text CHARSET utf8mb4 COLLATE utf8mb4_bin COMMENT 'this is c9',
	c10 int COMMENT 'this is c10',
	c11 int COMMENT 'this is c11',
	c12 int COMMENT 'this is c12',
	CONSTRAINT PRIMARY KEY (c1) USING btree COMMENT 'this is primary key',
	CONSTRAINT FOREIGN KEY (c8) REFERENCES t01(c1),
	CONSTRAINT CHECK (c10 > 0),
	CONSTRAINT UNIQUE (c11) COMMENT 'this is unique constraint',
	c13 text COMMENT 'this is c13',
	c14 int COMMENT 'this is c14',
	INDEX USING btree (c13(10)) COMMENT 'this is index',
	INDEX (c13(10),c14) USING btree COMMENT 'this is index too'
);

-- Display column information.
m_db=# DESCRIBE t01;
 Field |   Type   | Null | Key | Default |            Extra            
-------+----------+------+-----+---------+-----------------------------
 c1    | integer  | NO   | PRI |         | auto_increment              
 c2    | integer  | NO   |     |         |                             
 c3    | text     | YES  |     |         |                             
 c4    | integer  | YES  |     | 5       |                             
 c5    | datetime | YES  |     |         | on update CURRENT_TIMESTAMP 
 c6    | integer  | YES  |     |         | STORED GENERATED            
 c7    | integer  | YES  | UNI |         |                             
 c8    | integer  | YES  |     |         |                             
 c9    | text     | YES  |     |         |                             
 c10   | integer  | YES  |     |         |                             
 c11   | integer  | YES  | UNI |         |                             
 c12   | integer  | YES  |     |         |                             
 c13   | text     | YES  | MUL |         |                             
 c14   | integer  | YES  |     |         |                             
(14 rows)

-- Fuzzy match column information.
m_db=# DESCRIBE t01 c_;
 Field |   Type   | Null | Key | Default |            Extra            
-------+----------+------+-----+---------+-----------------------------
 c1    | integer  | NO   | PRI |         | auto_increment              
 c2    | integer  | NO   |     |         |                             
 c3    | text     | YES  |     |         |                             
 c4    | integer  | YES  |     | 5       |                             
 c5    | datetime | YES  |     |         | on update CURRENT_TIMESTAMP 
 c6    | integer  | YES  |     |         | STORED GENERATED            
 c7    | integer  | YES  | UNI |         |                             
 c8    | integer  | YES  |     |         |                             
 c9    | text     | YES  |     |         |                                                       
(9 rows)

m_db=# DESCRIBE t01 c%;
 Field |   Type   | Null | Key | Default |            Extra            
-------+----------+------+-----+---------+-----------------------------
 c1    | integer  | NO   | PRI |         | auto_increment              
 c2    | integer  | NO   |     |         |                             
 c3    | text     | YES  |     |         |                             
 c4    | integer  | YES  |     | 5       |                             
 c5    | datetime | YES  |     |         | on update CURRENT_TIMESTAMP 
 c6    | integer  | YES  |     |         | STORED GENERATED            
 c7    | integer  | YES  | UNI |         |                             
 c8    | integer  | YES  |     |         |                             
 c9    | text     | YES  |     |         |                                                       
 c10   | integer  | YES  |     |         |                             
 c11   | integer  | YES  | UNI |         |                             
 c12   | integer  | YES  |     |         |                             
 c13   | text     | YES  | MUL |         |                             
 c14   | integer  | YES  |     |         |                             
(14 rows)

m_db=# DROP TABLE t01;