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

「爱情、让人受尽委屈。」 2021-09-25 08:38 495阅读 0赞

### 目录 ###

*  新增字段
 *  删除字段
 *  修改字段类型长度及名称
 *  修改字段顺序
 *  ALTER TABLE 对 Null 值和默认值的影响
 *  删除约束
 *  修改表引擎
 *  修改表名称

--------------------

使用的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 '省份';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70]

# 修改字段类型长度及名称 #

老板：小李，现在我们的用户量已经上万了，按照这个增长速度，很快就突破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);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70 1]

所以在修改字段名称时如果要保证以前的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';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70 2]

# 修改字段顺序 #

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

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;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70]: /images/20210923/574567fa63b14798985faf3afad11d14.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70 1]: /images/20210923/e9be989fb0d246f590b8741086a330df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70 2]: /images/20210923/f137742b55894827987ae213f24ef10b.png