怎样在MySQL中安全地为大表添加DEFAULT默认值约束-mysql教程-PHP中文网

MySQL 5.7/8.0早期MODIFY COLUMN加DEFAULT会锁表重写全表，8.0.13+推荐用ALTER COLUMN SET DEFAULT（元数据级、不锁表），但需校验历史数据兼容性、NULL约束及SQL模式，并分三步安全实施。

怎样在mysql中安全地为大表添加default默认值约束

ALTER TABLE MODIFY COLUMN 会锁表并重写全表数据

在 MySQL 5.7 或 8.0 早期版本中，用 MODIFY COLUMN 添加 DEFAULT 值（例如 ALTER TABLE orders MODIFY COLUMN status VARCHAR(20) DEFAULT 'pending'）本质是重定义整列：MySQL 会重建表、复制所有行、重建索引。对千万级大表，这可能耗时数分钟到数小时，且全程持有 MDL_WRITE 锁，阻塞所有 DML（INSERT/UPDATE/DELETE）和 DDL。

常见误判：以为只是“加个元数据”，实际是 DDL 高危操作。

执行前务必确认表大小：SELECT table_name, round(((data_length + index_length) / 1024 / 1024), 2) AS size_mb FROM information_schema.tables WHERE table_schema = 'your_db' AND table_name = 'orders';
生产环境必须在低峰期操作，并提前通知上下游服务可能的写入中断
若使用 innodb_file_per_table=OFF，重写还会导致系统表空间膨胀，无法在线收缩

MySQL 8.0.13+ 推荐用 ALTER COLUMN SET DEFAULT（但仍有隐性风险）

ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending' 在 8.0.13+ 是元数据级变更，不锁表、不重写数据，理论上秒级完成。但它只改 INFORMATION_SCHEMA.COLUMNS.DEFAULT，不验证现有数据是否兼容新默认值 —— 这是最大盲区。

例如：字段原为 status VARCHAR(10) NOT NULL，已有 10 万行值为 ''（空字符串），你加 DEFAULT 'active' 后，后续 INSERT 省略该字段会生效，但旧数据仍为空字符串，业务逻辑可能误判。

它不解决历史脏数据问题，也不校验类型兼容性（比如对 TINYINT 列设 DEFAULT 'abc' 会静默失败或触发隐式转换）
执行后需立刻检查：SHOW COLUMNS FROM orders LIKE 'status'; 确认 Default 列已更新
若字段含大量 NULL 或空值，且业务要求“统一补默认值”，必须额外跑 UPDATE，且要加 WHERE 条件避免全表扫描

大表加 DEFAULT 前必须检查 SQL mode 和字段可空性

如果表启用了 STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES，而目标字段是 NOT NULL 且当前无 DEFAULT，则直接 ALTER COLUMN SET DEFAULT 可能成功，但后续 INSERT 省略该字段时仍会报错：Field 'status' doesn't have a default value —— 因为严格模式下，NOT NULL 字段必须显式有 DEFAULT 才允许省略。

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

更隐蔽的问题是字段当前定义是否允许 NULL：

查现状：SHOW COLUMNS FROM orders LIKE 'status'; 关注 Null 列（YES/NO）和 Default 列
若 Null = NO 且 Default 为空，添加 DEFAULT 前必须确保：要么字段允许 NULL（MODIFY COLUMN ... NULL DEFAULT ...），要么用 MODIFY COLUMN 显式带 NOT NULL DEFAULT ...（此时 MySQL 会尝试用默认值填充现有 NULL 行，但大表慎用）
TEXT/BLOB 类型在 8.0.19 前不支持 DEFAULT，强行执行会被忽略或报错，需先确认版本：SELECT VERSION();

真正安全的做法：分三步走，不依赖单条 DDL

对线上大表，不要幻想“一条命令搞定”。安全路径是明确拆解意图：改元数据 + 补历史数据 + 验证行为，每步可控。

第一步（元数据）：用 ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending'（8.0.13+）或 MODIFY COLUMN（5.7）—— 仅当表足够小或可停写时才选后者
第二步（历史数据）：分批 UPDATE，例如 UPDATE orders SET status = 'pending' WHERE status IS NULL LIMIT 10000;，循环执行直到影响行为 0；避免单次大事务撑爆 undo log
第三步（验证）：插入测试行（INSERT INTO orders (id, name) VALUES (1000001, 'test');），查结果确认 status 是否为 'pending'；再试 INSERT INTO orders VALUES (1000002, 'test2', DEFAULT, ...); 确认显式 DEFAULT 也生效

最易被忽略的是：DEFAULT 只作用于 INSERT，UPDATE 不触发；哪怕字段设了 DEFAULT CURRENT_TIMESTAMP，UPDATE 也不会自动刷新时间 —— 这不是 bug，是设计如此。