修改表 | 小菜鸟

MySQL 表结构修改全解析：从编码到列定义

在数据库维护过程中，修改表结构是常见操作，包括调整编码、修改列名、增减字段等。MySQL 提供了丰富的 ALTER TABLE 语句支持这些操作，本文详细讲解各类修改场景的语法和注意事项。

修改编码格式（数据库 / 表 / 字段）

编码格式不匹配可能导致中文乱码或表情存储失败，需根据需求调整为 utf8 或 utf8mb4（支持表情）。

1. 修改数据库编码

-- 语法：ALTER DATABASE 数据库名 CHARACTER SET 编码 [COLLATE 校对规则];
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

影响：新表默认使用该编码，已有表不受影响。

2. 修改表编码

-- 语法：ALTER TABLE 表名 CHARACTER SET 编码 [COLLATE 校对规则];
ALTER TABLE users CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

影响：表的默认编码更新，新添加的字段会继承该编码，已有字段需单独修改。

3. 修改字段编码

-- 语法：ALTER TABLE 表名 MODIFY 字段名 类型 CHARACTER SET 编码 [COLLATE 校对规则];
ALTER TABLE users MODIFY nickname VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

注意：需指定字段完整类型（如 VARCHAR(50)），否则可能覆盖原有类型。

修改表名

-- 语法1：ALTER TABLE 旧表名 RENAME TO 新表名;
ALTER TABLE user RENAME TO users;

-- 语法2：RENAME TABLE 旧表名 TO 新表名;
RENAME TABLE users TO user_info;

注意：修改表名会影响依赖该表的视图、存储过程等，需同步更新。

修改列定义（列名、类型、约束）

1. 修改列名

-- 语法：ALTER TABLE 表名 CHANGE 旧列名 新列名 类型 [约束];
ALTER TABLE users CHANGE u_name username VARCHAR(30) NOT NULL;

必须指定新列的完整类型（即使类型不变），否则会报错。
可同时修改类型（如从 VARCHAR(20) 改为 VARCHAR(30)）。

2. 修改列类型或约束

-- 语法：ALTER TABLE 表名 MODIFY 列名 新类型 [新约束];
-- 示例1：修改类型（int 改为 bigint）
ALTER TABLE users MODIFY age BIGINT UNSIGNED;

-- 示例2：添加非空约束
ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL;

-- 示例3：去除默认值
ALTER TABLE users MODIFY status TINYINT DEFAULT NULL;

MODIFY 仅修改列的类型和约束，不改变列名（与 CHANGE 的区别）。
若列中已有数据，新类型需兼容旧数据（如 VARCHAR 转 INT 可能失败）。

添加 / 删除列

1. 添加新列

-- 语法：ALTER TABLE 表名 ADD 列名 类型 [约束] [FIRST|AFTER 已有列名];
-- 示例1：添加普通列
ALTER TABLE users ADD phone VARCHAR(20) DEFAULT NULL;

-- 示例2：添加到指定位置（在 username 之后）
ALTER TABLE users ADD gender TINYINT AFTER username;

-- 示例3：添加为主键（需确保列值唯一且非空）
ALTER TABLE logs ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;

FIRST 表示添加到第一列，AFTER 列名 表示添加到指定列之后（默认添加到最后）。

2. 删除列

-- 语法：ALTER TABLE 表名 DROP 列名;
ALTER TABLE users DROP phone;

注意：删除列会永久丢失该列数据，操作前需备份。
若列是主键或外键，需先解除约束才能删除。

外键操作（添加 / 删除）

外键用于保证表之间的参照完整性，但实际项目中常通过业务逻辑维护（避免数据库耦合）。

1. 添加外键

-- 语法：ALTER TABLE 子表 ADD CONSTRAINT 外键名 FOREIGN KEY (子表列) REFERENCES 父表(父表列);
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_user 
FOREIGN KEY (user_id) 
REFERENCES users(id);

子表列与父表列的类型必须一致。
父表列需是主键或唯一索引。

2. 删除外键

-- 语法：ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;

外键名可通过 SHOW CREATE TABLE 表名 查看。
删除外键不影响关联的列，仅移除约束关系。

注意事项

数据安全：
- 修改表结构前需备份数据（尤其是 DROP 操作）。
- 生产环境建议在低峰期执行，大表修改可能锁表导致业务阻塞。
性能影响：
- 添加 / 删除列或修改类型可能导致表重建（尤其是 InnoDB 引擎），耗时与数据量成正比。
- 可通过 ALTER TABLE ... ALGORITHM=INPLACE 尝试在线修改（部分操作支持，需 MySQL 5.6+）。
约束兼容性：
- 给已有列添加 NOT NULL 约束时，需确保该列无 NULL 值，否则会失败。
- 修改类型时，需保证现有数据可转换为新类型（如字符串 '123' 可转为 INT，但 'abc' 不可）。