Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.2Mb
PDF (A4) - 37.2Mb
PDF (RPM) - 36.6Mb
HTML Download (TGZ) - 10.1Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.8Mb
Man Pages (TGZ) - 206.0Kb
Man Pages (Zip) - 313.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Online DDL SQL Syntax

15.13.3 Online DDL SQL Syntax

Typically, you do not need to do anything special to enable online DDL when using the ALTER TABLE statement for InnoDB tables. See Table 15.10, “Online Status for DDL Operations” for the kinds of DDL operations that can be performed in-place, allowing concurrent DML, or both. Some variations require particular combinations of configuration settings or ALTER TABLE clauses.

You can control the various aspects of a particular online DDL operation by using the LOCK and ALGORITHM clauses of the ALTER TABLE statement. These clauses come at the end of the statement, separated from the table and column specifications by commas. The LOCK clause is useful for fine-tuning the degree of concurrent access to the table. The ALGORITHM clause is primarily intended for performance comparisons and as a fallback to the older table-copying behavior in case you encounter any issues with existing DDL code. For example:

  • To avoid accidentally making the table unavailable for reads, writes, or both, specify a clause on the ALTER TABLE statement such as LOCK=NONE (allow both reads and writes) or LOCK=SHARED (allow reads). The operation halts immediately if the requested level of concurrency is not available.

  • To compare performance, run one statement with ALGORITHM=INPLACE and another with ALGORITHM=COPY, as an alternative to setting the old_alter_table configuration option.

  • To avoid tying up the server with an ALTER TABLE operation that copies the table, include ALGORITHM=INPLACE. The statement halts immediately if it cannot use the in-place mechanism. See Table 15.10, “Online Status for DDL Operations” for a list of the DDL operations that can or cannot be performed in-place.

See Section 15.13.2, “Online DDL Performance and Concurrency” for more details about the LOCK clause.


User Comments
Sign Up Login You must be logged in to post a comment.