技术 · 思考 · 实践 · 成长

首页/MySQL8专栏/正文

MySQL 8.0 ALTER TABLE 详解:字段、索引与约束修改

MySQL8专栏630 次阅读

ALTER TABLE 用于修改已有表的结构,包括重命名表、添加或删除字段、修改字段类型、管理索引与约束等。结构变更可能锁表、复制大量数据或影响现有程序,生产环境执行前必须备份并评估影响。

一、准备示例表

本文使用商品表演示常见操作:

CREATE TABLE products (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    stock INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

执行修改前,先确认当前表结构:

SHOW CREATE TABLE products\G
DESCRIBE products;

二、修改表名

ALTER TABLE products
RENAME TO shop_products;

也可以使用 RENAME TABLE:

RENAME TABLE shop_products TO products;

修改表名后,应同步检查应用 SQL、视图、存储程序和运维脚本中的表名引用。

MySQL 修改表名示例

三、添加字段

1. 在表末尾添加字段

ALTER TABLE products
ADD COLUMN status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态:1启用,0停用';

2. 指定字段位置

-- 添加到第一列
ALTER TABLE products
ADD COLUMN product_code VARCHAR(32) NOT NULL FIRST;

-- 添加到 name 字段后
ALTER TABLE products
ADD COLUMN category_id BIGINT UNSIGNED NULL AFTER name;

FIRST 和 AFTER 主要影响查看顺序,业务查询仍应明确写出字段名。调整物理顺序可能增加 DDL 成本,没有必要时可省略。

MySQL 添加字段示例

四、修改字段数据类型与属性

MODIFY COLUMN 修改字段定义,但不修改字段名:

ALTER TABLE products
MODIFY COLUMN name VARCHAR(200) NOT NULL COMMENT '商品名称';

使用 MODIFY 或 CHANGE 时,应重新写出需要保留的数据类型和全部属性。遗漏 NOT NULL、DEFAULT、COMMENT 等属性可能改变原字段定义。

缩短字符串长度、降低数值范围或把可空字段改为 NOT NULL 前,应先检查现有数据是否兼容:

SELECT MAX(CHAR_LENGTH(name)) AS max_name_length
FROM products;

SELECT COUNT(*) AS null_count
FROM products
WHERE name IS NULL;

五、修改字段名

1. 仅修改名称

MySQL 8.0 推荐使用 RENAME COLUMN,不需要重复字段定义:

ALTER TABLE products
RENAME COLUMN stock TO inventory;

2. 同时修改名称和定义

CHANGE COLUMN 可以同时修改字段名与完整定义:

ALTER TABLE products
CHANGE COLUMN inventory stock_count INT UNSIGNED NOT NULL DEFAULT 0;

重命名后还要检查视图、触发器、存储程序以及应用代码;这些对象中的引用不一定会被自动更新。

六、设置与删除默认值

-- 设置默认值
ALTER TABLE products
ALTER COLUMN price SET DEFAULT 0.00;

-- 删除默认值
ALTER TABLE products
ALTER COLUMN price DROP DEFAULT;

七、删除字段

ALTER TABLE products
DROP COLUMN category_id;

删除字段会永久丢失该列数据,不能通过事务 ROLLBACK 恢复。执行前应确认备份有效,并检查索引、约束和程序依赖。

MySQL 删除字段示例

八、调整字段位置

MODIFY COLUMN 配合 FIRST 或 AFTER 可以调整顺序:

ALTER TABLE products
MODIFY COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP FIRST;

ALTER TABLE products
MODIFY COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER stock_count;

必须完整写出字段定义,避免无意改变默认值或是否允许 NULL。

九、添加与删除索引

1. 普通索引

ALTER TABLE products
ADD INDEX idx_products_name (name);

ALTER TABLE products
DROP INDEX idx_products_name;

2. 唯一索引

ALTER TABLE products
ADD UNIQUE INDEX uk_products_code (product_code);

添加唯一索引前,应先检查重复值:

SELECT product_code, COUNT(*) AS duplicate_count
FROM products
GROUP BY product_code
HAVING COUNT(*) > 1;

3. 复合索引

ALTER TABLE products
ADD INDEX idx_products_status_price (status, price);

复合索引字段顺序应根据实际 WHERE、JOIN 和 ORDER BY 条件设计,而不是简单把常用字段全部加入索引。

十、添加与删除约束

1. CHECK 约束

ALTER TABLE products
ADD CONSTRAINT chk_products_price CHECK (price >= 0),
ADD CONSTRAINT chk_products_stock CHECK (stock_count >= 0);

ALTER TABLE products
DROP CHECK chk_products_stock;

2. 外键约束

ALTER TABLE products
ADD CONSTRAINT fk_products_category
FOREIGN KEY (category_id)
REFERENCES categories (id)
ON UPDATE CASCADE
ON DELETE SET NULL;

ALTER TABLE products
DROP FOREIGN KEY fk_products_category;

添加外键前,要确认两端字段类型一致、被引用字段有索引,并清理不满足约束的历史数据。

十一、一次执行多项修改

MySQL 允许在一条 ALTER TABLE 中使用多个子句:

ALTER TABLE products
ADD COLUMN updated_at DATETIME NULL,
ADD INDEX idx_products_created_at (created_at),
MODIFY COLUMN price DECIMAL(12, 2) NOT NULL DEFAULT 0.00;

合并操作可能减少重复重建表的成本,但生产环境仍需逐项确认兼容性;复杂变更应优先保证可审查和可回退。

十二、Online DDL:ALGORITHM 与 LOCK

InnoDB 的部分结构变更支持在线 DDL。可明确指定算法和锁级别,让不符合预期的操作直接失败,而不是悄悄使用代价更高的方式:

ALTER TABLE products
ADD COLUMN remark VARCHAR(255) NULL,
ALGORITHM = INSTANT;
ALTER TABLE products
ADD INDEX idx_products_price (price),
ALGORITHM = INPLACE,
LOCK = NONE;
  • INSTANT:只修改元数据,通常最快,但仅部分操作和版本支持。
  • INPLACE:尽量不复制整张表,但仍可能消耗较多资源。
  • COPY:复制并重建表,耗时和临时空间需求通常最大。
  • LOCK=NONE:请求变更期间允许并发读写;并非所有操作都支持。

即使是 Online DDL,在开始或结束阶段也可能短暂获取排他元数据锁。长事务可能阻塞 ALTER TABLE,因此大表操作必须选择维护窗口并持续监控。

十三、生产环境执行清单

  1. 确认 MySQL 小版本和表的存储引擎。
  2. 使用 SHOW CREATE TABLE 保存完整表结构。
  3. 检查数据是否符合新类型、唯一索引和约束。
  4. 确认可恢复的数据库备份,而不仅是导出建表语句。
  5. 评估表大小、临时磁盘空间、执行时间和锁表影响。
  6. 先在测试环境用相近数据量验证。
  7. 选择业务低峰执行,并监控元数据锁和复制延迟。
  8. 执行后验证表结构、行数、关键查询及应用功能。

查看最终结构:

SHOW CREATE TABLE products\G
SHOW INDEX FROM products;

完整语法与各操作支持的算法,可参考 MySQL 8.0 ALTER TABLE 官方文档和 InnoDB Online DDL 官方文档。

只有惜缘,才有缘分;只有惜友,才有友谊。

评论交流

0 条评论 · 支持多级回复
验证码
暂无评论,来说说你的想法吧

相关文章

继续阅读