【MySQL】聊聊MySQL中alter的那些事

---

使用的MySQL版本为：5.1.73

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.10 sec)

三个月前，小李在MySQL中创建了一张user_tb表用来存储用户信息，当时系统能获取到的用户信息就只有用户id和用户名称两个字段信息，所以小李就写下了以下建表sql语句：

drop table if exists user_tb;
create table if not exists user_tb (
user_id   varchar(5) comment '用户ID',
user_name varchar(200) not null unique comment '用户名称',
primary key (user_id)
) comment '用户信息表' engine=innodb default charset=utf8;

drop table if exists order_tb;
create table if not exists order_tb (
order_id    varchar(7) comment '订单id',
order_time  timestamp not null default current_timestamp comment '下单时间',
user_id     varchar(5) not null comment '用户id',
order_amt   int comment '订单金额',
primary key (order_id),
constraint user_id_for foreign key (user_id) references user_tb (user_id)
) comment '订单表' engine=innodb default charset=utf8;

最近公司业务猛增，技术不断迭代更新，以前的表结构已经满足不了现在业务的需求和技术的要求，老板找到了小李，要求小李根据现有业务情况对以前表结构进行修改，并且不能影响现有业务系统运行。

于是，MySQL的alter故事在小李身上开始了。

新增字段

老板：现在我们可以获取更多的用户信息，我们应该将这些信息存起来，以便以后进行分析及推荐等应用，小李，你将用户手机号码、用户省市区、用户性别、用户年龄等字段补充到用户表中。

-- 同时添加多个字段必须要有column关键字
alter table user_tb add column age tinyint comment '年龄', 
                    add column region varchar(11) comment '区县', 
					add column city varchar(11) comment '地市', 
					add column provice varchar(11) comment '省份';

-- 添加单个字段可以不用column关键字
alter table user_tb add gender tinyint comment '性别(1：男，2：女)' after age;

☆ 新增字段时如果需要指定字段顺序，可以通过first和after两个关键字设置

删除字段

-- 删除字段
alter table user_tb drop column region,
                    drop column city,
					drop column provice;
-- 新增字段
alter table user_tb add column region varchar(11) comment '区县', 
					add column city varchar(11) comment '地市', 
					add column provice varchar(11) comment '省份';

修改字段类型长度及名称

老板：小李，现在我们的用户量已经上万了，按照这个增长速度，很快就突破10万了，你表中user_id字段的长度肯定不够啊，还有订单量的order_id字段也是同样的问题，还有你的用户表中的省市区肯定是存id而不是直接存名称，将字段名称都加_id后缀，赶紧改了。

小李娴熟的写下了以下sql语句：

mysql> alter table user_tb modify user_id varchar(11);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table order_tb modify user_id varchar(11);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改字段名称时没有备注comment的情况，修改的字段以前的comment没有了

alter table user_tb change region region_id varchar(11),
                    change city city_id varchar(11),
					change provice provice_id varchar(11);

所以在修改字段名称时如果要保证以前的comment保留，在修改语句中也要有comment

-- 先删除以前的字段
alter table user_tb drop column region_id,
                    drop column city_id,
					drop column provice_id;
-- 再新增字段
alter table user_tb add column region varchar(11) comment '区县', 
					add column city varchar(11) comment '地市', 
					add column provice varchar(11) comment '省份';

-- 修改新增的字段名称
alter table user_tb change region region_id varchar(11) comment '区县id',
                    change city city_id varchar(11) comment '地市id',
					change provice provice_id varchar(11) comment '省份id';

修改字段顺序

老板：小李，你的用户表中按常规的字段顺序是省市区，你的顺序反了，都调节过来吧。

alter table user_tb modify city_id varchar(11) comment '地市id' after provice_id,
                    modify region_id varchar(11) comment '区县id' after city_id;

此处也要注意comment

ALTER TABLE 对 Null 值和默认值的影响

当你修改字段时，你可以指定是否包含值或者是否设置默认值。如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

alter table user_tb modify provice_id varchar(11) comment '省份id' default '湖南';

修改字段默认值

alter table user_tb alter provice_id set default '430000',
                    alter city_id set default '430101';

删除字段默认值

alter table user_tb alter provice_id drop default,
                    alter city_id drop default;

删除约束

老板：表中没必要设置外键，把外键给去掉吧

-- 删除外键
alter table order_tb drop foreign key user_id_for;
-- 删除主键
alter table user_tb drop primary key;

修改表引擎

alter table user_tb engine=myisam;

修改表名称

alter table user_tb rename to user_info;