MySQL - 显示列

MySQL：描述表结构

了解表的结构——其列、数据类型、键以及其他属性——是任何数据库开发人员或管理员的一项基本任务。MySQL 提供了多种方式来检索这些元数据，从简单的命令到强大、标准化的信息模式。

The SHOW COLUMNS 和 DESCRIBE 语句

SHOW COLUMNS 和 DESCRIBE（或其简写 DESC）是简单、MySQL 特有的语句，提供表结构的人类可读摘要。它们的功能是相同的。

语法

语法非常简单：

SHOW COLUMNS {FROM | IN} table_name [{FROM | IN} db_name];

-- Or using DESCRIBE
DESCRIBE table_name;

示例

让我们首先在名为 TUTORIALS 的数据库中创建一个示例 CUSTOMERS 表。

USE TUTORIALS;

CREATE TABLE CUSTOMERS(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(255) NULL,
    SALARY DECIMAL(10, 2) NULL
);

现在，让我们使用 SHOW COLUMNS 来检查其结构。

SHOW COLUMNS FROM CUSTOMERS;

输出

输出提供了每列的清晰概览。

字段	类型	允许为空	键	默认值	额外
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(100)	NO		NULL
AGE	int	NO		NULL
ADDRESS	varchar(255)	YES		NULL
SALARY	decimal(10,2)	YES		NULL

使用 DESCRIBE CUSTOMERS; 将产生完全相同的结果。

过滤结果

SHOW COLUMNS 支持可选的 LIKE 和 WHERE 子句来过滤结果，这对于大型表很有用。

使用 LIKE 子句

要查找名称以“A”开头的列：

SHOW COLUMNS FROM CUSTOMERS LIKE 'A%';

使用 WHERE 子句

要查找所有数据类型为 int 的列：

SHOW COLUMNS FROM CUSTOMERS WHERE Type = 'int';

最佳实践：INFORMATION_SCHEMA

对于程序化访问和更详细、标准化的信息，最佳实践是查询 INFORMATION_SCHEMA。这是 MySQL（以及其他 SQL 数据库）中可用的虚拟数据库，提供对数据库元数据的访问。INFORMATION_SCHEMA 中的 COLUMNS 表特别有用。

示例：查询 INFORMATION_SCHEMA

此查询检索更详细的信息，并且比 SHOW COLUMNS 更灵活。

SELECT
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_KEY,
    COLUMN_DEFAULT,
    EXTRA,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'TUTORIALS'
    AND TABLE_NAME = 'CUSTOMERS'
ORDER BY
    ORDINAL_POSITION;

这种方法在应用程序中更受欢迎，因为它返回一个易于解析的标准结果集，而 SHOW COLUMNS 的输出有时在不同 MySQL 版本之间可能有所不同。

在客户端程序中检索列信息

以下是在不同编程语言中获取表架构信息的方法，演示了简单的 DESCRIBE 命令和更强大的 INFORMATION_SCHEMA 查询。

Python
Node.js
Java
PHP



此 Python 示例使用 `mysql-connector-python` 并查询 `INFORMATION_SCHEMA` 以获取结构化数据。

import mysql.connector

DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': 'your_password',
    'database': 'TUTORIALS'
}
TABLE_NAME = 'CUSTOMERS'

def describe_table_columns():
    query = """
        SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
        ORDER BY ORDINAL_POSITION
    """
    try:
        with mysql.connector.connect(**DB_CONFIG) as cnx:
            with cnx.cursor(dictionary=True) as cursor:
                cursor.execute(query, (DB_CONFIG['database'], TABLE_NAME))
                columns = cursor.fetchall()
                print(f"表 '{TABLE_NAME}' 的列:")
                for col in columns:
                    print(
                        f"- {col['COLUMN_NAME']} ({col['COLUMN_TYPE']})， "
                        f"可空: {col['IS_NULLABLE']}"
                    )
    except mysql.connector.Error as err:
        print(f"数据库错误: {err}")

describe_table_columns()

输出

表 'CUSTOMERS' 的列:
- ID (int), Nullable: NO
- NAME (varchar(100)), Nullable: NO
- AGE (int), Nullable: NO
- ADDRESS (varchar(255)), Nullable: YES
- SALARY (decimal(10,2)), Nullable: YES



此 Node.js 示例使用 `mysql2/promise` 和预处理语句查询 `INFORMATION_SCHEMA`。

const mysql = require('mysql2/promise');

const DB_CONFIG = {
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'TUTORIALS'
};
const TABLE_NAME = 'CUSTOMERS';

async function describeTable() {
    let connection;
    const query = `
        SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        ORDER BY ORDINAL_POSITION;`;
    try {
        connection = await mysql.createConnection(DB_CONFIG);
        const [columns] = await connection.execute(query, [DB_CONFIG.database, TABLE_NAME]);
        console.log(`表 '${TABLE_NAME}' 的列:`);
        columns.forEach(col => {
            console.log(
                `- ${col.COLUMN_NAME} (${col.COLUMN_TYPE})， ` +
                `可空: ${col.IS_NULLABLE}`
            );
        });
    } catch (error) {
        console.error(`获取表描述失败: ${error.message}`);
    } finally {
        if (connection) await connection.end();
    }
}

describeTable();

输出

表 'CUSTOMERS' 的列:
- ID (int), Nullable: NO
- NAME (varchar(100)), Nullable: NO
- AGE (int), Nullable: NO
- ADDRESS (varchar(255)), Nullable: YES
- SALARY (decimal(10,2)), Nullable: YES



此 Java 示例使用 JDBC 和 `PreparedStatement` 对 `INFORMATION_SCHEMA` 进行安全且健壮的查询。

import java.sql.*;

public class ShowColumns {
    static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    static final String USER = "root";
    static final String PASS = "your_password";
    static final String TABLE_NAME = "CUSTOMERS";

    public static void main(String[] args) {
        String query = "SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS " +
                       "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(query)) {

            pstmt.setString(1, "TUTORIALS");
            pstmt.setString(2, TABLE_NAME);

            System.out.println("表 '" + TABLE_NAME + "' 的列:");
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("- %s (%s)， 可空: %s\n",
                        rs.getString("COLUMN_NAME"),
                        rs.getString("COLUMN_TYPE"),
                        rs.getString("IS_NULLABLE"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

输出

表 'CUSTOMERS' 的列:
- ID (int), Nullable: NO
- NAME (varchar(100)), Nullable: NO
- AGE (int), Nullable: NO
- ADDRESS (varchar(255)), Nullable: YES
- SALARY (decimal(10,2)), Nullable: YES



此 PHP 示例使用 `mysqli` 扩展和预处理语句，以确保安全性和可靠性。

<?php
$db_config = [
    'host' => 'localhost',
    'user' => 'root',
    'pass' => 'your_password',
    'name' => 'TUTORIALS'
];
$table_name = 'CUSTOMERS';

$mysqli = new mysqli($db_config['host'], $db_config['user'], $db_config['pass'], $db_config['name']);
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$query = "SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
          ORDER BY ORDINAL_POSITION";

$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $db_config['name'], $table_name);
$stmt->execute();
$result = $stmt->get_result();

echo "表 '$table_name' 的列:<br>";
while ($col = $result->fetch_assoc()) {
    echo "- " . htmlspecialchars($col['COLUMN_NAME'])
       . " (" . htmlspecialchars($col['COLUMN_TYPE']) . ")， "
       . "可空: " . htmlspecialchars($col['IS_NULLABLE']) . "<br>";
}

$stmt->close();
$mysqli->close();
?>

输出

表 'CUSTOMERS' 的列:
- ID (int), Nullable: NO
- NAME (varchar(100)), Nullable: NO
- AGE (int), Nullable: NO
- ADDRESS (varchar(255)), Nullable: YES
- SALARY (decimal(10,2)), Nullable: YES