MySQL - 添加/删除列

MySQL - 修改表结构：添加/删除列

随着应用程序的发展，其数据需求也在不断变化。对于开发人员来说，修改数据库表结构以适应新功能或删除过时功能是一项常见的任务。MySQL 中的 ALTER TABLE 语句是这些模式迁移的主要工具，它允许您在现有表中添加、删除或修改列。

向表中添加列

您可以使用 ALTER TABLE ... ADD COLUMN 语法向表中添加一个或多个列。您还可以指定新列在表中的位置。

语法

ALTER TABLE table_name
    ADD COLUMN new_column_definition [FIRST | AFTER existing_column],
    ADD COLUMN another_column_definition [FIRST | AFTER existing_column];

关键元素：

• column_definition：包括列名、数据类型和可选约束（如 NOT NULL 或 DEFAULT）。
• FIRST：将新列放在表的开头。
• AFTER existing_column：将新列放在指定的现有列之后。
• 如果未指定位置，新列将添加到表的末尾。

示例：逐步演进

我们从一个基本的 USERS 表开始。

CREATE TABLE USERS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    USERNAME VARCHAR(50) NOT NULL
);

-- Let's view the initial structure
DESCRIBE USERS;

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
USERNAME	varchar(50)	NO		NULL

现在，我们在末尾添加一个 EMAIL 列。

ALTER TABLE USERS ADD COLUMN EMAIL VARCHAR(100) NOT NULL;

接下来，我们添加一个带有默认值的 STATUS 列，并将其放置在 USERNAME 之后。

ALTER TABLE USERS ADD COLUMN STATUS VARCHAR(20) NOT NULL DEFAULT 'active' AFTER USERNAME;

验证

让我们检查表的最终结构。

DESCRIBE USERS;

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
USERNAME	varchar(50)	NO		NULL
STATUS	varchar(20)	NO		active
EMAIL	varchar(100)	NO		NULL

从表中删除列

要删除不再需要的列，请使用 ALTER TABLE ... DROP COLUMN 语句。此操作不可逆，将永久删除列及其所有数据，因此请谨慎操作。

语法

ALTER TABLE table_name
    DROP COLUMN column_to_delete,
    DROP COLUMN another_column_to_delete;

示例：清理表

假设我们决定不再需要 STATUS 列。我们可以删除它。

ALTER TABLE USERS DROP COLUMN STATUS;

您可以使用 DESCRIBE USERS; 验证更改。

生产环境的性能和最佳实践

在小型表上，ALTER TABLE 速度很快。在拥有数百万行的大型生产表上，它可能是一个危险、耗时且会锁定表的长时间运行操作，导致应用程序停机。现代 MySQL（使用 InnoDB 存储引擎）通过许多操作的“在线 DDL (Online DDL)”改进了这一点，但这并非万能药。

最佳实践

• 合并操作： 如果可能，在一个 ALTER TABLE 语句中执行多个添加、修改和删除操作。这比为每个更改运行单独的语句更高效。
• 安排停机时间： 对于无法使用在线 DDL 的关键表，请计划维护窗口进行模式更改。
• 使用在线模式更改工具： 对于大型表上的零停机迁移，行业标准是使用外部工具，如 Percona 的 pt-online-schema-change 或 GitHub 的 gh-ost。这些工具通过创建表的副本、更改副本，然后小心地将数据从原始表迁移到新表，最后进行交换，所有这些都不会长时间锁定原始表。
• 版本控制迁移： 所有模式更改都应保存为 SQL 脚本，并与应用程序代码一起提交到版本控制系统（如 Git）。这种做法，称为“数据库即代码 (database-as-code)”，确保了可追溯和可重复的迁移过程。

使用客户端程序添加/删除列

数据库迁移脚本通常以编程方式执行。以下是一个使用 PDO 管理模式更改的现代 PHP 示例，这在 Laravel 或 Symfony 等框架中是一项常见任务。

示例：PHP 与 PDO

PHP




<?php

// --- Configuration ---
$host = '127.0.0.1';
$db   = 'TUTORIALS';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

// --- Migration Logic ---
try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Database connection successful.\n";

    // --- Migration 1: Add a 'last_login' column ---
    echo "Attempting to add 'last_login' column...\n";
    $sql_add = "ALTER TABLE USERS ADD COLUMN last_login TIMESTAMP NULL DEFAULT NULL AFTER EMAIL";
    $pdo->exec($sql_add);
    echo "Column 'last_login' added successfully.\n";

} catch (PDOException $e) {
    // Check if the error is for a duplicate column
    if ($e->getCode() == '42S21' || strpos($e->getMessage(), 'Duplicate column name') !== false) {
        echo "Column 'last_login' already exists. Skipping.\n";
    } else {
        // Re-throw other errors
        throw $e;
    }
}

try {
    // --- Migration 2: Drop an obsolete 'legacy_id' column (example) ---
    // Let's pretend we added this column before and now want to remove it.
    // We first add it silently to make the example runnable.
    $pdo->exec("ALTER TABLE USERS ADD COLUMN legacy_id INT NULL");

    echo "Attempting to drop 'legacy_id' column...\n";
    $sql_drop = "ALTER TABLE USERS DROP COLUMN legacy_id";
    $pdo->exec($sql_drop);
    echo "Column 'legacy_id' dropped successfully.\n";

} catch (PDOException $e) {
    // Check if the error is for a non-existent column
    if ($e->getCode() == '42703' || strpos($e->getMessage(), 'check that column/key exists') !== false) {
        echo "Column 'legacy_id' does not exist. Skipping.\n";
    } else {
        throw $e;
    }
}

echo "Migration script finished.\n";

?>


Output
The expected output for a successful run:

Database connection successful.
Attempting to add 'last_login' column...
Column 'last_login' added successfully.
Attempting to drop 'legacy_id' column...
Column 'legacy_id' dropped successfully.
Migration script finished.