修改表字段类型

基本语法

1
2
3
4
alter table 表名 modify 字段名 字段类型;
-- 示例
-- 将user表的password类型改为varchar(30)
alter table user modify password varchar(30);
1
2
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看表结构

1
desc user;
1
2
3
4
5
6
7
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | NULL    |       |
| password | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

增加表字段

基本操作

1
2
3
4
5
6
alter table 表名 add column 字段名 类型;
-- 示例
-- user表增加age字段
alter table user add column age int(3);
-- 查看表结构
desc user；
1
2
3
4
5
6
7
8
9
10
11
12
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

<!-- mysql> desc user; -->
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | NULL    |       |
| password | varchar(30) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

增加字段时，控制字段顺序

普通地增加字段，所增加的字段总是在表的最后一列
如何增加到指定两个字段之间呢

1
2
3
4
alter table 表名 add 字段名 类型 after 字段名;
-- 示例
-- 增加sex字段，位于password后面
alter table user add sex int(1) after password;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> alter table user add sex int(1) after password;
Query OK, 0 rows affected, 1 warning (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | NULL    |       |
| password | varchar(30) | YES  |     | NULL    |       |
| sex      | int         | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

在表的最前方位置增加一个字段

1
2
3
4
alter table 表名 add 字段名 类型 first;
-- 示例
-- 增加id字段，在最前面
alter table user add id int(10) first;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> alter table user add id int(10) first;
Query OK, 0 rows affected, 1 warning (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
| password | varchar(30) | YES  |     | NULL    |       |
| sex      | int         | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除表字段

基本用法

1
2
3
4
alter table 表名 drop column 字段名；
-- 示例
-- 删除sex字段
alter table user drop column sex;

表字段改名

基本用法

1
2
3
4
alter table 表名 change 字段原名 字段新名 字段类型;
-- 示例
alter table user add email varchar(30);
alter table user change email mobile varchar(30);

调整字段顺序

1
2
3
4
5
6
alter table 表名 modify 字段名 类型 first;
-- 示例
alter table user modify mobile varchar(30) first;
alter table user modify age varchar(20) after password;
alter table user modify mobile varchar(30) after age;
desc user;
1
2
3
4
5
6
7
8
9
10
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
| password | varchar(30) | YES  |     | NULL    |       |
| age      | varchar(20) | YES  |     | NULL    |       |
| mobile   | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)