MariaDB 教程 / 用 ALTER TABLE 修改列与约束前如何检查数据
ALTER TABLE 用于修改现有表的列、类型与约束。先检查当前记录是否满足新规则,再把“加列、填充旧数据、施加约束”分成可核对的阶段,才能看清哪一步失败,以及是否可能丢失信息。
保存现有定义并准备恢复路径
结构变更不是只换一个显示标签。缩短字段、改成 NOT NULL 或增加 UNIQUE,都可能因现有记录不合要求而失败,或改变已保存的值。操作前保存 SHOW CREATE TABLE,准备适当的数据备份,核对依赖查询,并在分离环境先复现变化。
| 变更 | 先检查什么 | 主要风险 |
|---|---|---|
| 增加 NOT NULL | NULL 的数量与业务含义 | 需要明确缺失数据的处理方式 |
| 增加 UNIQUE | 重复键组合 | 冲突记录会让约束添加失败 |
| 缩短字符串 | 最大字符长度与边界数据 | 可能拒绝或截断 |
| 删除列 | 调用方与数据保留需求 | 列中已有值会被移除 |
NULL、DEFAULT 与 CHECK 教程说明了新规则的含义。本节重点是把规则应用到已有数据,而不是只在一张空表上声明它们。具体语法与适用条件见官方 ALTER TABLE 文档。
准备已有商品,再引入新编码
使用 course_user 连接学习服务器,先核对 SELECT VERSION(), DATABASE(); 和 SHOW TABLES;。本节独立使用 products_38_lab;若已有同名表,先查看结构与记录,保留需要的内容,不要覆盖未知用途的数据。
下面是完整准备代码,在 MariaDB 客户端执行。也可按照 SQL 文件与 SOURCE 教程保存为 UTF-8 文件,用实际路径加载,不能直接当作操作系统命令运行。
USE shopcourse;
CREATE TABLE products_38_lab (
product_id INT NOT NULL AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
note VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (product_id),
CONSTRAINT ck_p38_price CHECK (price >= 0),
CONSTRAINT ck_p38_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_38_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
(2, '볼펜', '문구', 1500.00, 0, ''),
(3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
(4, '스탠드', '생활', 25000.00, 5, NULL),
(5, '파일', '문구', 1500.00, 50, ''),
(6, '케이블', '전자', 8000.00, 0, '호환 확인');
原始韩文商品字符串保持不变,编号 1~6 分别表示笔记本、圆珠笔、马克杯、台灯、文件夹和线缆;分类表示文具、生活用品与电子产品。用编号核对原名称、价格、库存与备注,避免迁移时悄悄改变原字段。
这次给全部商品增加 sku 编码。先允许 NULL,才能在现有六行上填值后再要求必填。P 加三位编号仅是本样本的生成规则;实际业务还要规定长度、稳定性和唯一范围,不能把这种简短格式直接视为所有商品系统的规则。
添加可空列、填充数据并核对数量
SHOW CREATE TABLE products_38_lab;
ALTER TABLE products_38_lab ADD COLUMN sku VARCHAR(20) NULL;
UPDATE products_38_lab SET sku=CONCAT('P',LPAD(product_id,3,'0'))
WHERE sku IS NULL;
SELECT COUNT(*) AS rows_total,COUNT(sku) AS known_sku,
COUNT(DISTINCT sku) AS unique_sku FROM products_38_lab;
新列刚添加时,六条旧记录的 sku 都是 NULL。UPDATE 后生成 P001~P006,rows_total、known_sku、unique_sku 应全部为 6。三个计数分别核对总行数、已知编码数量与不同编码数量,而不是只看到 UPDATE 成功就继续。
如果数量不一致,停止施加最终约束,先列出缺失或重复的编号,判断生成规则与数据是否正确。重复编码需要有业务依据的修正,不应为了添加 UNIQUE 任意删除冲突记录。
DDL 与后面的 UPDATE 不组成一段可以统一回滚的普通事务。应记录加列、填值和验证各自是否已经完成;中断后先查看当前定义与实际行,再决定继续哪个阶段。事务边界教程解释了为什么结构清理不能混进回滚实验。
施加约束,并单独执行应被拒绝的修改
ALTER TABLE products_38_lab
MODIFY COLUMN sku VARCHAR(20) NOT NULL,
ADD CONSTRAINT uq_p38_sku UNIQUE(sku),
ADD CONSTRAINT ck_p38_name CHECK(CHAR_LENGTH(product_name)>0);
SHOW CREATE TABLE products_38_lab;
SELECT product_id,product_name,sku FROM products_38_lab ORDER BY product_id;
完成后 sku 为 VARCHAR(20)、NOT NULL,并且有 uq_p38_sku 唯一约束;名称检查要求字符数大于零。读取真实 SHOW CREATE TABLE,再逐行核对 P001~P006,确认声明和实际数据都符合目标。
使用 MODIFY 时,应明确重述需要保留的类型、长度、NULL 规则、默认值及其他属性。不要假定省略的属性永远自动保留;将变更前后定义逐项比较,尤其是已有默认值、字符属性或其他重要条件的列。
下面是故意失败的独立语句:把商品 2 改成商品 1 已占用的 P001。不要把它混在正常迁移文件中,并在失败后继续忽略错误:
UPDATE products_38_lab SET sku='P001' WHERE product_id=2;
预期得到 UNIQUE 错误,常见码为 1062;再查商品 2,应仍为 P002。也应分别核对 NULL 编码被 NOT NULL 拒绝,空字符串名称被 CHECK 拒绝,避免只检查约束名称出现而不检查行为。
这里的名称规则只检查 CHAR_LENGTH 大于零,因此一个普通空格也可以通过。它不是完整的“有意义名称”验证。如果业务不允许全空白,应另行定义准确的空白规则,并检查已有数据;不要把更严格的条件当成已经由当前 CHECK 完成。
评估锁、在线选项与部署成本
修改是否需要复制表、持有什么锁,取决于版本、引擎、操作与数据量。ALGORITHM 和 LOCK 选项不表示任何结构变更都能无中断完成。使用前核对支持组合、失败行为和应用等待影响,不要仅看到 ONLINE 字样就跳过部署评估。
长事务可能保留元数据锁,让 ALTER 等待,即使它没有更新目标行。需要查看相关连接的事务是否真正结束,而不是只看当前语句耗时。等待超时后也应确认结构是否已经改变,避免重复执行时对状态作出错误假设。
六行练习表的执行时间不能预测生产耗时。实际计划还应考虑数据和索引体积、额外磁盘空间、恢复时间、维护窗口,以及应用能否兼容分阶段的新旧字段。应把结果正确性与部署成本分别验证。
区分删除新列与恢复原数据
新列投入使用后再 DROP COLUMN,会丢掉其中累积的值,不一定等于“恢复到原来”。缩短类型等可能丢失信息的变化,也不能只靠反向 ALTER 自动恢复。需要保存变更前的数据与定义,并确认恢复流程可用。
本节应核对六件商品原有的名称、分类、价格、库存和备注均保持不变。新编码与约束是明确新增内容,不能用“行数还是六”代替全部字段检查。
若要重练,先查看当前结构与记录,保存应保留的数据,再只删除已确认可丢弃的本节表。下面会移除定义与全部记录,普通 ROLLBACK 无法恢复;将清理放在迁移与事务实验结束之后。
DROP TABLE shopcourse.products_38_lab;




