当前位置：首页 > 问答 > 正文

MySQL里改表那些事儿，实际操作代码给你参考下

增加字段

你想在已有的表里加一个新栏目，比如在user表里加一个phone号码字段。

基本语法长这样：

ALTER TABLE 表名 ADD COLUMN 新字段名 字段类型 [约束条件] [位置];

加在最后面：这是最简单的方式。
```
ALTER TABLE user ADD COLUMN phone varchar(20);
```
这句代码执行完，user表的最后一列就会多出一个phone字段，类型是字符串,最多存20个字符。
加在某个字段后面：比如你想让phone字段放在email字段的后面。
```
ALTER TABLE user ADD COLUMN phone varchar(20) AFTER email;
```
这个AFTER关键字很实用,可以让你精细地控制字段的顺序。
加在最前面：虽然不常用,但也可以。
```
ALTER TABLE user ADD COLUMN id int FIRST;
```
用FIRST关键字就行。

删除字段

觉得某个字段没用了，想删掉它。（警告：这个操作非常危险，数据会直接消失，务必先备份！）

ALTER TABLE user DROP COLUMN phone;

就这么简单，phone字段和它里面的所有数据就都没了，所以在执行DROP之前,一定要三思。

修改字段（这里面花样最多）

修改字段分为几种情况,用的关键字也不太一样。

只改字段类型或约束 你觉得原来phone字段的varchar(20)不够用了，想改成varchar(50)。
```
ALTER TABLE user MODIFY COLUMN phone varchar(50);
```
用MODIFY关键字，注意，如果表里已经有数据了，修改类型可能会失败（比如原来有字符串，你现在要改成数字类型），或者导致数据被截断（比如原来有超过50位的手机号，会被砍掉）。
重命名字段并修改类型 你想把phone字段改名为mobile_phone,同时把类型也改了。
```
ALTER TABLE user CHANGE COLUMN phone mobile_phone varchar(100);
```
用CHANGE关键字，它比MODIFY更强力，可以同时改变字段名和属性，注意，语法是CHANGE 旧字段名新字段名新类型。

修改字段的默认值

给字段设一个默认值，比如新用户注册时，如果没填状态，就默认是1（有效）。

-- 设置默认值
ALTER TABLE user ALTER COLUMN status SET DEFAULT 1;
-- 删除默认值
ALTER TABLE user ALTER COLUMN status DROP DEFAULT;

用ALTER COLUMN ... SET/DROP DEFAULT。

重命名表

觉得表名起得不好,想换一个。

RENAME TABLE user TO user_backup;

或者用另一种写法：

ALTER TABLE user RENAME TO user_backup;

两种方法效果一样。

操作索引（加快查询速度的关键）

添加索引：比如我们经常用username来登录，那就给它加个索引,查起来快得多。

-- 添加普通索引
ALTER TABLE user ADD INDEX idx_username (username);
-- 添加唯一索引（确保用户名不能重复）
ALTER TABLE user ADD UNIQUE INDEX uni_username (username);

idx_开头的通常是普通索引，uni_开头的通常是唯一索引。

删除索引

ALTER TABLE user DROP INDEX idx_username;

一些非常重要的实际操作建议

这些是血泪教训,比记住语法更重要：

备份！备份！备份！ 尤其是在生产环境的数据库上执行ALTER操作之前，一定要先把整张表或者整个数据库备份一遍，万一改错了，还有后悔药吃，可以用mysqldump命令来备份。
在测试环境先试试：不要直接在主数据库上操作，先在本地或者测试数据库上把SQL语句跑一遍,确认没问题再上生产环境。
小心大数据表：如果你的表有几百万、几千万条数据，直接ALTER TABLE可能会锁表很长时间，导致网站或应用在此期间无法访问该表，对于大表，有更高级的工具和方法（比如pt-online-schema-change，这个是Percona Toolkit里的一个工具，可以在线改表结构而不长时间锁表）,但那就涉及更专业的知识了。
关注字段类型变化的影响：比如把varchar改小可能会丢数据；把int改成bigint一般没问题,但反过来就可能出问题。
一次尽量只做一个操作：虽然可以把多个ADD、DROP、MODIFY写在一个ALTER TABLE语句里，但为了稳妥起见，特别是新手，建议一次只执行一个修改操作，这样万一出错,也容易定位问题。