mysql 修改表的详细语法介绍
?
ALTER [IGNORE] TABLE tbl_name
? ? [alter_specification [, alter_specification] ...]
? ? [partition_options]
?
algorithm_option:
? ? ALGORITHM [=] {DEFAULT|INPLACE|COPY}
?
lock_option:
? ? LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
?
alter_specification:
? ? table_options
? | ADD [COLUMN] col_name column_definition
? ? ? ? [FIRST | AFTER col_name ]
? | ADD [COLUMN] (col_name column_definition,...)
? | ADD {INDEX|KEY} [index_name]
? ? ? ? [index_type] (index_col_name,...) [index_option] ...
? | ADD [CONSTRAINT [symbol]] PRIMARY KEY
? ? ? ? [index_type] (index_col_name,...) [index_option] ...
? | ADD [CONSTRAINT [symbol]]
? ? ? ? UNIQUE [INDEX|KEY] [index_name]
? ? ? ? [index_type] (index_col_name,...) [index_option] ...
? | ADD FULLTEXT [INDEX|KEY] [index_name]
? ? ? ? (index_col_name,...) [index_option] ...
? | ADD SPATIAL [INDEX|KEY] [index_name]
? ? ? ? (index_col_name,...) [index_option] ...
? | ADD [CONSTRAINT [symbol]]
? ? ? ? FOREIGN KEY [index_name] (index_col_name,...)
? ? ? ? reference_definition
? | ALGORITHM [=] {DEFAULT|INPLACE|COPY}
? | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
? | CHANGE [COLUMN] old_col_name new_col_name column_definition
? ? ? ? [FIRST|AFTER col_name]
? | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
? | MODIFY [COLUMN] col_name column_definition
? ? ? ? [FIRST | AFTER col_name]
? | DROP [COLUMN] col_name
? | DROP PRIMARY KEY
? | DROP {INDEX|KEY} index_name
? | DROP FOREIGN KEY fk_symbol
? | DISABLE KEYS
? | ENABLE KEYS
? | RENAME [TO|AS] new_tbl_name
? | RENAME {INDEX|KEY} old_index_name TO new_index_name
? | ORDER BY col_name [, col_name] ...
? | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
? | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
? | DISCARD TABLESPACE
? | IMPORT TABLESPACE
? | FORCE
? | ADD PARTITION (partition_definition)
? | DROP PARTITION partition_names
? | DISCARD PARTITION {partition_names | ALL} TABLESPACE
? | IMPORT PARTITION {partition_names | ALL} TABLESPACE
? | TRUNCATE PARTITION {partition_names | ALL}
? | COALESCE PARTITION number
? | REORGANIZE PARTITION partition_names INTO (partition_definitions)
? | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
? | ANALYZE PARTITION {partition_names | ALL}
? | CHECK PARTITION {partition_names | ALL}
? | OPTIMIZE PARTITION {partition_names | ALL}
? | REBUILD PARTITION {partition_names | ALL}
? | REPAIR PARTITION {partition_names | ALL}
? | REMOVE PARTITIONING
?
?
常用的操作的例子：
1、修改列名，列类型，列名： 下面语句将列名为old_column的列名修改成new_column，并且数据类型为int
?
>alter table tablename change old_column new_column int ;
?
2、修改列的数据类型：将column_name?这个列的数据类型修改成为new_datatype?
?
>alter table tablename modify column_name new_datatype ;
?
3、添加字段
?
>alter table tablename add column_name?column_datatype ;
?
4、删除主键
?
>alter table tablename ?DROP PRIMARY KEY
?
5、删除分区
?
>alter table tablename ?DROP PARTITION partition_names
?
6、删除索引
?
>alter table tablename?DROP INDEX index_name
?
7、删除列
?
>alter table tablename?DROP ?col_name
?
原文：http://daizj.iteye.com/blog/2229170