How to get database structure in MySQL via query?

In MySQL, retrieving database structure typically involves viewing tables, columns, and their data types within the database. This is crucial for database maintenance, optimization, or migration. Here are several common methods to retrieve MySQL database structure information:

1. Using the SHOW Command

To view the list of all tables in the database, you can use:

sql
SHOW TABLES;

To view the structure of a specific table, including columns, data types, and whether NULL is allowed, you can use:

sql
SHOW COLUMNS FROM table_name;

Alternatively,

sql
DESCRIBE table_name;

2. Using INFORMATION_SCHEMA

INFORMATION_SCHEMA is a special database provided by MySQL that contains metadata for all other databases. You can query INFORMATION_SCHEMA to retrieve more detailed database structure information.

For example, to retrieve information about all tables in a specific database, you can use:

sql
SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'database_name';

To retrieve detailed information about all columns of a specific table, such as data types and whether NULL is allowed:

sql
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'database_name' AND table_name = 'table_name';

3. Using Third-Party Tools

Besides SQL commands, you can use various third-party database management tools to visually inspect and manage database structure, such as phpMyAdmin, MySQL Workbench, etc. These tools typically provide a graphical interface, making it more intuitive and understandable to view database structure.

Real-World Example

In my previous work experience, we needed to review and optimize the existing database structure. I first used DESCRIBE and INFORMATION_SCHEMA queries to collect detailed information about all tables and columns. This helped us identify unused columns and columns with improperly configured indexes. Based on this information, we performed database restructuring and index optimization, ultimately improving query performance and data consistency.

These are some common methods to retrieve database structure in MySQL, and I hope this helps you.