MySQL Describe Table

MySQL is an open–source relational database management system (RDBMS). It is one of the most popular databases globally, known for its reliability, and scalability. MySQL is designed to run on various operating systems, including Windows, Linux, macOS, and more. It is known for its high performance, quick data retrieval, and efficient data storage. It provides robust security features, including user authentication, access control, and data encryption.

In this article, we will understand how to use the DESCRIBE TABLE command using steps to follow, examples, and so on.

DESCRIBE TABLE in MYSQL

The DESCRIBE TABLE statement, also commonly written as DESC TABLE or DESCRIBE, is a MySQL command used to retrieve metadata about a table. It offers a better way to view essential information about the columns within a specified table, such as the data type, nullability, and key constraints. DESCRIBE TABLE displays information about primary and foreign key constraints, providing insights into the relationships between tables.

Understanding the structure of databases is important for good database handling and development. In MySQL, the­ DESCRIBE TABLE command is helpful. It gives us detailed info about a table’s structure and attributes.

How To Describe a Table In MySQL?

The DESCRIBE TABLE command in MySQL is a use­ful Data Definition Language (DDL) command. It’s good for inspecting the structure of a table. It shows users about columns, data types, constraints, and other attributes. It give­s an overall look at how the table­ is structured. To understand the DESCRIBE TABLE in MYSQL we should know SQL Data Types, SQL Types of Indexes, and SQL Types of Keys.

Steps for Executing DESCRIBE Statement

Executing the­ DESCRIBE TABLE statement is simple. First, ope­n a MySQL command-line interface or a database­ management tool(WorkBench).

Then, run the following command:

Query:

DESCRIBE table_name;

Explanation: This command will return a result containing information about the specified table.

The output of the DESCRIBE statement includes columns like:

• Field: the column name.
• Type the data type of the column.
• Null: whether the column allows NULL values or not.
• Key: index information of the column.
• Default : the default value set in the column.
• Extra : additional information about the column.

How to Display Table Information in MySQL Workbench?

In MySQL Workbench, you can view table information by:

• Open MySQL Workbench and connect to the database.
• In the Navigator(that left side panel), locate that database and expand it.
• Click on “Tables“.
• Right-click on that table you want to describe.
• Click on “Table Inspector” to view detailed information.

Let’s see the visual representation of How to Display Table Information in MySQL Workbench

Example of DESCRIBE TABLE

We have two tables named student, fees with some columns.

Let’s perform the DESCRIBE TABLE command in the student table and understand the output.

Query:

DESCRIBE students;

Output:

Describing student table

Explanation:

Lets understand the output of each column in the table, including its data type, nullability, constraints, and default values:

• student_id : This shown as type int. The NO shows it cannot be NULL. As a primary key (PRI). As Extra its have auto_increment for autoge­nerated integer.
• first_name : This is a varchar(50) column, it can be­ up to 50 characters in length. The NO shows it cannot be NULL.
• last_name : same as per the first_name column.
• date_of_birth : This column is of type of date. The NULL field is marked as YES, meaning it allows NULL values.
• email : A varchar(100) type­ column, it can be­ up to 100 characters in length. The NULL field is marked as YES, meaning it stores NULL values. The UNI key shows it has a unique constraint.
• Registration_date : This is a timestamp column. It can have NULL values. The CURRENT_TIMESTAMP and DEFAULT_GENERATED set automatically default value using the current timestamp when we add a new row.
• is_active : This column is of type tinyint(1), it allows NULL values, and the default value is 1.

Let’s perform the DESCRIBE TABLE command in the fees table and understand the output.

Query:

DESCRIBE fees

Output:

Describing fees table

Explanation:

• fee_id : This column is type of int. The NULL value field is NO, so it can’t be empty. As a primary key (PRI). As Extra its have auto_increment for autoge­nerated integer.
• student_id : This column is of type int. It sores NULL values. MUL means it’s part of multiple indexes. This column has a foreign key referencing to the student_id in the student table.
• amount : This column is type of decimals. It can have up to 8 digits in total, 2 being after the dot. The NULL value field is NO, so it can’t be empty.
• payment_date : This column is type of date. It stores NULL values. MUL means it’s part of multiple indexes.

MySQL SHOW COLUMNS Command

Another way to get table information is using the SHOW COLUMNS command.

Query:

SHOW COLUMNS FROM student;

This command gives details same to the DESCRIBE statement.

Both “SHOW COLUMNS FROM student” and “DESCRIBE stude­nt” provide details on the “student” table­ but in different ways. “SHOW COLUMNS FROM student” pre­sents details in table form. It shows fie­ld name, data type, if it allows null, key, de­fault value, and more. Alternative­ly, “DESCRIBE student” offers a simple list. This simple list may not show relevant info, unlike “SHOW COLUMNS.” There­fore, it’s up to the user to se­lect either a de­tailed table or a simple list. But, in most cases, both commands will shows similar results.

Conclusion

MySQL table structure knowledge is good for database handling. You can understand it using commands like DESCRIBE, SHOW COLUMNS, and tools like MySQL Workbench. This will show you table parts like column names, data types, and constraints. This article show easy instructions for these commands. It helps make managing data more efficiently.