The World of MySQL & MariaDB

Beginner’s Guide: Quick MySQL Table View (CLI & GUI)

Table Management
目次

1. Introduction

When using MySQL, checking the table definitions is extremely important. Accurately understanding the table definitions is essential for reviewing and revising database design, troubleshooting, and even optimizing SQL queries. In this article, we will explain the steps for “checking MySQL table definitions” in a way that is easy for beginners to understand. We’ll cover approaches from basic commands to GUI tools, offering a multifaceted perspective.

2. Easy for beginners! Basic commands for checking MySQL table definitions

When checking MySQL table definitions, basic commands that can be run from the command line are extremely handy. Below, we explain the specific commands and how to use them.

Check table overview with the DESCRIBE command

The DESCRIBE command is the simplest way to view a table’s structure in MySQL.
  • Command example:
  DESCRIBE table_name;
  • Output example:
  +---------+--------------+------+-----+---------+-------+
  | Field   | Type         | Null | Key | Default | Extra |
  +---------+--------------+------+-----+---------+-------+
  | id      | int          | NO   | PRI | NULL    |       |
  | name    | varchar(100) | YES  |     | NULL    |       |
  +---------+--------------+------+-----+---------+-------+
  • Key information:
  • Field: Column name
  • Type: Data type
  • Null: Whether NULL is allowed
  • Key: Primary key or foreign key information
  • Default: Default value
  • Extra: Additional attributes such as AUTO_INCREMENT

Retrieve detailed information with the SHOW COLUMNS command

The SHOW COLUMNS command is similar to DESCRIBE but is used when you need to display more detailed information.
  • Command example:
  SHOW COLUMNS FROM table_name;
  • Advantages:
  • Column comments and encoding information may also be displayed.

Get the SQL statement with the SHOW CREATE TABLE command

The SHOW CREATE TABLE command is used to view the SQL statement that created a table.
  • Command example:
  SHOW CREATE TABLE table_name;
  • Output example:
  CREATE TABLE `example_table` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • Use case:
  • Useful when migrating the table structure to another environment.

3. Detailed Inspection of MySQL Table Definitions with INFORMATION_SCHEMA

MySQL’s INFORMATION_SCHEMA is a system view for retrieving database metadata. It can be used when you want to examine table definitions in more detail.

Retrieve Column Information with INFORMATION_SCHEMA.COLUMNS

The INFORMATION_SCHEMA.COLUMNS view allows you to obtain detailed information about columns belonging to a specific table.
  • SQL query example:
  SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'table_name';
  • Key information:
  • COLUMN_NAME: column name
  • DATA_TYPE: data type (e.g., INT, VARCHAR)
  • IS_NULLABLE: whether NULL is allowed
  • COLUMN_DEFAULT: default value

Review Overall Table Information with INFORMATION_SCHEMA.TABLES

INFORMATION_SCHEMA.TABLES provides metadata about the tables themselves.
  • SQL query example:
  SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, CREATE_TIME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = 'database_name';
  • Sample output:
  +-------------+------------+-------------+---------------------+
  | TABLE_NAME  | TABLE_ROWS | DATA_LENGTH | CREATE_TIME         |
  +-------------+------------+-------------+---------------------+
  | example_tbl | 1000       | 16384       | 2024-01-01 00:00:00 |
  +-------------+------------+-------------+---------------------+

4. How to Easily View MySQL Table Definitions with GUI Tools

If you’re not comfortable with the command line or prefer to see information visually, using a GUI tool is recommended.

Using MySQL Workbench

MySQL Workbench is the official MySQL GUI tool, allowing you to intuitively inspect table structures.
  • Steps:
  1. Launch Workbench and connect to the database.
  2. Select the desired table from the “Schema” tab.
  3. Right‑click the table name and choose “Table Inspector.”
  4. View column information on the “Columns” tab.

Using phpMyAdmin

phpMyAdmin is a web‑based MySQL management tool available on many shared hosting services.
  • Steps:
  1. Log in to phpMyAdmin and select the database.
  2. Click the “Structure” tab of the target table.
  3. Column information will be displayed in a list.

5. FAQ on Verifying MySQL Table Definitions

Q1. How do I modify a table definition in MySQL?

  • Answer:
  ALTER TABLE table_name ADD COLUMN new_column_name data_type;

Q2. How can I view only a specific column?

  • Answer:
  SELECT COLUMN_NAME, DATA_TYPE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'table_name' AND COLUMN_NAME = 'column_name';

Q3. How do I export a table definition?

  • Answer:
  • Use SHOW CREATE TABLE, then copy and save the SQL statement.
  • Or use the “Export” feature of a GUI tool.

6. Summary and Next Steps

This article explained multiple approaches to checking table definitions in MySQL. It covers a wide range, from basic commands for beginners to advanced techniques for retrieving detailed information, and even how to use GUI tools. As a next step, we recommend learning about “MySQL database optimization” and “index design.”