Four implementations of viewing table structure in MySQL

1. Use the DESCRIBE or DESC commands

DESCRIBE(or its abbreviationDESC) is the easiest and most direct way to display the column information of the table.

grammar：

DESCRIBE table_name;
-- or
DESC table_name;

Example:
Suppose there is a name calledemployees The table can be viewed in this way:

DESCRIBE employees;
-- or
DESC employees;

2. Use the SHOW COLUMNS command

SHOW COLUMNSThe command can also display column information for the table, but it provides more details such as the default values of the columns and additional information.

grammar:

SHOW COLUMNS FROM table_name;
-- or
SHOW COLUMNS FROM table_name FROM database_name;

Example:

SHOW COLUMNS FROM employees;
-- or
SHOW COLUMNS FROM employees FROM mydatabase;

3. Use the SHOW CREATE TABLE command

SHOW CREATE TABLEThe command can display the complete SQL statements that create a table, including all column definitions, indexes, constraints and other detailed information.

grammar:

SHOW CREATE TABLE table_name;

Example:

SHOW CREATE TABLE employees;

4. Use INFORMATION_SCHEMA

INFORMATION_SCHEMA It is a system database that contains information about database metadata. By queryINFORMATION_SCHEMA.COLUMNStable, you can obtain detailed column information.

grammar:

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'table_name' 
AND TABLE_SCHEMA = 'database_name';

Example:

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'employees' 
AND TABLE_SCHEMA = 'mydatabase';

Sample outputAssumptionemployees The structure of the table is as follows:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(100),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

useDESCRIBEor DESCCommand output:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(11)          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(100)     | NO   |     | NULL    |                |
| position   | varchar(100)     | YES  |     | NULL    |                |
| hire_date  | date             | YES  |     | NULL    |                |
| salary     | decimal(10,2)    | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

useSHOW COLUMNSCommand output:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(11)          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(100)     | NO   |     | NULL    |                |
| position   | varchar(100)     | YES  |     | NULL    |                |
| hire_date  | date             | YES  |     | NULL    |                |
| salary     | decimal(10,2)    | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

useSHOW CREATE TABLECommand output:

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `position` varchar(100) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

useINFORMATION_SCHEMAOutput:

+-----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME      | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE  | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE       | COLUMN_KEY | EXTRA          | PRIVILEGES | COLUMN_COMMENT |
+-----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+
| def       | mydatabase   | employees       | id   | 1           | NULL  | NO      | int      | NULL                  | NULL                  | 10                    | 0             | NULL              | NULL               | NULL           | int(11)         | PRI        | auto_increment | SELECT,INSERT,UPDATE,REFERENCES |                |
| def       | mydatabase   | employees       | name | 2           | NULL  | NO      | varchar  | 100                  | 300                  | NULL                 | NULL          | NULL              | utf8mb4            | utf8mb4_0900_ai_ci | varchar(100)  | MUL        |                | SELECT,INSERT,UPDATE,REFERENCES |                |
| def       | mydatabase   | employees       | position | 3           | NULL  | YES     | varchar  | 100                  | 300                  | NULL                 | NULL          | NULL              | utf8mb4            | utf8mb4_0900_ai_ci | varchar(100)  |                |                | SELECT,INSERT,UPDATE,REFERENCES |                |
| def       | mydatabase   | employees       | hire_date | 4           | NULL  | YES     | date     | NULL                  | NULL                  | NULL                 | NULL          | 0                | NULL               | NULL           | date            |                |                | SELECT,INSERT,UPDATE,REFERENCES |                |
| def       | mydatabase   | employees       | salary | 5           | NULL  | YES     | decimal  | NULL                  | NULL                  | 10                   | 2             | NULL              | NULL               | NULL           | decimal(10,2)   |                |                | SELECT,INSERT,UPDATE,REFERENCES |                |
+-----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+

Summarize

DESCRIBE or DESC: Basic structure suitable for quick viewing of tables.
SHOW COLUMNS: Provides more detailed column information, such as default values and additional information.
SHOW CREATE TABLE: Displays the complete SQL statement that creates a table, including all column definitions, indexes, and constraints.
INFORMATION_SCHEMA: Get the most detailed metadata information by querying the system database.

This is the end of this article about the three implementations of table structure in MySQL. For more related contents of MySQL to view table structure, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!