Interpreting DESCRIBE Output in MySQL

You should understand how to interpret the output of the DESCRIBE table_name statement. This is of particular importance both for this study guide and for taking certification exams. In both cases, when it's necessary that you know the structure of a table, it will be shown as the output of a DESCRIBE statement in the same format as that displayed by the mysql program. For example, assume that a question requires you to know about a table named City. The table's structure will be presented as follows (example):

 
mysql> DESCRIBE City;

+-------------+----------+------+-----+---------+----------------+

| Field       | Type     | Null | Key | Default | Extra          |

+-------------+----------+------+-----+---------+----------------+

| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |

| Name        | char(35) | NO   |     |         |                |

| CountryCode | char(3)  | NO   |     |         |                |

| District    | char(20) | NO   |     |         |                |

| Population  | int(11)  | NO   |     | 0       |                |

+-------------+----------+------+-----+---------+----------------+

The output of the DESCRIBE statement contains one row for each column in the table. The most important features of the output are as follows:

  • The Field value indicates the column name.

  • The Type value shows the column data type.

  • The Null indicator is the word YES if the column can contain NULL values and NO if it cannot. In the example shown, Null is NO for all columns of the City table. This indicates that none of that table's columns can contain NULL values.

  • The Key indicator may be empty or contain one of three non-empty values:

    • An empty Key value indicates that the column in question either isn't indexed or is indexed only as a secondary column in a multiple-column, non-unique index. For purposes of the exam, you should assume that if Key is empty, it's because the column is not indexed at all.

    • If the Key value is the keyword PRI (as in the output shown for the ID column), this indicates that the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.

    • If the Key value is the keyword UNI, this indicates that the column is the first column of a unique-valued index that cannot contain NULL values.

    • If the Key value is the keyword MUL, this indicates that the column is the first column of a non-unique index or a unique-valued index that can contain NULL values.

    It's possible that more than one of the Key values may apply to a given column of a table. For example, a column that is a PRIMARY KEY might also be part of other indexes. When it's possible for more than one of the Key values to describe an index, DESCRIBE displays the one with the highest priority, in the order PRI, UNI, MUL.

    Because a column can be part of several indexes, the Key values do not necessarily provide an exhaustive description of a table's indexes. However, for purposes of the exam, you should assume that the table descriptions given provide all the information needed to correctly answer the question.

  • Default shows the column's default value. This is the value that MySQL assigns to the column when a statement that creates a new record does not provide an explicit value for the column. (For example, this can happen with the INSERT, REPLACE, and LOAD DATA INFILE statements.)

  • The ExTRa value displays other details about the column. The only ExTRa detail about which you need be concerned for the exam is the value auto_increment. This value indicates that the column has the AUTO_INCREMENT attribute. The ID column shown in the example is such an instance.

 

Вас заинтересует / Intresting for you:

Connecting with a MySQL databa...
Connecting with a MySQL databa... 1148 views Valerij Sun, 26 Aug 2018, 11:27:52
MySQL Database Overview
MySQL Database Overview 2822 views Antoniy Thu, 19 Sep 2019, 11:26:55
Determining characteristics of...
Determining characteristics of... 678 views Valerij Sun, 26 Aug 2018, 12:52:37
Importing MySQL module for Pyt...
Importing MySQL module for Pyt... 683 views Valerij Sun, 26 Aug 2018, 09:30:11

Comments on Interpreting DESCRIBE Output in MySQL

Be the first to comment
Please login to comment