MySQL / 系统课时
修改列类型与属性
修改列类型与属性
列定义变更看起来只是改一行 DDL,但真正风险来自旧数据能否被新定义接受,以及 InnoDB 是否需要扫描、重建数据。本文用一个可验证的流程处理长度、字符集、NULL、默认值与数据类型变化,并避免线上 ALTER 静默走向高代价执行路径。
学习目标
- 在执行 DDL 前检查现有数据是否兼容目标列定义
- 区分元数据调整与可能触发表重建的数据变更
- 用显式算法、锁级别和结果查询验证结构变更
先把变更拆成兼容性问题
同一个 MODIFY COLUMN 可能同时改变类型、长度、字符集、排序规则、NULL 属性和默认值。不要只看 SQL 是否能解析,而要逐项判断已有值是否合法,以及存储和索引属性是否发生变化。
| 变更 | 执行前重点检查 | 主要风险 |
|---|---|---|
扩大 VARCHAR 长度 |
目标长度、字符集和实际值长度 | 某些定义变化可能触发更重的执行路径 |
| 缩短字符串或数值范围 | 最大长度、最小值、最大值 | 截断、溢出或 DDL 失败 |
| 修改字符集/排序规则 | 不可转换字符、索引键长度 | 数据重编码、索引重建、排序语义变化 |
NULL 改为 NOT NULL |
是否仍存在 NULL | 直接失败,或需要先回填 |
| 修改默认值 | 应用是否依赖旧默认值 | 新写入语义改变 |
INT、BIGINT、有符号属性互转 |
现有值域与目标范围 | 溢出、存储格式变化、执行成本上升 |
下面的演示可在测试库直接运行,建立一张最小示例表。
DROP TABLE IF EXISTS customer_profile;
CREATE TABLE customer_profile (
id BIGINT UNSIGNED NOT NULL,
nickname VARCHAR(50)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
NOT NULL,
age TINYINT UNSIGNED NULL,
status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO customer_profile (id, nickname, age)
VALUES
(1, 'Alice', 29),
(2, 'Bob', 31),
(3, 'Carol', 40);预期结果:
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 3 rows affected先验证旧数据能否进入新定义
计划把昵称从 VARCHAR(50) 扩到 VARCHAR(60),并把 age 从可空改为非空。执行 ALTER 前先检查真实数据,而不是把兼容性判断交给 DDL。
SELECT
MAX(CHAR_LENGTH(nickname)) AS max_nickname_len,
SUM(age IS NULL) AS null_age_rows,
MIN(age) AS min_age,
MAX(age) AS max_age
FROM customer_profile;预期结果:
+------------------+---------------+---------+---------+
| max_nickname_len | null_age_rows | min_age | max_age |
+------------------+---------------+---------+---------+
| 5 | 0 | 29 | 40 |
+------------------+---------------+---------+---------+若 null_age_rows 不为 0,先按业务规则回填;若长度或值域超出目标范围,应先修数据或调整目标定义。不要依赖非严格 SQL 模式去“自动截断”,否则结构变更会演变成数据质量问题。
用显式算法阻止意外降级
对预期可在线执行的变更,建议显式声明算法和锁级别。下面扩大昵称并收紧年龄的 NULL 属性,示例已经通过前置检查。
ALTER TABLE customer_profile
MODIFY COLUMN nickname VARCHAR(60)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
NOT NULL,
MODIFY COLUMN age TINYINT UNSIGNED NOT NULL,
ALGORITHM=INPLACE,
LOCK=NONE;预期结果:
Query OK, 0 rows affected
Records: <VALUE> Duplicates: 0 Warnings: 0<VALUE> 与版本、表规模和实际执行路径有关。如果当前版本或这组列变更不支持请求的 ALGORITHM/LOCK 组合,语句会直接报错;这比让服务器自动选择更高代价算法更安全,因为你可以停止上线并重新评估窗口、磁盘、redo、复制延迟和锁风险。
字符集转换、缩短类型、改变数值存储属性等操作更容易进入需要扫描或重建数据的路径。对大表,不要把“命令可以在线提交”等同于“没有资源冲击”。
单独处理默认值语义
默认值主要影响未来写入:它通常不改写已有行,却可能改变应用省略该列时的业务行为。把默认值调整单独执行,能减少一次 ALTER 混合多个风险点的复杂度。
ALTER TABLE customer_profile
ALTER COLUMN status SET DEFAULT 'PENDING';
SELECT
COLUMN_NAME,
COLUMN_DEFAULT,
IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'customer_profile'
AND COLUMN_NAME = 'status';预期结果:
+-------------+----------------+-------------+
| COLUMN_NAME | COLUMN_DEFAULT | IS_NULLABLE |
+-------------+----------------+-------------+
| status | PENDING | NO |
+-------------+----------------+-------------+如果应用显式传入 status,默认值不会生效;只有插入时省略该列才使用新默认值。因此结构验证之后,还要检查写入代码和 ORM 映射是否依赖旧默认值。
变更后同时验证结构和数据
DDL 成功并不代表迁移完成。至少确认三件事:列定义符合预期、旧数据没有被意外转换、关键读写 SQL 仍按预期工作。对字符集和排序规则变更,还要回归比较、排序、唯一索引和前缀索引行为。
修改列定义时,最安全的顺序是先证明数据兼容,再用显式算法约束执行路径,最后从 information_schema 和业务 SQL 两侧验证。只要任何一步无法证明安全,就应把它视为需要重建或分阶段迁移的高风险变更。