1.ALTER COLUMN
用于设置或者移除某一列的默认（缺省）值，
1.1用法
ALTER TABLE MyTable ALTER COLUMN foo SET DEFAULT 'bar';
ALTER TABLE MyTable ALTER COLUMN foo DROP DEFAULT;
1.2 举例
mysql> desc pre_common_block;
+----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------------+------+-----+---------+----------------+
| bid | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| blockclass | varchar(255) | NO | | 0 | |
| blocktype | tinyint(1) | NO | | 0 | |
| name | varchar(255) | NO | | | |
| title | text | NO | | NULL | |
| classname | varchar(255) | NO | | | |
| summary | text | NO | | NULL | |
| uid | mediumint(8) unsigned | NO | | 0 | |
| username | varchar(255) | NO | | | |
| styleid | smallint(6) unsigned | NO | | 0 | |
| blockstyle | text | NO | | NULL | |
| picwidth | smallint(6) unsigned | NO | | 0 | |
| picheight | smallint(6) unsigned | NO | | 0 | |
| target | varchar(255) | NO | | | |
| dateformat | varchar(255) | NO | | | |
| dateuformat | tinyint(1) | NO | | 0 | |
| script | varchar(255) | NO | | | |
| param | text | NO | | NULL | |
| shownum | smallint(6) unsigned | NO | | 0 | |
| cachetime | int(10) | NO | | 0 | |
| cachetimerange | char(5) | NO | | | |
| punctualupdate | tinyint(1) | NO | | 0 | |
| hidedisplay | tinyint(1) | NO | | 0 | |
| dateline | int(10) unsigned | NO | | 0 | |
| notinherited | tinyint(1) | NO | | 0 | |
| isblank | tinyint(1) | NO | | 0 | |
+----------------+-----------------------+------+-----+---------+----------------+
26 rows in set (0.14 sec) mysql> ALTER TABLE pre_common_block ALTER COLUMN blocktype set DEFAULT 5;
Query OK, 0 rows affected (0.32 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc pre_common_block;
+----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------------+------+-----+---------+----------------+
| bid | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| blockclass | varchar(255) | NO | | 0 | |
| blocktype | tinyint(1) | NO | | 5 | |
| name | varchar(255) | NO | | | |
| title | text | NO | | NULL | |
| classname | varchar(255) | NO | | | |
| summary | text | NO | | NULL | |
| uid | mediumint(8) unsigned | NO | | 0 | |
| username | varchar(255) | NO | | | |
| styleid | smallint(6) unsigned | NO | | 0 | |
| blockstyle | text | NO | | NULL | |
| picwidth | smallint(6) unsigned | NO | | 0 | |
| picheight | smallint(6) unsigned | NO | | 0 | |
| target | varchar(255) | NO | | | |
| dateformat | varchar(255) | NO | | | |
| dateuformat | tinyint(1) | NO | | 0 | |
| script | varchar(255) | NO | | | |
| param | text | NO | | NULL | |
| shownum | smallint(6) unsigned | NO | | 0 | |
| cachetime | int(10) | NO | | 0 | |
| cachetimerange | char(5) | NO | | | |
| punctualupdate | tinyint(1) | NO | | 0 | |
| hidedisplay | tinyint(1) | NO | | 0 | |
| dateline | int(10) unsigned | NO | | 0 | |
| notinherited | tinyint(1) | NO | | 0 | |
| isblank | tinyint(1) | NO | | 0 | |
+----------------+-----------------------+------+-----+---------+----------------+
26 rows in set (0.02 sec)
2.ALTER COLUMN
用于某一数据列的重命名、修改数据类型或者删除。
2.1用法
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL AFTER baz;
2.2 举例
mysql> desc pre_common_block;
+----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------------+------+-----+---------+----------------+
| bid | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| blockclass | varchar(255) | NO | | 0 | |
| blocktype | tinyint(1) | NO | | 5 | |
| name | varchar(255) | NO | | | |
| title | text | NO | | NULL | |
| classname | varchar(255) | NO | | | |
| summary | text | NO | | NULL | |
| uid | mediumint(8) unsigned | NO | | 0 | |
| username | varchar(255) | NO | | | |
| styleid | smallint(6) unsigned | NO | | 0 | |
| blockstyle | text | NO | | NULL | |
| picwidth | smallint(6) unsigned | NO | | 0 | |
| picheight | smallint(6) unsigned | NO | | 0 | |
| target | varchar(255) | NO | | | |
| dateformat | varchar(255) | NO | | | |
| dateuformat | tinyint(1) | NO | | 0 | |
| script | varchar(255) | NO | | | |
| param | text | NO | | NULL | |
| shownum | smallint(6) unsigned | NO | | 0 | |
| cachetime | int(10) | NO | | 0 | |
| cachetimerange | char(5) | NO | | | |
| punctualupdate | tinyint(1) | NO | | 0 | |
| hidedisplay | tinyint(1) | NO | | 0 | |
| dateline | int(10) unsigned | NO | | 0 | |
| notinherited | tinyint(1) | NO | | 0 | |
| isblank | tinyint(1) | NO | | 0 | |
+----------------+-----------------------+------+-----+---------+----------------+
26 rows in set (0.02 sec) mysql> ALTER TABLE pre_common_block CHANGE COLUMN blocktype changetype tinyint(1) NOT NULL
Query OK, 2 rows affected (0.77 sec)
Records: 2 Duplicates: 0 Warnings: 0 mysql> desc pre_common_block;
+----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------------+------+-----+---------+----------------+
| changetype | tinyint(1) | NO | | NULL | |
| bid | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| blockclass | varchar(255) | NO | | 0 | |
| name | varchar(255) | NO | | | |
| title | text | NO | | NULL | |
| classname | varchar(255) | NO | | | |
| summary | text | NO | | NULL | |
| uid | mediumint(8) unsigned | NO | | 0 | |
| username | varchar(255) | NO | | | |
| styleid | smallint(6) unsigned | NO | | 0 | |
| blockstyle | text | NO | | NULL | |
| picwidth | smallint(6) unsigned | NO | | 0 | |
| picheight | smallint(6) unsigned | NO | | 0 | |
| target | varchar(255) | NO | | | |
| dateformat | varchar(255) | NO | | | |
| dateuformat | tinyint(1) | NO | | 0 | |
| script | varchar(255) | NO | | | |
| param | text | NO | | NULL | |
| shownum | smallint(6) unsigned | NO | | 0 | |
| cachetime | int(10) | NO | | 0 | |
| cachetimerange | char(5) | NO | | | |
| punctualupdate | tinyint(1) | NO | | 0 | |
| hidedisplay | tinyint(1) | NO | | 0 | |
| dateline | int(10) unsigned | NO | | 0 | |
| notinherited | tinyint(1) | NO | | 0 | |
| isblank | tinyint(1) | NO | | 0 | |
+----------------+-----------------------+------+-----+---------+----------------+
26 rows in set (0.00 sec)
3.MODIFY COLUMN
除了重命名外能够做CHANGE COLUMN能做的一切工作。
3.1用法
ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;