• Index Operations

• Primary Key Operations

• Column Operations

• Generated Column Operations

• Foreign Key Operations

• Table Operations

• Tablespace Operations

• Partitioning Operations

CREATE INDEX name ON table (col_list);

ALTER TABLE tbl_name ADD INDEX name (col_list);

The table remains available for read and write operations while the index is being created. The CREATE INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table. Online DDL support for adding secondary indexes means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded. A newly created secondary index contains only the committed data in the table at the time the CREATE INDEX or ALTER TABLE statement finishes executing. It does not contain any uncommitted values, old versions of values, or values marked for deletion but not yet removed from the old index. Some factors affect the performance, space usage, and semantics of this operation. For details, see Section 15.12.8, “Online DDL Limitations” . Dropping an index

DROP INDEX name ON table;

ALTER TABLE tbl_name DROP INDEX name;

The table remains available for read and write operations while the index is being dropped. The DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table. Renaming an index

ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;

CREATE FULLTEXT INDEX name ON table(column);

CREATE TABLE geom (g GEOMETRY NOT NULL);
ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;

ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INSTANT;

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation. ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted to NOT NULL . Restructuring the clustered index always requires copying of table data. Thus, it is best to define the primary key when you create a table, rather than issuing ALTER TABLE ... ADD PRIMARY KEY later. When you create a UNIQUE or PRIMARY KEY index, MySQL must do some extra work. For UNIQUE indexes, MySQL checks that the table contains no duplicate values for the key. For a PRIMARY KEY index, MySQL also checks that none of the PRIMARY KEY columns contains a NULL . When you add a primary key using the ALGORITHM=COPY clause, MySQL converts NULL values in the associated columns to default values: 0 for numbers, an empty string for character-based columns and BLOBs, and 0000-00-00 00:00:00 for DATETIME . This is a non-standard behavior that Oracle recommends you not rely on. Adding a primary key using ALGORITHM=INPLACE is only permitted when the SQL_MODE setting includes the strict_trans_tables or strict_all_tables flags; when the SQL_MODE setting is strict, ALGORITHM=INPLACE is permitted, but the statement can still fail if the requested primary key columns contain NULL values. The ALGORITHM=INPLACE behavior is more standard-compliant. If you create a table without a primary key, InnoDB chooses one for you, which can be the first UNIQUE key defined on NOT NULL columns, or a system-generated key. To avoid uncertainty and the potential space requirement for an extra hidden column, specify the PRIMARY KEY clause as part of the CREATE TABLE statement. MySQL creates a new clustered index by copying the existing data from the original table to a temporary table that has the desired index structure. Once the data is completely copied to the temporary table, the original table is renamed with a different temporary table name. The temporary table comprising the new clustered index is renamed with the name of the original table, and the original table is dropped from the database. The online performance enhancements that apply to operations on secondary indexes do not apply to the primary key index. The rows of an InnoDB table are stored in a clustered index organized based on the primary key , forming what some database systems call an “ index-organized table ” . Because the table structure is closely tied to the primary key, redefining the primary key still requires copying the data. When an operation on the primary key uses ALGORITHM=INPLACE , even though the data is still copied, it is more efficient than using ALGORITHM=COPY because: No undo logging or associated redo logging is required for ALGORITHM=INPLACE . These operations add overhead to DDL statements that use ALGORITHM=COPY . The secondary index entries are pre-sorted, and so can be loaded in order. The change buffer is not used, because there are no random-access inserts into the secondary indexes. Dropping a primary key

ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;

Only ALGORITHM=COPY supports dropping a primary key without adding a new one in the same ALTER TABLE statement. Dropping a primary key and adding another

ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

Data is reorganized substantially, making it an expensive operation. The following table provides an overview of online DDL support for column operations. An asterisk indicates additional information, an exception, or a dependency. For details, see Syntax and Usage Notes .

Table 15.18 Online DDL Support for Column Operations

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INSTANT;

INSTANT is the default algorithm as of MySQL 8.0.12, and INPLACE before that. The following limitations apply when the INSTANT algorithm adds a column: A statement cannot combine the addition of a column with other ALTER TABLE actions that do not support the INSTANT algorithm. The INSTANT algorithm can add a column at any position in the table. Before MySQL 8.0.29, the INSTANT algorithm could only add a column as the last column of the table. Columns cannot be added to tables that use ROW_FORMAT=COMPRESSED , tables with a FULLTEXT index, tables that reside in the data dictionary tablespace, or temporary tables. Temporary tables only support ALGORITHM=COPY . MySQL checks the row size when the INSTANT algorithm adds a column, and throws the following error if the addition exceeds the limit. ERROR 4092 (HY000): Column can't be added with ALGORITHM=INSTANT as after this max possible row size crosses max permissible row size. Try ALGORITHM=INPLACE/COPY. Before MySQL 8.0.29, MySQL does not check the row size when the INSTANT algorithm adds a column. However, MySQL does check the row size during DML operations that insert and update rows in the table. The maximum number of columns in the internal representation of the table cannot exceed 1022 after column addition with the INSTANT algorithm. The error message is: ERROR 4158 (HY000): Column can't be added to tbl_name with ALGORITHM=INSTANT anymore. Please try ALGORITHM=INPLACE/COPY Multiple columns may be added in the same ALTER TABLE statement. For example:

ALTER TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT, ALGORITHM=INSTANT;

A new row version is created after each ALTER TABLE ... ALGORITHM=INSTANT operation that adds one or more columns, drops one or more columns, or adds and drops one or more columns in the same operation. The INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS column tracks the number of row versions for a table. The value is incremented each time a column is instantly added or dropped. The initial value is 0.

mysql>  SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES 
        WHERE NAME LIKE 'test/t1';
+---------+--------------------+
| NAME    | TOTAL_ROW_VERSIONS |
+---------+--------------------+
| test/t1 |                  0 |
+---------+--------------------+

When a table with instantly added or dropped columns is rebuilt by table-rebuilding ALTER TABLE or OPTIMIZE TABLE operation, the TOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 64, as each row version requires additional space for table metadata. When the row version limit is reached, ADD COLUMN and DROP COLUMN operations using ALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using the COPY or INPLACE algorithm. ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE. The following INFORMATION_SCHEMA columns provide additional metadata for instantly added columns. Refer to the descriptions of those columns for more information. See Section 26.4.9, “The INFORMATION_SCHEMA INNODB_COLUMNS Table” , Section 26.4.23, “The INFORMATION_SCHEMA INNODB_TABLES Table” . INNODB_COLUMNS.DEFAULT_VALUE INNODB_COLUMNS.HAS_DEFAULT INNODB_TABLES.INSTANT_COLS Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation. At a minimum, ALGORITHM=INPLACE, LOCK=SHARED is required. The table is rebuilt if ALGORITHM=INPLACE is used to add a column. Dropping a column

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INSTANT;

INSTANT is the default algorithm as of MySQL 8.0.29, and INPLACE before that. The following limitations apply when the INSTANT algorithm is used to drop a column: Dropping a column cannot be combined in the same statement with other ALTER TABLE actions that do not support ALGORITHM=INSTANT . Columns cannot be dropped from tables that use ROW_FORMAT=COMPRESSED , tables with a FULLTEXT index, tables that reside in the data dictionary tablespace, or temporary tables. Temporary tables only support ALGORITHM=COPY . Multiple columns may be dropped in the same ALTER TABLE statement; for example:

ALTER TABLE t1 DROP COLUMN c4, DROP COLUMN c5, ALGORITHM=INSTANT;

Each time a column is added or dropped using ALGORITHM=INSTANT , a new row version is created. The INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS column tracks the number of row versions for a table. The value is incremented each time a column is instantly added or dropped. The initial value is 0.

mysql>  SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES 
        WHERE NAME LIKE 'test/t1';
+---------+--------------------+
| NAME    | TOTAL_ROW_VERSIONS |
+---------+--------------------+
| test/t1 |                  0 |
+---------+--------------------+

When a table with instantly added or dropped columns is rebuilt by table-rebuilding ALTER TABLE or OPTIMIZE TABLE operation, the TOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 64, as each row version requires additional space for table metadata. When the row version limit is reached, ADD COLUMN and DROP COLUMN operations using ALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using the COPY or INPLACE algorithm. ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE. If an algorithm other than ALGORITHM=INSTANT is used, data is reorganized substantially, making it an expensive operation. Renaming a column

ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INSTANT, LOCK=NONE;

ALGORITHM=INSTANT support for renaming a column was added in MySQL 8.0.28. Earlier MySQL Server releases support only ALGORITHM=INPLACE and ALGORITHM=COPY when renaming a column. To permit concurrent DML, keep the same data type and only change the column name. When you keep the same data type and [NOT] NULL attribute, only changing the column name, the operation can always be performed online. Renaming a column referenced from another table is only permitted with ALGORITHM=INPLACE . If you use ALGORITHM=INSTANT , ALGORITHM=COPY , or some other condition that causes the operation to use those algorithms, the ALTER TABLE statement fails. ALGORITHM=INSTANT supports renaming a virtual column; ALGORITHM=INPLACE does ALGORITHM=INSTANT and ALGORITHM=INPLACE do not support renaming a column when adding or dropping a virtual column in the same statement. In this case, only ALGORITHM=COPY is supported. Reordering columns To reorder columns, use FIRST or AFTER in CHANGE or MODIFY operations.

ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

Data is reorganized substantially, making it an expensive operation. Changing the column data type

ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

Changing the column data type is only supported with ALGORITHM=COPY . Extending VARCHAR column size

ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

The number of length bytes required by a VARCHAR column must remain the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy ( ALGORITHM=COPY ). For example, attempting to change VARCHAR column size for a single byte character set from VARCHAR(255) to VARCHAR(256) using in-place ALTER TABLE returns this error:

ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
column type INPLACE. Try ALGORITHM=COPY.

ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INSTANT;

Only modifies table metadata. Default column values are stored in the data dictionary . Dropping a column default value

ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INSTANT;

ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));
ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INSTANT;

ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) STORED), ALGORITHM=COPY;

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED FIRST, ALGORITHM=COPY;

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL), ALGORITHM=INSTANT;

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL FIRST, ALGORITHM=COPY;

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;

ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1)
  REFERENCES tbl2(col2) referential_actions;

ALTER TABLE tbl DROP FOREIGN KEY fk_name;

SHOW CREATE TABLE table\G

ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;

ALTER TABLE tbl_name ROW_FORMAT = row_format, ALGORITHM=INPLACE, LOCK=NONE;

Data is reorganized substantially, making it an expensive operation. For additional information about the ROW_FORMAT option, see Table Options . Changing the KEY_BLOCK_SIZE

ALTER TABLE tbl_name KEY_BLOCK_SIZE = value, ALGORITHM=INPLACE, LOCK=NONE;

Data is reorganized substantially, making it an expensive operation. For additional information about the KEY_BLOCK_SIZE option, see Table Options . Setting persistent table statistics options

ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;

Only modifies table metadata. Persistent statistics include STATS_PERSISTENT , STATS_AUTO_RECALC , and STATS_SAMPLE_PAGES . For more information, see Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters” . Specifying a character set

ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE, LOCK=NONE;

Rebuilds the table if the new character encoding is different. Converting a character set

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name, ALGORITHM=COPY;

Rebuilds the table if the new character encoding is different. Optimizing a table

OPTIMIZE TABLE tbl_name;

In-place operation is not supported for tables with FULLTEXT indexes. The operation uses the INPLACE algorithm, but ALGORITHM and LOCK syntax is not permitted. Rebuilding a table with the FORCE option

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

Uses ALGORITHM=INPLACE as of MySQL 5.6.17 . ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes. Performing a "null" rebuild

ALTER TABLE tbl_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes. Renaming a table

ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INSTANT;

Renaming a table can be performed instantly or in place. MySQL renames files that correspond to the table tbl_name without making a copy. (You can also use the RENAME TABLE statement to rename tables. See Section 13.1.36, “RENAME TABLE Statement” .) Privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually. The following table provides an overview of online DDL support for tablespace operations. For details, see Syntax and Usage Notes .

Table 15.22 Online DDL Support for Tablespace Operations

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;

ALTER TABLESPACE tablespace_name ENCRYPTION='Y';

ALTER TABLE tbl_name ENCRYPTION='Y', ALGORITHM=COPY;