Columns in the table are a series of cells that can stores text, numbers, and images. Every column stores one value for each row in a table. When we work with the MySQL server, it is common to display the column information from a particular table. In this section, we are going to discuss how to display or list columns in an existing table.
MySQL provides two ways for displaying the column information:
- MySQL SHOW COLUMNS Statement
- MySQL DESCRIBE Statement
Let us discuss both in detail.
MySQL SHOW COLUMNS Statement
SHOW COLUMNS statement in MySQL is a more flexible way to display the column information in a given table. It can also support views. Using this statement, we will get only that column information for which we have some privilege.
Syntax
The following is a syntax to display the column information in a specified table:
SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
{FROM | IN} table_name
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]Let’s discuss the syntax parameters in detail.
The EXTENDED is an optional keyword to display the information, including hidden columns. MySQL uses hidden columns internally that are not accessible by users.
The FULL is also an optional keyword to display the column information, including collation, comments, and the privileges we have for each column.
The table_name is the name of a table from which we are going to show column information.
The db_name is the name of a database containing a table from which we will show column information.
The LIKE or WHERE clause is used to display only the matched column information in a given table.
We can also use the alternative of table_name FROM db_name syntax as db_name.tbl_name. Therefore, the below statements are equivalent:
SHOW COLUMNS FROM mytable_name FROM mydb_name;
OR,
SHOW COLUMNS FROM mydb_name.mytable_name;SHOW COLUMNS Statement Example
Let us understand how this statement works in MySQL through various examples.
Suppose we have a table named student_info in a sample database that contains the data as follows:
Next, if we want to get the columns information of this table, we can use the statement as follows:
mysql> SHOW COLUMNS FROM student_info;We will see the below output:
If we want to filter the columns of a table, we need to use the LIKE or WHERE clause in the statement. See the below query:
mysql> SHOW COLUMNS FROM student_info LIKE 's%'; This query shows the column information that starts with the letter S only. See the below output:
If we want to display hidden column information, we need to add the FULL keyword to the SHOW COLUMNS statement as follows:
mysql> SHOW FULL COLUMNS FROM student_info; It returns the below output that displays all columns information of the student_info table in the sample database.
The SHOW COLUMNS statement provides the below information for each column in a given table:
Field: It indicates the name of the column in a given table.
Type: It indicates the data type of each column.
Collation: It is used to sequence the order of a specific character set. Here it indicates the string column for non-binary values and NULL for other columns. We will see this column only when we use the FULL keyword.
Null: It indicates the nullability of a column. If a column can store NULL values, it returns YES. And if a column cannot store NULL value, it contains NO value.
Key: It indicates the indexing of the columns as PRI, UNI, and MUL. Let us understand this field in detail.
- If we have not specified any key, it means the column is not indexed. Otherwise, index as a secondary column in a multiple-column.
- If the column is specified as a PRI, it means the column is a PRIMARY KEY or one of the fields in a multiple-column PRIMARY KEY.
- If the column is specified as a UNI, it means the column contains a UNIQUE index.
- If the column is specified as a MUL, it means the column is the first column of a non-unique index where we can use a given value multiple times.
- If the column is specified by more than one key-value, this field displays the key which has the highest priority (the key priority is in the order of PRI, UNI, and MUL).
Default: It indicates the default value to the column. If the column includes no DEFAULT clause or has an explicit NULL default, it contains a NULL value.
Extra: It indicates the additional information related to a given column. This field is non-empty in the following cases:
- If the column is specified with the AUTO_INCREMENT attribute, its value is filled with auto_increment.
- If the column is specified with TIMESTAMP or DATETIME that have the ON UPDATE CURRENT_TIMESTAMP attribute, its value is filled with on update CURRENT_TIMESTAMP.
- For the generated columns, its value filled with VIRTUAL GENERATED or VIRTUAL STORED.
- If the column contains an expression default value, its value is filled with DEFAULT_GENERATED.
Privileges: It indicates the privileges that we have for the column. We will see this column only when we use the FULL keyword.
Comment: It indicates the comment that we have included in the column definition. We will see this column only when we use the FULL keyword.
MySQL DESCRIBE Statement
DESCRIBE statement in MySQL is also provides information similar to the SHOW COLUMNS command.
Syntax
The following is the syntax to display the column information in a given table:
{DESCRIBE | DESC} table_name;In this syntax, the DESCRIBE and DESC clause return the same result.
Example
If we want to show column information of students_info table, we can execute the below statement.
mysql> DESCRIBE students_info; After successful execution, it will give the output as below image:
How to display column information in MySQL Workbench?
We first launch the tool and log in with the username and password to display the given table’s column information in MySQL Workbench. Now, we need to do the following steps to show the column information:
1. Go to the Navigation tab and click on the Schema menu where all the previously created databases available. Select your desired database (for example, mstudentdb). It will pop up the following options.
2. Click on the Tables that show all tables stored in the mysqltestdb database. Select a table whose column information you want to display. Then, mouse hour on that table, it will show three icons. See the below image:
3. Click the icon (i) shown in the red rectangular box. We should get the screen as follows:
4. Finally, click on the “Columns” menu. We can see the column information as like below output.
Leave a Reply