Syntax:
ALTER [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

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
  | {WITHOUT|WITH} VALIDATION
  | 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
  | UPGRADE PARTITIONING

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

index_type:
    USING {BTREE | HASH}

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

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

partition_options:
    (see CREATE TABLE options)

ALTER TABLE changes the structure of a table. For example, you can add
or delete columns, create or destroy indexes, change the type of
existing columns, or rename columns or the table itself. You can also
change characteristics such as the storage engine used for the table or
the table comment.

Following the table name, specify the alterations to be made. If none
are given, ALTER TABLE does nothing.

The syntax for many of the permissible alterations is similar to
clauses of the CREATE TABLE statement. See [HELP CREATE TABLE], for
more information.

table_options signifies table options of the kind that can be used in
the CREATE TABLE statement, such as ENGINE, AUTO_INCREMENT,
AVG_ROW_LENGTH, MAX_ROWS, ROW_FORMAT, or TABLESPACE. For a list of all
table options and a description of each, see [HELP CREATE TABLE].
However, ALTER TABLE ignores the DATA DIRECTORY and INDEX DIRECTORY
table options.

Use of table options with ALTER TABLE provides a convenient way of
altering single table characteristics. For example, if t1 is currently
not an InnoDB table, this statement changes its storage engine to
InnoDB:

ALTER TABLE t1 ENGINE = InnoDB;

To change the InnoDB table to use compressed row-storage format:

ALTER TABLE t1 ROW_FORMAT = COMPRESSED;

If the InnoDB tablespace encryption feature is enabled (see
http://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.htm
l), encryption for t1 can be enabled or disabled like this:

ALTER TABLE t1 ENCRYPTION='Y';
ALTER TABLE t1 ENCRYPTION='N';

To disable encryption for an InnoDB table, set ENCRYPTION='N' using
ALTER TABLE.

To reset the current auto-increment value:

ALTER TABLE t1 AUTO_INCREMENT = 13;

To change the default table character set:

ALTER TABLE t1 CHARACTER SET = utf8;

To add (or change) a table comment:

ALTER TABLE t1 COMMENT = 'New table comment';

NDB_TABLE options in table comments MySQL Cluster NDB 7.5.2 and later
supports setting NDB_TABLE options for controlling a table's partition
balance (fragment count type), read-from-any-replica capability, full
replication, or any combination of these, as part of the table comment
for an ALTER TABLE statement in the same manner as for CREATE TABLE, as
shown in this example:

mysql> ALTER TABLE t1
     > COMMENT = "NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RA_BY_NODE";
Query OK, 0 rows affected (1.73 sec)
Records: 0  Duplicates: 0  Warnings: 0

To verify that the table options were changed as you intend, use SHOW
CREATE TABLE, or query INFORMATION_SCHEMA.TABLES. Bear in mind that
ALTER TABLE ... COMMENT ... discards any existing comment for the
table. See
http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-c
omment-ndb-table-options, for additional information and examples.

partition_options signifies options that can be used with partitioned
tables for repartitioning, for adding, dropping, discarding, importing,
merging, and splitting partitions, and for performing partitioning
maintenance. It is possible for an ALTER TABLE statement to contain a
PARTITION BY or REMOVE PARTITIONING clause in an addition to other
alter specifications, but the PARTITION BY or REMOVE PARTITIONING
clause must be specified last after any other specifications. The ADD
PARTITION, DROP PARTITION, DISCARD PARTITION, IMPORT PARTITION,
COALESCE PARTITION, REORGANIZE PARTITION, EXCHANGE PARTITION, ANALYZE
PARTITION, CHECK PARTITION, and REPAIR PARTITION options cannot be
combined with other alter specifications in a single ALTER TABLE, since
the options just listed act on individual partitions. For more
information about partition options, see [HELP CREATE TABLE], and
http://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations
.html. For information about and examples of ALTER TABLE ... EXCHANGE
PARTITION statements, see
http://dev.mysql.com/doc/refman/5.7/en/partitioning-management-exchange
.html.

Prior to MySQL 5.7.6, partitioned InnoDB tables used the generic
ha_partition partitioning handler employed by MyISAM and other storage
engines not supplying their own partitioning handlers; in MySQL 5.7.6
and later, such tables are created using the InnoDB storage engine's
own (or "native") partitioning handler. Beginning with MySQL 5.7.9, you
can upgrade an InnoDB table that was created in MySQL 5.7.6 or earlier
(that is, created using ha_partition) to the InnoDB native partition
handler using ALTER TABLE ... UPGRADE PARTITIONING. (Bug #76734, Bug
#20727344) This version of ALTER TABLE does not accept any other
options and can be used only on a single table at a time.

*Note*: You can also use mysql_upgrade in MySQL 5.7.9 or later to
upgrade older partitioned InnoDB tables to the native partitioning
handler.

Some operations may result in warnings if attempted on a table for
which the storage engine does not support the operation. These warnings
can be displayed with SHOW WARNINGS. See [HELP SHOW WARNINGS].

For information on troubleshooting ALTER TABLE, see
http://dev.mysql.com/doc/refman/5.7/en/alter-table-problems.html.

URL: http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

