MySQL 中使用 ALTER TABLE 语句实现字段的增删改与索引管理
目录
一、引言
二、正文
场景 1：新增字段（单字段 / 多字段）
实现 SQL 语句
关键说明
场景 2：修改字段（属性 / 名称）
2.1 仅修改字段属性（类型 / 长度 / 约束）
2.2 同时修改字段名 + 属性
关键说明
场景 3：删除字段（单字段 / 多字段）
实现 SQL 语句
关键说明
场景 4：索引管理（添加 / 删除）
4.1 添加索引
4.2 删除索引
关键说明
注意事项
三、结论
一、引言
在 MySQL 数据库的开发与维护过程中，业务需求的迭代必然会带来表结构的调整，比如新增业务字段、删除废弃字段、修改字段属性，或是为提升查询效率添加 / 删除索引。ALTER TABLE作为修改表结构的核心语句，能一站式实现这些操作。本文将聚焦实际开发中字段增删改和索引管理两大高频场景，结合实操案例讲解ALTER TABLE的具体用法，助力快速掌握表结构调整技巧。
二、正文
本文以用户表user_info为测试表，演示ALTER TABLE在字段增删改和索引管理中的具体使用，所有语句适配 MySQL 5.7/8.0 版本，执行前需确保拥有表的ALTER操作权限。
场景 1：新增字段（单字段 / 多字段）
业务中新增用户手机号、性别字段时，可通过ADD关键字实现，支持指定字段位置、添加约束条件。
实现 SQL 语句
关键说明
1.ADD COLUMN可简写为ADD；
2.AFTER 字段名指定新增字段位置，省略则默认添加在表最后；
3.可通过COMMENT添加字段注释，DEFAULT设置默认值，UNIQUE/NOT NULL添加约束。
场景 2：修改字段（属性 / 名称）
实际开发中常需调整字段类型、长度，或修改字段名，分别通过MODIFY和CHANGE实现，二者功能有明确区分。
2.1 仅修改字段属性（类型 / 长度 / 约束）
实现 SQL 语句
2.2 同时修改字段名 + 属性
实现 SQL 语句
关键说明
MODIFY仅能修改字段属性，不能改字段名；CHANGE可同时修改字段名和属性，需依次写旧名 + 新名 + 新属性；- 修改字段类型时需谨慎，如字符串转数字，非合规数据会导致执行失败或数据丢失。
场景 3：删除字段（单字段 / 多字段）
当业务字段废弃时，可通过DROP关键字删除，支持单字段和多字段批量删除。
实现 SQL 语句
关键说明
DROP COLUMN可简写为DROP；- 删除字段为高危操作，执行后字段数据会永久丢失，生产环境需先备份数据、确认字段无业务依赖。
场景 4：索引管理（添加 / 删除）
为高频查询字段添加索引是提升 MySQL 查询效率的关键，ALTER TABLE可实现普通索引、唯一索引的添加与删除，补充主键索引的常用操作。
4.1 添加索引
实现 SQL 语句
4.2 删除索引
实现 SQL 语句
关键说明
- 索引名建议遵循idx_普通索引字段、uk_唯一索引字段的命名规范，便于维护；
- 联合索引需遵循最左匹配原则，创建时按查询频率排序字段；
- 索引并非越多越好，频繁插入更新的表，过多索引会降低写入效率。
注意事项
- 生产环境操作时机：千万级以上大表执行
ALTER TABLE会触发锁表，阻塞业务的读 / 写操作，建议在业务低峰期执行，或使用 Online DDL 工具（如 pt-online-schema-change）； - 执行前备份：所有表结构修改操作前，务必备份整表数据，防止操作失误导致数据丢失；
- 约束兼容检查：添加非空 / 唯一约束时，需先检查表中现有数据是否符合约束条件，否则执行会失败；
- 语句原子性：多字段操作写在一条
ALTER TABLE语句中，为原子操作，要么全部执行成功，要么全部失败，避免分条执行导致表结构不一致。
三、结论
本文围绕 MySQL 开发中的高频场景，讲解了ALTER TABLE实现字段新增、修改、删除和索引添加、删除的具体用法，总结如下：
- 新增字段用
ADD，支持单 / 多字段，可指定位置和约束； - 修改字段属性用
MODIFY，同时改字段名 + 属性用CHANGE，二者不可混用； - 删除字段用
DROP，属于高危操作，务必先备份再执行； - 索引管理通过
ADD INDEX/UNIQUE INDEX和DROP INDEX实现，需遵循命名规范和最左匹配原则； - 生产环境执行表结构修改，需关注锁表问题、数据备份和约束兼容检查。
ALTER TABLE是 MySQL 表结构维护的必备技能，掌握其高频用法能高效应对业务需求迭代，同时养成先测试、再备份、最后执行的操作习惯，才能保证数据库的数据完整性和业务稳定性。