13.2.4 DDL语句 2.修改表结构的语法

发表于 更新于 分类于 ， ， Waline： 本文字数： 7.5k 阅读时长 ≈ 7 分钟

13.2.4 DDL语句 2.修改表结构的语法

修改表结构使用alter table，修改表结构包括增加列定义、修改列定义、删除列、重命名列等操作。

增加列定义

增加列定义的语法如下:

1
2
3
4
5
6
alter table 表名
add
(
    列名 类型 [default 默认值],
    ...
);

上面的语法格式中圆括号部分与建表语法的圆括号部分完全相同，只是此时圆括号里的列定义是追加到已有表的列定义后面。还有一点需要指出，如果只是新增一列，则可以省略圆括号，仅在add后紧跟一个列定义即可。

实例

现在为hehe表增加一个hehe_id字段,如下所示:

1
2
alter table hehe
add hehe_id int;

运行效果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> desc hehe;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | NO   |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

mysql> alter table hehe
add hehe_id int;
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc hehe;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | varchar(10) | NO   |     | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| hehe_id | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set

再添加两个字段,如下所示:

1
2
3
4
5
6
alter table hehe
add
(
    aaa varchar(255) default 'xxx',
    bbb varchar(255)
);

运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> desc hehe;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | varchar(10) | NO   |     | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| hehe_id | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set

mysql> alter table hehe
add
(
    aaa varchar(255) default 'xxx',
    bbb varchar(255)
);
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc hehe;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | varchar(10)  | NO   |     | NULL    |       |
| name    | varchar(30)  | YES  |     | NULL    |       |
| hehe_id | int(11)      | YES  |     | NULL    |       |
| aaa     | varchar(255) | YES  |     | xxx     |       |
| bbb     | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set

上面第二条SQL语句增加aaa字段时，为该字段指定默认值为'xxx'。值得指出的是，**SQL语句中的字符串值不是用双引号引起，而是用单引号引起的**。
增加字段时需要注意：如果数据表中已有数据记录，除非给新增的列指定了默认值，否则新增的数据列不可指定非空约束，因为那些已有的记录在新增列上肯定是空（实际上，修改表结构很容易失败，只要新增的约束与已有数据冲突，修改就会失败）。

修改列定义

修改列定于的语法如下:

1
2
3
alter table 表名
modify 列名 类型 [default 默认值]
[first|after col_name];

上面语法中first或者aftercol_name指定需要将目标修改到指定位置。
从上面修改语法中可以看出，该修改语句每次只能修改一个列定义，如下代码所示：

实例

将hehe表的hehe_id该成varchar(255)类型

1
2
alter table hehe
modify hehe_id varchar(255);

运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> desc hehe;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | varchar(10)  | NO   |     | NULL    |       |
| name    | varchar(30)  | YES  |     | NULL    |       |
| hehe_id | int(11)      | YES  |     | NULL    |       |
| aaa     | varchar(255) | YES  |     | xxx     |       |
| bbb     | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set

mysql> alter table hehe
modify hehe_id varchar(255);
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc hehe;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | varchar(10)  | NO   |     | NULL    |       |
| name    | varchar(30)  | YES  |     | NULL    |       |
| hehe_id | varchar(255) | YES  |     | NULL    |       |
| aaa     | varchar(255) | YES  |     | xxx     |       |
| bbb     | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set
mysql>

将hehe表的bbb列修改成int类型

1
2
alter table hehe
modify bbb int;

运行效果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> desc hehe;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | varchar(10)  | NO   |     | NULL    |       |
| name    | varchar(30)  | YES  |     | NULL    |       |
| hehe_id | varchar(255) | YES  |     | NULL    |       |
| aaa     | varchar(255) | YES  |     | xxx     |       |
| bbb     | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set

mysql> alter table hehe
modify bbb int;
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc hehe;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | varchar(10)  | NO   |     | NULL    |       |
| name    | varchar(30)  | YES  |     | NULL    |       |
| hehe_id | varchar(255) | YES  |     | NULL    |       |
| aaa     | varchar(255) | YES  |     | xxx     |       |
| bbb     | int(11)      | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set
mysql>

从上面代码中不难看出，使用SQL修改数据表里列定义的语法和为数据表只增加一个列定义的语法几乎完全一样，关键是增加列定义使用add关键字，而修改列定义使用modify关键字。还有一点需要指出，**add新增的列名必须是原表中不存在的，而modify修改的列名必须是原表中已存在的。
注意
虽然MySQL的一个modify命令不支持一次修改多个列定义，但其他数据库如Oracle支持一个modify命令修改多个列定义，一个modify命令修改多个列定义的语法和一个add命令增加多个列定义的语法非常相似，也需要使用圆括号把多个列定义括起来。如果需要让MySQL支持一次修改多个列定义，则可在alter table后使用多个modify命令**。

如果数据表里已有数据记录，则修改列定义非常容易失败，因为有可能修改的列定义规则与原有的数据记录不符合。如果修改数据列的默认值，则只会对以后的插入操作有作用，对以前已经存在的数据不会有任何影响。

删除列的语法

从数据表中删除列的语法比较简单：

1
2
alter table 表名
drop column_name

删除列只要在drop后紧跟需要删除的列名即可

实例

删除hehe表中的aaa字段

1
2
alter table hehe
drop aaa;

运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> desc hehe;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | varchar(10)  | NO   |     | NULL    |       |
| name    | varchar(30)  | YES  |     | NULL    |       |
| hehe_id | varchar(255) | YES  |     | NULL    |       |
| aaa     | varchar(255) | YES  |     | xxx     |       |
| bbb     | int(11)      | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set

mysql> alter table hehe
drop aaa;
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc hehe;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | varchar(10)  | NO   |     | NULL    |       |
| name    | varchar(30)  | YES  |     | NULL    |       |
| hehe_id | varchar(255) | YES  |     | NULL    |       |
| bbb     | int(11)      | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set

从数据表中删除列定义通常总是可以成功，删除列定义时将从每行中删除该列的数据，并释放该列在数据块中占用的空间。所以删除大表中的字段时需要比较长的时间，因为还需要回收空间。

MySQL独有语法

上面介绍的这些增加列、修改列和删除列的语法是标准的SQL语法，对所有的数据库都通用。除此之外，MySQL还提供了两种特殊的语法：重命名数据表和完全改变列定义。

重命名数据表

重命名数据表的语法格式如下：

1
2
alter table 表名
rename to 新表名;

实例

如下SQL语句用于将hehe表命名为wawa：

1
2
alter table hehe
rename to wawa;

运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> show tables;
+-----------------------+
| Tables_in_test_engine |
+-----------------------+
| hehe                  |
| user_inf              |
+-----------------------+
2 rows in set

mysql> alter table hehe
rename to wawa;
Query OK, 0 rows affected

mysql> show tables;
+-----------------------+
| Tables_in_test_engine |
+-----------------------+
| user_inf              |
| wawa                  |
+-----------------------+
2 rows in set
mysql>

改变列名

MySQL为altertable提供了change选项，该选项可以改变列名。change选项的语法如下：

1
2
3
alter table 表名
change 旧列名 新列名 [default 默认值]
[first|after 列明];

对比change和modify两个选项，不难发现：change选项比modify选项多了一个列名，因为**change选项可以改变列名，所以它需要两个列名。一般而言，如果不需要改变列名，使用alter table的modify选项即可，只有当需要修改列名时才会使用change选项**。

实例

将wawa数据表的bbb字段重命名为ddd:

1
2
alter table wawa
change bbb ddd int;

运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> desc wawa;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | varchar(10)  | NO   |     | NULL    |       |
| name    | varchar(30)  | YES  |     | NULL    |       |
| hehe_id | varchar(255) | YES  |     | NULL    |       |
| bbb     | int(11)      | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set

mysql> alter table wawa
change bbb ddd int;
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc wawa;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | varchar(10)  | NO   |     | NULL    |       |
| name    | varchar(30)  | YES  |     | NULL    |       |
| hehe_id | varchar(255) | YES  |     | NULL    |       |
| ddd     | int(11)      | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set
mysql>