修改表结构

建完表不等于永远不用改：列名写错、业务要加字段、表名要规范化，都要改结构。今天集中练 ALTER，并认识移动表与复制表。

系列：MySQL 入门到查询 · 第 5 / 17 篇（第 5 天 · 4 月 25 日）
上一篇：创建库和表
下一篇：数据的增删改
总目录：系列索引

环境说明

• 已会：登录、use 库、create table、desc
• 练习库：继续用 train（没有则先建：create database train charset utf8mb4;）
• 全程在 mysql> 下执行，语句以 ; 结束

1. 为什么要改结构

场景	例子
设计时手误	列名 gander 本意是 gender
业务变化	要多存一个「库存」列
类型不合适	score double 要改成 int
表名不规范	personnel 改成 p1 或更清晰的名字
编码/引擎	与对接系统对齐 utf8mb4 / InnoDB

提醒：改结构可能影响已有数据（尤其改类型、删列）。练习库随便改；生产要谨慎、先备份。

2. 准备一张练习表

若昨天建过其他表也不影响。下面从零建 personnel，保证步骤可跟：

use train;

create table personnel(
    id int
);

desc personnel;

后面所有 ALTER 都围绕「把这张极简表逐步改丰满」来做。

3. ALTER：改表名

alter table personnel rename p1;

验证：

show tables;
desc p1;

要点	说明
rename	只改表名，列与数据一般仍在
show tables	应看不到 personnel，只有 p1

4. ALTER：增加列

4.1 加在最后（最常用）

alter table p1 add salary int;
desc p1;

4.2 加在第一列

alter table p1 add phone_number int first;
desc p1;

4.3 加在指定列之后

alter table p1 add sex char(1) after salary;
desc p1;

关键字	含义
add 列名 类型	追加到表的最后
first	插到最前面
after 目标列	插到某列后面

当前结构示意（执行后）：

phone_number | id | salary | sex   （具体以后以 desc 为准）

再补一列姓名，方便后面改类型：

alter table p1 add name varchar(30);
desc p1;

5. ALTER：修改列的类型

alter table p1 modify salary double;
desc p1;

关键字	含义
modify 列名 新类型	只改类型（列名不变）

也可以用 modify 挪位置（类型可写原类型）：

alter table p1 modify id int first;
desc p1;

alter table p1 modify sex char(1) after name;
desc p1;

易错：modify 后面必须写类型，不能只写列名。

类型改窄可能丢数据（概念）

例如列里已有 9999，再 modify 成过小的范围或不合适类型，可能报错或截断。练习表没数据时随便改；有数据时先：

-- 等学了 DML 再用
-- select * from p1;

6. ALTER：改列名（change）

alter table p1 change sex gender varchar(1);
desc p1;

关键字	含义
change 原列名 新列名 类型	改列名时类型也要写全

只改类型、不改名，用 modify 更省事；改名就用 change。

-- 只改类型
alter table p1 modify name varchar(50);

-- 改列名 + 类型一并写
alter table p1 change name username varchar(50);
desc p1;

7. ALTER：删除列

alter table p1 drop phone_number;
desc p1;

关键字	含义
drop 列名	删除该列

危险：drop 列会连同该列的数据一起删除，一般无法在 SQL 里简单「撤销」。不确定就先备份。

8. 修改库 / 表的编码与引擎

8.1 改库的编码

show create database train;
alter database train charset utf8mb4;
show create database train;

规则	说明
改库编码	不会自动改掉已经存在的表
新建表且未指定编码	才会用库的新编码

8.2 改表的编码与引擎

show create table p1;

alter table p1 charset=utf8mb4 engine=InnoDB;
show create table p1;

也可以拆开只改引擎或只改编码：

alter table p1 engine=InnoDB;
alter table p1 charset=utf8mb4;

8.3 表编码与列编码不一致（进阶）

有时表改成了 gbk，但字符列仍是 utf8mb4，show create table 里能看出来。需按列再改：

-- 示意：把某一字符列编码改到与表一致
-- alter table p1 modify name varchar(30) charset gbk;

步骤	建议
1	show create table p1 看表编码
2	看字符列是否带不同的 CHARACTER SET
3	用 modify ... charset 新编码 逐列对齐

入门策略：新项目统一 utf8mb4，尽量少来回改编码。

8.4 编码规则复习

建库：不指定 → MySQL 默认；指定了 → 用指定的
建表：不指定 → 用库的编码；指定了 → 用指定的
改库：不影响已存在的表
改表：不一定改到每一列，可能要 modify 列

9. 移动表 / 改名（rename table）

除了 alter table ... rename，还有：

-- 同库内改名
rename table p1 to personnel;
show tables;

rename table personnel to p1;

-- 跨库移动（表结构+数据一起「搬到」另一库，表名可不变）
-- rename table 库名.表名 to 目标库.表名;

准备第二个库并演示移动：

create database train_bak charset utf8mb4;
rename table train.p1 to train_bak.p1;

use train;
show tables;

use train_bak;
show tables;
desc p1;

改名后再移回去：

rename table train_bak.p1 to train.p1;

场景	写法
同库改表名	rename table 旧名 to 新名;
移到另一库	rename table 库.表 to 目标库.表;
移动并改名	rename table 库.表 to 目标库.新名;

在目标库或当前库时，库名有时可省略（视版本与上下文）；写全更不容易错。

10. 复制表

10.1 复制结构 + 数据

create table p2 select * from p1;
-- 或等价条件恒真：
create table p2 select * from p1 where 1=1;

10.2 只复制结构、不要数据

create table p3 select * from p1 where 1=2;
desc p3;
show tables;

条件	效果
where 1=1	条件恒真 → 数据都复制（常可省略）
where 1=2	条件恒假 → 只复制结构，数据不复制

说明：1=1 / 1=2 是课堂常用写法，表示「与表内容无关的真假条件」；不必死记其他复杂写法。

10.3 把数据批量灌进另一张结构兼容的表（预告）

等第 6 天学了 insert 之后，还会遇到：

-- insert into 目标表 select * from 来源表;

今天只要知道：复制表、导入数据是常见运维操作即可。

11. 完整跟练脚本

use train;
show tables;

-- 结构
create table personnel(id int);
desc personnel;

alter table personnel rename p1;
alter table p1 add salary int;
alter table p1 add sex char(1) after salary;
alter table p1 add name varchar(30);
alter table p1 add phone_number int first;
desc p1;

alter table p1 modify salary double;
alter table p1 change sex gender varchar(1);
alter table p1 modify id int first;
alter table p1 drop phone_number;
desc p1;

-- 编码与引擎
show create table p1;
alter table p1 charset=utf8mb4 engine=InnoDB;

-- 复制
create table p2 select * from p1;
create table p3 select * from p1 where 1=2;
show tables;
desc p3;

-- （可选）跨库移动再改回
create database train_bak charset utf8mb4;
rename table train.p2 to train_bak.p2;
use train_bak;
show tables;
rename table train_bak.p2 to train.p2;
use train;
show tables;

12. 速查表

目标	语句
改表名	alter table 旧名 rename 新名;
或	rename table 旧名 to 新名;
加列（末尾）	alter table 表 add 列 类型;
加列到最前	... add 列 类型 first;
加列到某列后	... add 列 类型 after 目标列;
改类型/位置	alter table 表 modify 列 类型 [first|after x];
改列名	alter table 表 change 旧列 新列 类型;
删列	alter table 表 drop 列;
改库编码	alter database 库 charset utf8mb4;
改表编码/引擎	alter table 表 charset=utf8mb4 engine=InnoDB;
跨库移动表	rename table 库.表 to 目标库.表;
复制表+数据	create table 新表 select * from 旧表;
只复制结构	create table 新表 select * from 旧表 where 1=2;

13. 常见错误

易错：modify 写成只改名却不写类型；改名要用 change 且类型写全。
易错：after 目标列 目标列名写错。
易错：改库编码后以为旧表编码也变了——并没有。
易错：rename table 跨库时源库/目标库名写反。
易错：误以为 drop 列 还能无损恢复。
语法错：看 at line 与 to use near，先检查逗号、括号、关键字拼写。

常见疑问

问：modify 和 change 到底怎么选？
只改类型或位置 → modify；要改列名 → change（必须带类型）。

问：生产上改表要什么流程？
一般要：评估影响 → 备份 → 在测试库演练 → 低峰执行 → 验证 desc 与业务读写。本系列实验环境可直接改。

问：复制表会不会把约束、索引完全复制？
create table ... select 主要复制列结构与数据；主键、索引、约束等不一定按原样完整带上（约束在第 15 天讲）。重要表建议用导出工具做完整迁移。

小结

1. 加列 add，删列 drop，改类型 modify，改列名 change。
2. 位置用 first / after 目标列 控制。
3. 编码：改库不影响旧表；改表可能还要对齐列编码。
4. rename table 可同库改名或跨库移动；create table ... select 可复制表，where 1=2 只要结构。
5. 下一篇进入 DML：往表里插入、修改、删除数据。

---

系列导航：总目录 · 上一篇：创建库和表 · 下一篇：数据的增删改