MySQL DESCRIBE table

Introduction to MySQL DESCRIBE table

Often, we forget the contents, names, and attributes assigned to the columns of the tables. In this case, we need to get the details of the table structure. MySQL provides the functionality to describe the table using the DESCRIBE statement. There are many alternative statements available in MySQL for describing the table, such as desc that is the short name of the describe, show columns which are internally used by the describe query itself.

Syntax:

The syntax of the describe statement is simple, as shown below:

DESCRIBE name_of_table;

Or

DESC name_of_table;

Where name_of_table is the table name that you want to describe.

Example of MySQL DESCRIBE table

Let us consider one example; we have a table named developers inside the educba named database. Now, we want to describe that table.

Code:

desc developers;

Output:

We can see in the output that we get different details of each column of the table. The field stands for the name o the column; the type is the column’s data type. Null stands for whether the column allows the NULL value in it or not. If the value of the Null is YES, then it allows NULL value, while in the case of NO, a NULL value is restricted for storing in that column. The key indicates that the column is being indexed. This can be either primary key indexing, foreign key indexing, or unique index. The default specifies the default value that will be assigned to the column. Extra tells us about more description of the column and is set to auto_increment value instead of blank value if the AUTO_INCREMENTATION option is set to that column while table creation.

From the above output, we can see that the developer’s table includes six columns and the name of the columns are mentioned in the Field column of the output; the primary key is defined on the developer_id column, and also auto_increment option is applied on the developer_id column. The name column has a unique index defined on it which means that duplicate values cannot be stored in this column of the developer’s table.developer_id and team_id columns cannot contain NULL values in it, which all other columns allow the storage of NULL value in them.

Let us use the alternative of the DESC statement to describe the developer’s table using the following statement.

Code:

describe developers;

Output:

We can see that both of the queries give the same output.

EXPLAIN Statement to DESCRIBE Table

EXPLAIN statement is used in MySQL to describe the complex queries and the execution and row retrieval of each of the tables in the queries that may contain more than one table in it. This query can also be used for SELECT, INSERT, UPDATE, and DELETE operations. This statement can also be used to describe that table structure.

Let us try using EXPLAIN statement to describe the same table developers using the following query statement.

Code:

explain developers;

Output:

We can observe that the EXPLAIN query gives the same output as that of the describe statement.

Internal Execution of DESCRIBE Statement

When we execute the describe query, then internally, it executes the same flow as that of show columns from that table name statement.

Let us see the syntax of the SHOW COLUMNS statement.

Code:

SHOW COLUMNS FROM name_of_table;

Where name_of_table is the table name whose column details need to be retrieved.

Let us use the show columns statement to describe the same table developers using the following query statement.

Code:

show columns from developers;

Output:

We can also mention the name of the database to which the table belongs in case if we are not using the same database to which table belongs, as shown in the below query statement.

Code:

SHOW COLUMNS FROM developers IN educba;

Output:

If we want to retrieve additional details about the columns of the table, we can use the FULL clause in the SHOW COLUMNS statement as used below in the query statement.

Code:

SHOW FULL COLUMNS FROM developers IN educba;

Output:

We can see the additional column details, including the collation, privileges, and comments associated with the columns of the table. Privileges include the operations that can be performed on the columns for that user. The comment stands for the comment if we have given it to the column while adding it to the table. If you want to display the description of the columns in the vertical format of the list, then you can use \G in the query.

Let us use the \G in the above command.

Code:

SHOW FULL COLUMNS FROM developers IN educba \G;

Output:

If we want to retrieve column details of only columns beginning with t character, you can use the following query statement.

Code:

SHOW FULL COLUMNS FROM developers IN educba LIKE 't%';

Output:

The output gives the column details of only team_id and technology column as both of them begin with ‘t’ character.

Conclusion

We can use DESCRIBE or DESC statement to get the details of the columns of the table. Other alternatives used for describing the columns of the table are EXPLAIN statement which is most often used for describing the flow of the complex queries. Internally, describe the statement uses the SHOW COLUMNS statement to fetch the details of tables columns. We can directly use this statement to get details of the table columns.

Recommended Articles

This is a guide to the MySQL DESCRIBE table. Here we discuss the introduction, example, EXPLAIN statement and internal execution of DESCRIBE statement. You may also have a look at the following articles to learn more –

1. MySQL today()
2. MySQL INSERT IGNORE
3. MySQL EXISTS
4. MySQL Lock Table