之前在win7下用sqlyog客户端操作mysql数据库时有个功能“reorder column”(列顺序调整)在表结构定义时被经常用到,后来在mac上使用navicat客户端(可能版本不对)居然没找到类似功能,所以只好写sql语句来实现,顺便总结下mysql 列操作相关的sql语法。
1、mysql5.7参考手册 与列操作相关的sql语法
ALTER [IGNORE] TABLE tbl_name
| ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
准备一张表,所有操作基于该表:
CREATE TABLE `jdbc_student` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2、新增、删除列
1)新增列:gendar
alter table jdbc_student add column gendar varchar(1) not null comment '性别' after age
在age列后面新增一列gendar,varchar(1),非空,注释:性别
2)删除列:gendar
alter table jdbc_student drop column gendar
3、修改列
修改列有2个关键字:change和modify
- alter table tbl change col col …
- alter table tbl modify col …
两者区别:
- change 可以修改列名,若列名不变修改列其它属性则需要多写一次列名
- modify 不可以修改列名,较change可以少些一次列名,语法看书更简洁
注意事项:
不管change还是modify,列原有属性需要带上,否则会丢失。
将上述新建的字段:gendar 由varchar(1) 改成 char(1)
alter table jdbc_student change gendar gendar char(1) not null comment '性别'
alter table jdbc_student modify gendar char(1) not null comment '性别'
4、调整列顺序
调整列顺序实际上还是修改列,因其操作特殊性,单独列出。
将上述新增的gendar字段,放到age前面,sql如下:
--先通过change调到age前,name后
alter table jdbc_student change gendar gendar char(1) not null comment '性别' after name
--再通过modify调到age后
alter table jdbc_student modify gendar char(1) not null comment '性别' after age