MySQL - 修改表

MySQL - ALTER TABLE 命令

什么是 ALTER TABLE 命令？

ALTER TABLE 命令是一个强大的数据定义语言（DDL - Data Definition Language）语句，用于修改现有表的结构。它允许您执行各种模式（schema）更改，包括添加、删除或修改列，以及更改表属性和管理约束。

区分 ALTER 和 UPDATE 至关重要。ALTER 更改表的结构（蓝图），而 UPDATE 更改表内部的数据（记录）。

基本语法

ALTER TABLE table_name
    [alter_specification [, alter_specification] ...];

alter_specification 可以是 ADD COLUMN、DROP COLUMN、RENAME TO 等操作。让我们从一个示例表开始。

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

添加新列

使用 ADD COLUMN 子句添加新列。默认情况下，新列会添加到表的末尾。

ALTER TABLE products ADD COLUMN price DECIMAL(10, 2) NOT NULL DEFAULT 0.00;

您可以使用 FIRST 或 AFTER 控制新列的位置。

-- 在 'id' 列之后添加一个 'sku' 列
ALTER TABLE products ADD COLUMN sku VARCHAR(50) UNIQUE AFTER id;

删除现有列

要删除列，请使用 DROP COLUMN 子句。请务必小心，因为此操作会永久删除列及其所有数据。

-- 让我们添加一个临时列，然后将其删除
ALTER TABLE products ADD COLUMN temp_notes TEXT;
ALTER TABLE products DROP COLUMN temp_notes;

修改列定义 (MODIFY 与 CHANGE)

MySQL 提供了两种修改列定义的方式：MODIFY 和 CHANGE。它们之间有一个微妙但重要的区别。

使用 MODIFY

MODIFY 用于更改列的数据类型、默认值或其他属性，但它不能重命名列。

-- 将 'sku' 列更改为非唯一且更长的类型
ALTER TABLE products MODIFY COLUMN sku VARCHAR(100) NULL;

使用 CHANGE

CHANGE 可以完成 MODIFY 的所有功能，并且它还可以重命名列。其语法要求您指定旧名称和新名称，即使它们相同。

-- 将 'name' 重命名为 'product_name' 并使其更长
ALTER TABLE products CHANGE COLUMN name product_name VARCHAR(500) NOT NULL;

如果您只想使用 CHANGE 更改类型，则必须重复列名：

ALTER TABLE products CHANGE COLUMN price price DECIMAL(12, 2) NOT NULL;

重命名表

要重命名整个表，请使用 RENAME TO 子句。

ALTER TABLE products RENAME TO inventory;

您可以通过尝试描述新表名来验证更改：

DESCRIBE inventory;

生产环境的最佳实践

在实时生产环境中的大型表上运行 ALTER TABLE 可能会有风险。它可能会长时间锁定表，导致应用程序停机。现代实践至关重要：

• 在线 DDL（Online DDL）： 较新的 MySQL 版本（5.6+）和 InnoDB 支持许多操作的在线 DDL，使用 ALGORITHM=INPLACE 和 LOCK=NONE 子句。这允许在不锁定表进行读写的情况下进行一些模式更改。请务必查阅您特定操作和 MySQL 版本的文档。
• 在线模式更改工具： 对于重大更改或在较旧的 MySQL 版本上，专门的工具，如 Percona 的 pt-online-schema-change 或 GitHub 的 gh-ost 是黄金标准。它们通过创建一个新的、已更改的表，以小块方式从旧表复制数据，并使用触发器在复制过程中捕获更改来工作。完成后，它们以原子方式交换表，实现零停机时间。
• 模式迁移工具： 在您的开发工作流中使用像 Flyway 或 Liquibase 这样的模式版本控制工具。这些工具以版本化的脚本文件跟踪数据库更改，从而可以以可重复和可靠的方式轻松地在不同环境（开发、测试、生产）中应用、回滚和管理模式演进。
• 备份： 在进行任何重大模式更改之前，务必确保您拥有最新的、可恢复的数据库备份。
• 测试： 在镜像生产设置的测试环境中测试您的模式更改，以识别任何性能问题或意外行为。

通过客户端程序修改表结构

从客户端应用程序执行 ALTER TABLE 语句与运行任何其他 DDL 查询没有区别。然而，由于执行时间可能很长，您可能需要调整连接超时设置。

PHP (PDO)
Node.js (mysql2/promise)
Java (JDBC)
Python (mysql-connector-python)




```php
<?php
// 假设 $pdo 是一个已连接的 PDO 对象

try {
    echo "正在向 inventory 表添加 'is_active' 列...\n";
    $sql = "ALTER TABLE inventory ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE";
    $pdo->exec($sql);
    echo "表修改成功。\n";
} catch (PDOException $e) {
    echo "修改表时出错：" . $e->getMessage() . "\n";
}
?>

main.js

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

async function addColumn() {
    let connection;
    try {
        connection = await mysql.createConnection({ /* connection config */ });
        console.log("正在向 inventory 表添加 'last_updated' 列...");
        const sql = "ALTER TABLE inventory ADD COLUMN last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP";
        await connection.query(sql);
        console.log('表修改成功。');
    } catch (error) {
        console.error('修改表时出错:', error);
    } finally {
        if (connection) await connection.end();
    }
}

addColumn();

SchemaChanger.java

import java.sql.*;

public class SchemaChanger {
    // 假设 DB_URL, USER, PASS 已定义
    public static void main(String[] args) {
        String sql = "ALTER TABLE inventory ADD COLUMN in_stock_quantity INT UNSIGNED NOT NULL DEFAULT 0";
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {
            System.out.println("正在向 inventory 表添加 'in_stock_quantity' 列...");
            stmt.executeUpdate(sql);
            System.out.println("表修改成功。");
        } catch (SQLException e) {
            System.err.println("修改表时出错：" + e.getMessage());
            e.printStackTrace();
        }
    }
}

alter_script.py

import mysql.connector

def add_column_to_inventory():
    try:
        conn = mysql.connector.connect(/* connection config */)
        cursor = conn.cursor()

        print("正在向 inventory 表添加 'notes' 列...")
        sql = "ALTER TABLE inventory ADD COLUMN notes TEXT NULL"
        cursor.execute(sql)
        conn.commit()
        print("表修改成功。")

    except mysql.connector.Error as err:
        print(f"修改表时出错: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    add_column_to_inventory()