MySQL修改表结构完整指南：增删列、索引与字符集操作

查看与准备

在修改表结构前，首先了解当前表定义至关重要。使用 SHOW CREATE TABLE 可以显示建表语句，包含所有字段、类型、引擎及默认字符集信息。

mysql> show create table person;

字段结构修改

第1步：删除列

当表中不再需要某个字段时，可以直接移除该列及其数据。这不可逆，请谨慎操作。

ALTER TABLE person DROP COLUMN birthday;

第2步：添加新列

若需扩展字段，使用 ADD 关键字指定新列名及数据类型。注意，新列默认添加到表的最后一列位置。

ALTER TABLE person ADD COLUMN birthday datetime;

第3步：修改列属性或名称

有两种方式可改变现有列的定义：MODIFY 用于仅改变列的类型或约束（不改变列名）；CHANGE 用于同时改变列名和类型。

例如，将 number 列改为 BIGINT 且不允许为空：

ALTER TABLE person MODIFY number BIGINT NOT NULL;

或者，将列名从 number 更改为 id，类型设为 BIGINT：

ALTER TABLE person CHANGE number id BIGINT;

主键管理

第4步：添加主键

主键用于唯一标识每一行记录。添加时需指定主键列。若列中已有重复数据，此操作会失败。

ALTER TABLE person ADD PRIMARY KEY (id);

第5步：删除主键

移除现有的主键约束。注意：如果存在自增列，删除主键通常也会自动删除自增属性。

ALTER TABLE person DROP PRIMARY KEY;

索引管理

合理建立索引可提升查询性能，但过多的索引会增加写入开销。以下介绍三种常见索引的操作。

第6步：添加唯一索引

确保特定列的数据在所有行中保持唯一，并加速查找。语法格式为 ADD UNIQUE [索引名] (列名)。

ALTER TABLE person ADD UNIQUE name_unique_index (`name`);

上述命令为 name 列创建了名为 name_unique_index 的唯一索引。

第7步：添加普通索引

普通索引是最基础的索引类型，允许重复值，主要用于加快检索速度。

ALTER TABLE person ADD INDEX birthday_index (`birthday`);

第8步：删除索引

当某些索引不再需要或造成性能负担时，应将其删除。删除的是索引而非数据列。

ALTER TABLE person DROP INDEX birthday_index;
ALTER TABLE person DROP INDEX name_unique_index;

特殊索引操作(MyISAM)

对于旧版存储引擎 MyISAM，在处理大量数据插入时，可以通过控制索引更新来提升速度。

第9步：启用与非唯一索引管理

禁用非唯一索引： 在执行批量导入前运行，停止实时更新非唯一索引，加速数据处理。

ALTER TABLE person DISABLE KEYS;

激活非唯一索引： 数据导入完成后运行，重新构建被暂停更新的非唯一索引。

ALTER TABLE person ENABLE KEYS;

注意：这些命令主要针对 MyISAM 表有效，InnoDB 表处理方式不同。

字符集与排序规则调整

修改字符集常用于解决乱码问题或统一数据库规范。分为三层次：全表转换、单列转换、仅改默认值。

第10步：整表转换字符集

将表中所有相关的字符列(CHAR, VARCHAR, TEXT)及表默认字符集都转换为目标编码。

ALTER TABLE person CONVERT TO CHARACTER SET utf8;

第11步：仅修改某列字符集

只针对特定列进行字符集和长度的重置。必须使用 CHANGE 语法重新声明该列定义。

ALTER TABLE person CHANGE name name varchar(255) CHARACTER SET utf8;

第12步：仅修改默认字符集

不改动现存列数据，仅改变表的默认字符集设置。后续新建的列若未指定编码，将沿用此设置。

ALTER TABLE person DEFAULT CHARACTER SET utf8;

表级别维护操作

第13步：修改表名

重命名当前数据库中的表，语法简洁且高效，原子操作。

RENAME TABLE person TO person_other;

第14步：移动表到其他数据库

利用 RENAME 语句的跨库特性，不仅改名，还能将表物理移动至另一个数据库中。前提是目标数据库必须存在。

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;