修改表
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'不可）。
- 给已有列添加