【mysql】数据库表结构变更方案

背景

在做管理软件的时候，数据库的表结构不是一层不变的。升级升级时会附带数据库升级，通常数据库需要先完成升级才能使用。
在处理管理系统软件的表字段变更时，需平衡 数据一致性、业务连续性 和 开发效率。以下通过 5大核心步骤 和 典型场景案例，提供一套完整的工程化解决方案：

---

一、变更前的风险评估与方案设计

1. 影响分析清单

评估维度	检查项示例	工具/方法
业务影响	涉及哪些核心功能模块？	代码依赖扫描（SQL日志分析）
数据量级	变更表的数据量（百万/亿级）	SELECT COUNT(*) FROM table
关联系统	是否有外部系统依赖该字段？	接口文档审查
回滚难度	是否可逆？回滚所需时间	预写回滚SQL脚本

2. 变更方案选型

---

二、变更实施：分阶段操作指南

场景1：安全新增字段

-- 步骤1：检查表结构
DESC user;

-- 步骤2：非阻塞添加字段（MySQL 5.6+ Online DDL）
ALTER TABLE user 
ADD COLUMN wechat VARCHAR(64) COMMENT '微信账号' 
AFTER phone, 
ALGORITHM=INPLACE, LOCK=NONE;

-- 步骤3：验证默认值
SELECT COUNT(*) FROM user WHERE wechat IS NOT NULL; -- 应为0

场景2：修改字段类型（非兼容变更）

-- 目标：将手机号字段 phone VARCHAR(20) → CHAR(11)
-- 步骤1：新增临时字段
ALTER TABLE user ADD COLUMN phone_new CHAR(11);

-- 步骤2：数据迁移（分批次防止锁表）
UPDATE user SET phone_new = LEFT(TRIM(phone), 11) 
WHERE id BETWEEN 1 AND 10000; -- 分批执行

-- 步骤3：原子切换（事务保证一致性）
BEGIN;
ALTER TABLE user DROP COLUMN phone;
ALTER TABLE user CHANGE COLUMN phone_new phone CHAR(11);
COMMIT;

场景3：删除废弃字段

-- 步骤1：代码层移除字段引用（API/DAO层）
-- 步骤2：标记字段为废弃
COMMENT ON COLUMN user.old_address IS 'DEPRECATED-2024: 使用新地址字段';

-- 步骤3：观察期后物理删除
ALTER TABLE user DROP COLUMN old_address;

---

三、数据迁移的工程化实践

1. 零停机迁移策略（适用于亿级表）

---

四、变更后的验证与监控

1. 自动化验证脚本

# 验证字段是否生效
def test_new_column():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT wechat FROM user LIMIT 1")
    assert cursor.fetchone() is not None, "新增字段未生效"
    
# 验证数据一致性
def test_data_consistency():
    old_count = query("SELECT COUNT(*) FROM user WHERE phone LIKE '% %'")
    new_count = query("SELECT COUNT(*) FROM user WHERE LENGTH(phone)!=11")
    assert old_count == new_count, "数据迁移存在丢失"

2. 监控指标配置

• 数据库性能：

  -- 监控锁等待
  SHOW ENGINE INNODB STATUS LIKE '%lock%';
  
  -- 查询QPS变化
  SELECT VARIABLE_VALUE 
  FROM performance_schema.global_status 
  WHERE VARIABLE_NAME = 'Questions';
  

• 业务日志：
  捕获包含旧字段的SQL异常（如Unknown column 'old_address'）

---

五、版本控制与团队协作

1. 数据库迁移工具集成

使用 Flyway 或 Liquibase 管理变更脚本：

<!-- Flyway配置示例 -->
<migration>
    <version>2024.03.001</version>
    <description>Add wechat column to user table</description>
    <sql>
        ALTER TABLE user ADD COLUMN wechat VARCHAR(64);
    </sql>
    <rollback>
        ALTER TABLE user DROP COLUMN wechat;
    </rollback>
</migration>

2. Git分支策略

---

六、经典故障案例与避坑指南

案例：错误的时间类型修改导致数据截断

• 错误操作：

  ALTER TABLE orders MODIFY COLUMN pay_time DATETIME(3); -- 原为TIMESTAMP
  

• 后果：
  TIMESTAMP范围（1970-2038）外的数据被重置为0
• 正确做法：
  1. 创建新字段pay_time_new DATETIME(3)
  2. 数据迁移时转换时间格式
  3. 应用程序兼容新旧字段读写

---

总结：字段变更最佳实践

1. 变更即代码：所有DDL操作纳入版本控制
2. 灰度发布：先小规模验证再全量执行
3. 防御式设计：代码兼容新旧字段至少一个迭代周期
4. 监控全覆盖：从数据库指标到业务日志多层监控
5. 团队协同：通过文档和会议同步变更信息

-- 最终检查清单
SELECT 
    COLUMN_NAME AS 字段名,
    COLUMN_TYPE AS 类型,
    COLUMN_COMMENT AS 注释
FROM 
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_NAME = 'user';