MySQL - 描述表

MySQL：描述表结构和分析查询

理解数据库模式是任何开发人员的基础。MySQL 提供了多种命令来检查表的结构。此外，一个相关但不同的命令 EXPLAIN 对于分析 MySQL 如何执行查询至关重要。

本教程涵盖了这两个方面：如何描述表的列以及如何深入了解查询性能。

第一部分：描述表结构

要检索表的定义——其列、数据类型、键和其他属性——你可以使用 DESCRIBE、其快捷方式 DESC 或 SHOW COLUMNS。

DESCRIBE 和 DESC 语句

DESCRIBE 语句（及其较短的别名 DESC）是快速查看表布局最常用的方法。

语法

DESCRIBE table_name;
-- 或快捷方式 --
DESC table_name;

示例

首先，让我们创建一个示例 CUSTOMERS 表。

CREATE TABLE CUSTOMERS(
    ID INT AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    EMAIL VARCHAR(100) UNIQUE NOT NULL,
    AGE INT,
    MEMBER_SINCE DATE DEFAULT (CURRENT_DATE),
    PRIMARY KEY(ID)
);

现在，让我们描述其结构。

DESCRIBE CUSTOMERS;

输出

输出清晰地列出了每列及其属性：

字段	类型	空	键	默认值	额外
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(100)	NO		NULL
EMAIL	varchar(100)	NO	UNI	NULL
AGE	int	YES		NULL
MEMBER_SINCE	date	YES		CURRENT_DATE

SHOW COLUMNS 语句

SHOW COLUMNS 是另一个与 DESCRIBE 产生完全相同输出的命令。它在脚本中有时更具可读性。

SHOW COLUMNS FROM CUSTOMERS;

第二部分：使用 EXPLAIN 分析查询执行

虽然 DESCRIBE 向你展示一个表 是什么，但 EXPLAIN 向你展示 MySQL 如何 执行查询。它揭示了查询执行计划：表将如何连接，将使用哪些索引，以及将检查多少行。它是性能调优不可或缺的工具。

重要区别：不要混淆 DESCRIBE table_name 和 EXPLAIN SELECT ...。前者描述模式，后者分析查询的执行。

语法

EXPLAIN [FORMAT = format_name] SELECT_statement;
-- format_name 可以是 TRADITIONAL, JSON, 或 TREE (MySQL 8.0+)

示例：分析简单查询

让我们分析一个针对 CUSTOMERS 表的简单 SELECT 查询。

EXPLAIN SELECT ID, NAME FROM CUSTOMERS WHERE EMAIL = 'test@example.com';

因为我们在 EMAIL 列上有一个 UNIQUE 键，MySQL 可以非常高效地使用它来查找行。输出中的 type 列将显示 const 或 eq_ref，表示高度优化的查找。

理解 EXPLAIN 格式

FORMAT 选项改变了执行计划的显示方式。

FORMAT = TRADITIONAL（默认）

经典的表格格式。简洁但需要知识来解释 type、key 和 Extra 等列。

EXPLAIN FORMAT=TRADITIONAL SELECT * FROM CUSTOMERS;

FORMAT = JSON

提供更详细、结构化的输出，包括查询的成本估算。非常适合程序化分析。

EXPLAIN FORMAT=JSON SELECT * FROM CUSTOMERS;

FORMAT = TREE (MySQL 8.0+)

以树状显示计划，这对于可视化复杂连接和子查询可能更直观。

EXPLAIN FORMAT=TREE SELECT * FROM CUSTOMERS;

实际应用：在代码中描述表

以编程方式描述表对于 ORM（对象关系映射器）、数据库管理工具或动态代码生成器非常有用。

Node.js (使用 mysql2)
Python (使用 mysql-connector-python)



### 代码
此示例获取 `CUSTOMERS` 表的模式并以格式化方式打印。

```javascript
// 文件名：describeTable.js
const mysql = require('mysql2/promise');

async function describeTable(tableName) {
  try {
    const pool = mysql.createPool({
      host: 'localhost', user: 'root', password: 'password', database: 'TUTORIALS'
    });

    const sql = `DESCRIBE ??`; // 使用 ?? 进行表/标识符转义

    const [rows, fields] = await pool.query(sql, [tableName]);

    console.log(`表 '${tableName}' 的模式`);
    console.log('--------------------------------------------------');
    rows.forEach(col => {
        console.log(
            `字段: ${col.Field}, 类型: ${col.Type}, 可空: ${col.Null}, 键: ${col.Key || 'N/A'}`
        );
    });
    console.log('--------------------------------------------------');

    await pool.end();

  } catch (error) {
    console.error(`描述表失败：`, error);
  }
}

describeTable('CUSTOMERS');

输出

Schema for table: 'CUSTOMERS'
--------------------------------------------------
Field: ID, Type: int, Nullable: NO, Key: PRI
Field: NAME, Type: varchar(100), Nullable: NO, Key: N/A
Field: EMAIL, Type: varchar(100), Nullable: NO, Key: UNI
Field: AGE, Type: int, Nullable: YES, Key: N/A
Field: MEMBER_SINCE, Type: date, Nullable: YES, Key: N/A
--------------------------------------------------

代码

此示例使用 mysql-connector-python 来获取和显示表模式。

describe_table.py

import mysql.connector
from mysql.connector import Error

def describe_table(table_name):
    """ 连接到 MySQL 并描述给定表。 """
    try:
        with mysql.connector.connect(
            host='localhost', user='root', password='password', database='TUTORIALS'
        ) as connection:
            query = f"DESCRIBE {table_name}"

            # 使用 dictionary=True 可以通过列名访问行
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(query)
                columns_info = cursor.fetchall()

                print(f"表 '{table_name}' 的模式")
                print('-' * 60)
                for col in columns_info:
                    print(
                        f"字段: {col['Field']:<15} 类型: {col['Type']:<15} "
                        f"可空: {col['Null']:<5} 键: {col['Key'] or 'N/A'}"
                    )
                print('-' * 60)

    except Error as e:
        print(f"错误: {e}")

if __name__ == "__main__":
    describe_table('CUSTOMERS')

输出

Schema for table: 'CUSTOMERS'
------------------------------------------------------------
Field: ID              Type: int             Nullable: NO    Key: PRI
Field: NAME            Type: varchar(100)    Nullable: NO    Key: N/A
Field: EMAIL           Type: varchar(100)    Nullable: NO    Key: UNI
Field: AGE             Type: int             Nullable: YES   Key: N/A
Field: MEMBER_SINCE    Type: date            Nullable: YES   Key: N/A
------------------------------------------------------------