mysql 改变字段 位置_MySQL中新增或修改字段时调整其位置

基本上每种数据库修改表结构都是使用alter table语句，MySQL也不例外，其通过alter table语句的add、change、modify等不同的选项可以实现字段增加和修改。

例如，增加字段

root@database-one 22:53: [gftest]> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename | varchar(10) | YES | | NULL | |

| hiredate | date | YES | | NULL | |

| sal | decimal(10,2) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

4 rows in set (0.33 sec)

root@database-one 22:53: [gftest]> alter table emp add column age int;

Query OK, 0 rows affected (0.91 sec)

Records: 0 Duplicates: 0 Warnings: 0

root@database-one 22:54: [gftest]> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename | varchar(10) | YES | | NULL | |

| hiredate | date | YES | | NULL | |

| sal | decimal(10,2) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

常规的增加字段，数据库都是将新增的字段放到最后面，但是MySQL中却有个有意思的小特性，可以在增加字段时指定字段在表中的位置。比如我们想将新增的age字段放到ename和hiredate之间

root@database-one 22:54: [gftest]> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename | varchar(10) | YES | | NULL | |

| hiredate | date | YES | | NULL | |

| sal | decimal(10,2) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

root@database-one 22:54: [gftest]>

root@database-one 22:58: [gftest]>

root@database-one 22:58: [gftest]> alter table emp drop column age;

Query OK, 0 rows affected (0.34 sec)

Records: 0 Duplicates: 0 Warnings: 0

root@database-one 23:00: [gftest]> alter table emp add column age int after ename;

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

root@database-one 23:00: [gftest]> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename | varchar(10) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| hiredate | date | YES | | NULL | |

| sal | decimal(10,2) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

5 rows in set (0.06 sec)

也可以修改已存在字段的位置，比如将hiredate放到sal后边

root@database-one 23:00: [gftest]> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename | varchar(10) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| hiredate | date | YES | | NULL | |

| sal | decimal(10,2) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

5 rows in set (0.06 sec)

root@database-one 23:05: [gftest]>

root@database-one 23:05: [gftest]> alter table emp modify hiredate date after sal;

Query OK, 0 rows affected (0.57 sec)

Records: 0 Duplicates: 0 Warnings: 0

root@database-one 23:05: [gftest]> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename | varchar(10) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| sal | decimal(10,2) | YES | | NULL | |

| hiredate | date | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

5 rows in set (0.06 sec)