MySQL / 系统课时

修改列类型与属性

修改列类型与属性

列定义变更看起来只是改一行 DDL,但真正风险来自旧数据能否被新定义接受,以及 InnoDB 是否需要扫描、重建数据。本文用一个可验证的流程处理长度、字符集、NULL、默认值与数据类型变化,并避免线上 ALTER 静默走向高代价执行路径。

学习目标

  • 在执行 DDL 前检查现有数据是否兼容目标列定义
  • 区分元数据调整与可能触发表重建的数据变更
  • 用显式算法、锁级别和结果查询验证结构变更

先把变更拆成兼容性问题

同一个 MODIFY COLUMN 可能同时改变类型、长度、字符集、排序规则、NULL 属性和默认值。不要只看 SQL 是否能解析,而要逐项判断已有值是否合法,以及存储和索引属性是否发生变化。

变更 执行前重点检查 主要风险
扩大 VARCHAR 长度 目标长度、字符集和实际值长度 某些定义变化可能触发更重的执行路径
缩短字符串或数值范围 最大长度、最小值、最大值 截断、溢出或 DDL 失败
修改字符集/排序规则 不可转换字符、索引键长度 数据重编码、索引重建、排序语义变化
NULL 改为 NOT NULL 是否仍存在 NULL 直接失败,或需要先回填
修改默认值 应用是否依赖旧默认值 新写入语义改变
INT、BIGINT、有符号属性互转 现有值域与目标范围 溢出、存储格式变化、执行成本上升

下面的演示可在测试库直接运行,建立一张最小示例表。

sql
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);

预期结果:

text
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 3 rows affected

先验证旧数据能否进入新定义

计划把昵称从 VARCHAR(50) 扩到 VARCHAR(60),并把 age 从可空改为非空。执行 ALTER 前先检查真实数据,而不是把兼容性判断交给 DDL。

sql
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;

预期结果:

text
+------------------+---------------+---------+---------+
| max_nickname_len | null_age_rows | min_age | max_age |
+------------------+---------------+---------+---------+
|                5 |             0 |      29 |      40 |
+------------------+---------------+---------+---------+

若 null_age_rows 不为 0,先按业务规则回填;若长度或值域超出目标范围,应先修数据或调整目标定义。不要依赖非严格 SQL 模式去“自动截断”,否则结构变更会演变成数据质量问题。

用显式算法阻止意外降级

对预期可在线执行的变更,建议显式声明算法和锁级别。下面扩大昵称并收紧年龄的 NULL 属性,示例已经通过前置检查。

sql
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;

预期结果:

text
Query OK, 0 rows affected
Records: <VALUE>  Duplicates: 0  Warnings: 0

<VALUE> 与版本、表规模和实际执行路径有关。如果当前版本或这组列变更不支持请求的 ALGORITHM/LOCK 组合,语句会直接报错;这比让服务器自动选择更高代价算法更安全,因为你可以停止上线并重新评估窗口、磁盘、redo、复制延迟和锁风险。

字符集转换、缩短类型、改变数值存储属性等操作更容易进入需要扫描或重建数据的路径。对大表,不要把“命令可以在线提交”等同于“没有资源冲击”。

单独处理默认值语义

默认值主要影响未来写入:它通常不改写已有行,却可能改变应用省略该列时的业务行为。把默认值调整单独执行,能减少一次 ALTER 混合多个风险点的复杂度。

sql
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';

预期结果:

text
+-------------+----------------+-------------+
| COLUMN_NAME | COLUMN_DEFAULT | IS_NULLABLE |
+-------------+----------------+-------------+
| status      | PENDING        | NO          |
+-------------+----------------+-------------+

如果应用显式传入 status,默认值不会生效;只有插入时省略该列才使用新默认值。因此结构验证之后,还要检查写入代码和 ORM 映射是否依赖旧默认值。

变更后同时验证结构和数据

DDL 成功并不代表迁移完成。至少确认三件事:列定义符合预期、旧数据没有被意外转换、关键读写 SQL 仍按预期工作。对字符集和排序规则变更,还要回归比较、排序、唯一索引和前缀索引行为。

修改列定义时,最安全的顺序是先证明数据兼容,再用显式算法约束执行路径,最后从 information_schema 和业务 SQL 两侧验证。只要任何一步无法证明安全,就应把它视为需要重建或分阶段迁移的高风险变更。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

修改列类型与属性

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通