WeB LoG'S JuUiER: ALTER TABLE Syntax of MySQL

Thursday, August 5, 2010

ALTER TABLE Syntax of MySQL

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
  alter_specification [, alter_specification] ...

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
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
      [FIRST|AFTER col_name]
| 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] new_tbl_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
| partition_options
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| COALESCE PARTITION number
| REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
| 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 }
| PARTITION BY partitioning_expression
| REMOVE PARTITIONING

index_col_name:
  col_name [(length)] [ASC | DESC]

index_type:
  USING {BTREE | HASH | RTREE}

index_option:
  KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name

table_options:
  table_option [[,] table_option] ...  (see CREATE TABLE options)