MySQL 表結構變更全集：增刪改列、調整順序、索引與主鍵管理

列管理核心操作

增加列

-- 指定位置（AFTER 列名 / FIRST）
ALTER TABLE `user_movement_log` 
ADD COLUMN `GatewayId` INT NOT NULL DEFAULT 0 AFTER `Regionid`;

-- 批量增加
ALTER TABLE `tbl` 
ADD COLUMN `col1` INT DEFAULT 0,
ADD COLUMN `col2` VARCHAR(50) DEFAULT '';

刪除列

ALTER TABLE `user_movement_log` DROP COLUMN `Gatewayid`;

修改列：名稱、類型、屬性、位置

-- 重命名 + 修改類型 + 調整位置
ALTER TABLE `user_movement_log` 
CHANGE `GatewayId` `GatewayId` INT NOT NULL DEFAULT 0 AFTER `RegionID`;

-- 僅修改類型（名稱不變用 MODIFY）
ALTER TABLE `t1` MODIFY `b` BIGINT NOT NULL;

-- 修改默認值
ALTER TABLE `infos` CHANGE `list` `list` TINYINT NOT NULL DEFAULT '0';

調整列順序

ALTER TABLE `tbl` CHANGE `col` `col` INT NOT NULL DEFAULT 0 AFTER `other_col`;

索引管理

創建索引

-- 普通索引
ALTER TABLE `tablename` ADD INDEX `idx_name` (`col1`, `col2`);

-- 唯一索引
ALTER TABLE `tablename` ADD UNIQUE `uk_card` (`cardnumber`);

-- 主鍵
ALTER TABLE `tablename` ADD PRIMARY KEY (`id`);

刪除索引

DROP INDEX `idx_name` ON `tablename`;
ALTER TABLE `tablename` DROP INDEX `idx_name`;
ALTER TABLE `tablename` DROP PRIMARY KEY;

表重命名

ALTER TABLE `t1` RENAME `t2`;

主鍵與自增列特殊操作

-- 增加自增主鍵列
ALTER TABLE `t2` 
ADD `new_field_id` INT(5) UNSIGNED DEFAULT 0 NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`new_field_id`);

-- 修改自增起始值
ALTER TABLE `tablename` AUTO_INCREMENT = 1000;

時間戳列添加

ALTER TABLE `t2` ADD `d` TIMESTAMP;

語法風格對比

最佳實踐清單

1. ✅ 生產環境變更前必備份（mysqldump 或備份表）
2. ✅ 大表 DDL 使用 pt-online-schema-change 或 MySQL 8.0+ 瞬時 DDL
3. ✅ 修改類型評估數據截斷風險（如 VARCHAR(100) → VARCHAR(50)）
4. ✅ 刪除列前確認無視圖/存儲過程/外鍵依賴
5. ✅ 索引創建考慮選擇性（Cardinality），低選擇性列慎建索引
6. ✅ 批量 DDL 合併單條 ALTER TABLE 減少鎖表次數

版本差異提示

• MySQL 5.6+：支持 ALGORITHM=INPLACE 部分操作無鎖
• MySQL 8.0+：瞬時 DDL（Instant ADD COLUMN、修改默認值等）無鎖、秒級完成
• MyISAM 表 DDL 通常全表鎖，InnoDB 相對友好