MySQL之 ALTER vs CHANGE vs MODIFY COLUMN
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;
