MySQL ALTER TABLE 修改表结构与索引操作详解

基础语法解析

`ALTER TABLE` 支持对单张表执行多种规格的变更，包括添加/删除列、修改列定义、增删索引以及修改表名。基本语法结构如下：

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

其中 alter_spec 定义了具体的动作类型，如 ADD（添加）、MODIFY（修改）、DROP（删除）等。多个规格可以通过逗号分隔在一次语句中完成。

高频操作实战指南

第1步：向表中添加新字段

在扩展业务功能时，通常需要给现有表增加列。默认情况下，新字段会追加到表的最后。

• 操作目标：创建新列并指定数据类型。
• 关键参数：COLUMN（可选），字段名，数据类型。

ALTER TABLE employee ADD COLUMN Account_Number INT;

第2步：为查询优化添加索引

为了提升检索效率，可以在特定字段上建立普通索引、主键或唯一索引。

• 普通索引：允许重复和 NULL 值。
• 唯一索引：所有值必须唯一，但可为 NULL。
• 主键：唯一的非空标识符，一张表通常只有一个主键。

-- 添加普通索引
ALTER TABLE employee ADD INDEX (ID); 

-- 添加唯一索引
ALTER TABLE employee ADD UNIQUE (ID);

-- 添加主键
ALTER TABLE employee ADD PRIMARY KEY (ID); 

第3步：修改字段属性与重命名

当需求变更导致字段命名不当或数据类型不符合预期时，可使用 CHANGE 或 MODIFY。

• CHANGE old_col_name new_col_name ...：可同时修改名称和属性。
• MODIFY col_name ...：仅修改属性，不改变名称。

-- 将 ID 字段重命名为 salary 并改为 INT 类型
ALTER TABLE employee CHANGE ID salary INT;

-- 仅修改 First_Name 的长度限制
ALTER TABLE employee MODIFY First_Name varchar(100);  

第4步：删除字段或索引

清理废弃的数据结构。需要注意的是，DROP INDEX 仅移除索引文件，不会删除关联的数据字段；而 DROP 直接针对列则会将数据和索引一并移除。

-- 删除 Customer_ID 这个字段（含索引）
ALTER TABLE employee DROP Customer_ID 

-- 仅删除 Customer_ID 上的索引，字段保留
ALTER TABLE employee DROP INDEX Customer_ID 

-- 清除所有主键约束
ALTER TABLE employee DROP PRIMARY KEY 

第5步：批量变更与表重命名

利用组合语法可以提高执行效率，减少锁表时间。此外，该命令也可用于整表的更名。

-- 重命名表
ALTER TABLE employee RENAME Customer 

复杂的多维改动可以写在一起，例如同时添加主键、外键约束和新字段：

mysql> ALTER TABLE Books
  -> ADD PRIMARY KEY (BookID),
  -> ADD CONSTRAINT fk_1 FOREIGN KEY (PubID) REFERENCES Publishers (PubID),
  -> ADD COLUMN Format ENUM('paperback', 'hardcover') NOT NULL AFTER BookName;

注意事项

1. 备份先行：涉及表结构调整的操作在生产环境具有风险，建议在实施前做好全量备份。
2. 锁表影响：对于大表执行 ALTER TABLE 可能会导致长时间锁表，建议分时段操作或使用在线DDL工具。
3. 数据类型匹配：修改字段类型时，旧数据可能需要转换为新类型，若转换失败可能导致执行中断。

以上就是 MySQL alter table 修改表命令详细内容的介绍，更多关于 MySQL 高级操作的资料请关注本站其它相关文章！