mysql 修改表的语法详解

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