MySQL添加多个字段时，如何避免重复执行ALTER TABLE导致错误？

Question

**常见技术问题：** 在MySQL版本迭代或灰度发布中，运维脚本或数据库迁移工具（如Flyway/Liquibase）频繁执行`ALTER TABLE t ADD COLUMN a INT, ADD COLUMN b VARCHAR(32)`，若脚本被重复运行，会因字段已存在而报错 `ERROR 1060 (42S21): Duplicate column name 'a'`，导致部署失败。尤其在无幂等性校验的Shell/Python脚本中高发。该问题并非MySQL原生支持“IF NOT EXISTS”于ADD COLUMN（该语法仅适用于CREATE INDEX/TABLE等），故无法靠语法规避。开发者常误以为单条ALTER含多个ADD即天然安全，实则只要表结构已含任一待加字段，整条语句即失败。如何在不依赖外部元数据服务的前提下，安全、原子、可重入地批量添加字段？

Answer 1

```html

一、问题本质剖析：为什么单条 ALTER TABLE ADD COLUMN 不具备幂等性？

MySQL 8.0.19 之前完全不支持 ADD COLUMN IF NOT EXISTS；即便在 8.0.19+ 中，该语法也仅适用于 CREATE INDEX 或 CREATE TABLE，ALTER TABLE ... ADD COLUMN 仍严格校验字段唯一性——只要任一待加列已存在，整条语句即原子失败（回滚不可分），错误码 ERROR 1060 (42S21)。这导致灰度发布中脚本重复执行时，即使只新增一个字段失败，整个批量操作中断，违背“可重入”与“部署韧性”原则。

二、典型误用场景还原（Shell/Python 脚本高危模式）

• 反模式 Shell 示例：mysql -e "ALTER TABLE t ADD COLUMN a INT, ADD COLUMN b VARCHAR(32);" —— 无前置校验，第二次运行必报错
• Flyway 迁移脚本陷阱：V1__init.sql 含多列 ADD，若因网络中断重试，Flyway 默认不跳过已执行脚本，但 MySQL 层面拒绝执行
• Python + PyMySQL 直连风险：未查询 INFORMATION_SCHEMA.COLUMNS 即执行 DDL，缺乏事务级元数据快照一致性保障

三、核心约束与设计边界

约束维度	说明
✅ 不依赖外部元数据服务	禁用独立配置中心、ETCD 或自建 Schema Registry
✅ 原子性保障	单次执行要么全部成功，要么全部跳过（非部分生效）
✅ 可重入（Idempotent）	同一脚本 N 次执行，结果状态恒等
❌ 禁用存储过程封装（运维环境限制）	多数生产 DBA 禁用 DEFINER 权限，且跨版本兼容性差

四、分层解决方案演进（由浅入深）

1. 【基础层】INFORMATION_SCHEMA 驱动的条件判断（Shell）
   

   if ! mysql -Nse "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE TABLE_SCHEMA='db' AND TABLE_NAME='t' AND COLUMN_NAME='a'" | grep -q 1; then
       mysql -e "ALTER TABLE t ADD COLUMN a INT";
   fi
   if ! mysql -Nse "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE TABLE_SCHEMA='db' AND TABLE_NAME='t' AND COLUMN_NAME='b'" | grep -q 1; then
       mysql -e "ALTER TABLE t ADD COLUMN b VARCHAR(32)";
   fi

2. 【增强层】单事务内动态构建安全 DDL（Python + SQLAlchemy Core）
   利用 inspect(engine).get_columns('t') 获取当前列集，计算差集后拼接 ALTER TABLE ... ADD COLUMN 子句，全程在事务外预检、事务内单条执行。
3. 【生产级】基于 MySQL 8.0.19+ 的原子化多列安全添加（推荐）
   虽不支持 ADD COLUMN IF NOT EXISTS，但可组合 CREATE OR REPLACE VIEW + ALTER TABLE ... ALGORITHM=INSTANT 实现零锁表+幂等感知 —— 关键在于将“列存在性”转化为“视图定义是否匹配”，再通过视图重写屏蔽底层变更。

五、终极方案：元数据快照 + 动态 DDL 编译流程图

graph TD A[启动迁移脚本] --> B{查询 INFORMATION_SCHEMA.COLUMNS
获取 t 表当前所有列名} B --> C[构建期望列集合 S = {a, b}] C --> D[计算缺失列 Δ = S - 已有列集] D --> E{Δ 为空？} E -- 是 --> F[跳过，日志记录 “无需变更”] E -- 否 --> G[生成 ALTER TABLE t ADD COLUMN a INT, ADD COLUMN b VARCHAR(32)] G --> H[执行 DDL] H --> I[验证：SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='t' AND COLUMN_NAME IN ('a','b') = |Δ|] I --> J[成功/失败回调]

六、Flyway/Liquibase 适配增强实践

在 V2__safe_add_columns.sql 中嵌入如下模板（兼容 MySQL 5.7+）：

-- Flyway placeholder: ${schema}
SET @sql = CONCAT('ALTER TABLE ', '${schema}', '.t ');
SELECT GROUP_CONCAT(
  CONCAT('ADD COLUMN ', column_name, ' ', column_type) 
  SEPARATOR ', '
) INTO @add_clause
FROM (
  SELECT 'a' AS column_name, 'INT' AS column_type
  UNION ALL
  SELECT 'b', 'VARCHAR(32)'
) AS target_cols
WHERE NOT EXISTS (
  SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = '${schema}' AND TABLE_NAME = 't' AND COLUMN_NAME = target_cols.column_name
);
SET @sql = CONCAT(@sql, IFNULL(@add_clause, ''));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

该方案利用 MySQL 用户变量+动态 SQL，在单个迁移文件内完成“检测-构建-执行”闭环，无需外部脚本协调。

七、关键避坑指南（5年+从业者必读）

• ⚠️ 不要信任 SHOW COLUMNS 的实时性：在高并发 DDL 场景下，其结果可能滞后于实际元数据提交（尤其涉及复制延迟）
• ⚠️ ALGORITHM=INSTANT 并非万能：仅对 ADD/DROP COLUMN（非主键）、RENAME COLUMN 等有限操作生效，且要求 MySQL ≥ 8.0.12 + 行格式为 DYNAMIC/COMPRESSED
• ⚠️ 字符集/排序规则隐式冲突：VARCHAR(32) 若未显式声明 COLLATE，可能因库级默认值差异导致 ADD COLUMN 失败（ERROR 1071）
• ✅ 黄金法则：所有生产 DDL 迁移必须配套 SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE ... 快照留存，用于事后审计与回滚决策

```