萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

PHP 動態給 MySQL 表添加列:ALTER TABLE ADD COLUMN 完整實戰

核心 SQL

ALTER TABLE `table_name` 
ADD COLUMN `column_name` VARCHAR(20) NOT NULL DEFAULT '' 
COMMENT '列描述';

完整 PHP 實現(PDO 版)

pdo = new PDO($dsn, $user, $pass, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    }
    
    /**
     * 安全添加列:檢查存在性、類型兼容、默認值
     */
    public function addColumnIfNotExists(
        string $table,
        string $column,
        string $definition,  -- 如 "VARCHAR(50) NOT NULL DEFAULT ''"
        string $comment = ''
    ): bool {
        // 1. 檢查表存在
        $stmt = $this->pdo->prepare(
            "SELECT 1 FROM information_schema.tables 
             WHERE table_schema = DATABASE() AND table_name = ?"
        );
        $stmt->execute([$table]);
        if (!$stmt->fetchColumn()) {
            throw new Exception("Table `$table` not exists");
        }
        
        // 2. 檢查列是否已存在
        $stmt = $this->pdo->prepare(
            "SELECT 1 FROM information_schema.columns 
             WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?"
        );
        $stmt->execute([$table, $column]);
        if ($stmt->fetchColumn()) {
            error_log("Column `$table`.`$column` already exists, skip");
            return false;
        }
        
        // 3. 構建並執行 ALTER
        $sql = "ALTER TABLE `$table` ADD COLUMN `$column` $definition";
        if ($comment) {
            $sql .= " COMMENT " . $this->pdo->quote($comment);
        }
        
        try {
            $this->pdo->exec($sql);
            error_log("Column `$table`.`$column` added successfully");
            return true;
        } catch (PDOException $e) {
            // 1060: Duplicate column name (併發下可能)
            if ($e->getCode() == '42S21' || $e->getCode() == 1060) {
                error_log("Column `$table`.`$column` race condition, already exists");
                return false;
            }
            throw $e;
        }
    }
}

// ===== 使用示例 =====
$mgr = new SchemaManager(
    "mysql:host=127.0.0.1;dbname=test;charset=utf8mb4",
    "root", "password"
);

// 添加幾種常見類型
$mgr->addColumnIfNotExists('users', 'nickname', 'VARCHAR(50) NOT NULL DEFAULT \'\'', '用戶昵稱');
$mgr->addColumnIfNotExists('users', 'balance', 'DECIMAL(12,2) NOT NULL DEFAULT 0.00', '賬戶餘額');
$mgr->addColumnIfNotExists('users', 'last_login_at', 'DATETIME NULL DEFAULT NULL', '最後登錄時間');
$mgr->addColumnIfNotExists('users', 'tags', 'JSON', '標籤集合');
$mgr->addColumnIfNotExists('orders', 'extra_fee', 'INT UNSIGNED NOT NULL DEFAULT 0', '額外費用(分)');

MySQL 8.0+ 瞬時加列

MySQL 8.0.12+ 支持 Instant ADD COLUMN:僅修改元數據,無需重建表,毫秒級完成,不阻塞讀寫。

-- 自動使用 INSTANT 算法(滿足條件時)
ALTER TABLE `huge_table` ADD COLUMN `new_col` INT DEFAULT 0;

-- 強制指定算法
ALTER TABLE `huge_table` ADD COLUMN `new_col` INT DEFAULT 0, ALGORITHM=INSTANT;
-- 若不支持 INSTANT 會報錯,可改 ALGORITHM=INPLACE(僅鎖表短暫)

INSTANT 限制條件

  • 不能是主鍵、外鍵、分區鍵
  • 不能有默認值為 CURRENT_TIMESTAMP 的 TIMESTAMP
  • 不能是虛擬生成列
  • 表不能有 FULLTEXT 索引(某版本)

避坑指南

生產環境變更流程

  1. 提交 Schema 變更單(含回滾方案)
  2. 測試環境驗證腳本冪等性
  3. 低峰期執行,監控 SHOW PROCESSLIST 和 performance_schema
  4. 驗證新列可讀寫:INSERT ... SELECT ... 測試
  5. 更新應用代碼(向後兼容:新列有默認值)
  6. 清理舊代碼/遷移腳本

擴展:動態刪除/修改列

-- 刪除列(同樣需檢查存在性)
ALTER TABLE `t` DROP COLUMN `col`;

-- 修改列類型(大表慎用,會重建表)
ALTER TABLE `t` MODIFY COLUMN `col` BIGINT NOT NULL DEFAULT 0;

-- 重命名列
ALTER TABLE `t` CHANGE COLUMN `old_name` `new_name` VARCHAR(50) NOT NULL DEFAULT '';
copyright © 萬盛學電腦網 all rights reserved
坑點後果規避
未指定 DEFAULT NOT NULL大表報錯 "Column cannot be null"必須提供 DEFAULT 或允許 NULL
併發執行同一 ALTERDuplicate column name 錯誤應用層分布式鎖 或 捕獲 1060 錯誤忽略
大表加索引列鎖表數小時用 pt-online-schema-change 或 gh-ost
字符集不匹配隱式轉換導致索引失效顯式指定 CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci