Table Options

Table options are used to optimize the behavior of a table.

CREATE [...] tbl_name (create_definition, ...) [table_options]

ALTER [...] TABLE tbl_name [alter_specification { "," alter_specification }]

alter_specification:
    table_options
  | alter_table_options
  | ADD [...]

table_options:
    table_option { "," table_option }

table_option:
    [[<a href="#min_pages">MIN_PAGES</a]] [=] value
  | [...]

alter_table_options:
    alter_table_option { "," alter_table_option }

alter_table_option:
    [[<a href="#lock">LOCK</a]] [=] EXCLUSIVE
  | [[<a href="#no_wait">NO_WAIT</a]]
  | [...]

The table_option rule signifies a table option of the kind that can be used in a CREATE TABLE or ALTER TABLE statement, while the alter_table_option rule signifies options that can only be used in an ALTER TABLE statement.

NO_WAIT

If NO_WAIT is set, conflicting locks requests that would normally block (while the table is being altered) are aborted instead. An aborted lock request results in the following error being returned to the client:

ERROR 1689 (ER_LOCK_ABORTED): Wait on a lock was aborted due to a conflicting lock

MIN_PAGES

In the case of a CREATE TABLE statement, MIN_PAGES specifies the initial size, in pages, of InnoDB per-table tablespaces. In the case of an ALTER TABLE statement, MIN_PAGES extends the tablespace to the desired size. If the tablespace is large enough already, nothing changes. In order to retain forward compatibility in FRM files, MIN_PAGES is implemented as an alias for MIN_ROWS. Using MIN_ROWS has the same effect as MIN_PAGES. If the MIN_PAGES option is used in a CREATE TABLE statement that is logged to the binary log, the MIN_PAGES option is also used in the statement that is sent to slaves. The option is only meaningful if innodb_file_per_table is enabled.

LOCK

The LOCK clause can be used to enforce a specific lock mode for an alter table operation. The only supported lock mode parameter is EXCLUSIVE, which will block any attempts to read or write to the table while the operation is in progress.