mysql ddl 锁_MySQL数据库之MySQL5.6 Online DDL 是否锁表、rebuild表、inplace的说明

本文主要向大家介绍了MySQL数据库之MySQL5.6 Online DDL 是否锁表、rebuild表、inplace的说明 ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

Operation

In-Place?

Rebuilds Table?

Permits Concurrent DML?

Only Modifies Metadata?

Notes

CREATE INDEX, ADD INDEX    Yes*    No*    Yes    No    Restrictions apply for FULLTEXT indexes; see next row.

ADD FULLTEXT INDEX    Yes*    No*    No    No    Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Subsequent FULLTEXT indexes may be added on the same table without rebuilding the table.

DROP INDEX    Yes    No    Yes    Yes    Only modifies table metadata.

OPTIMIZE TABLE    Yes*    Yes    Yes    No    Performed in-place as of MySQL 5.6.17. In-place operation is not supported for tables withFULLTEXT indexes.

Set column default value    Yes    No    Yes    Yes    Only modifies table metadata.

Change auto-incrementvalue    Yes    No    Yes    No*    Modifies a value stored in memory, not the data file.

Add foreign key constraint    Yes*    No    Yes    Yes    The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only theCOPY algorithm is supported.

Drop foreign key constraint    Yes    No    Yes    Yes    foreign_key_checks can be enabled or disabled.

Rename column    Yes    No    Yes*    Yes    To permit concurrent DML, keep the same data type and only change the column name.

Add column    Yes    Yes    Yes*    No    Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation.

Drop column    Yes    Yes    Yes    No    Data is reorganized substantially, making it an expensive operation.

Reorder columns    Yes    Yes    Yes    No    Data is reorganized substantially, making it an expensive operation.

Change ROW_FORMATproperty    Yes    Yes    Yes    No    Data is reorganized substantially, making it an expensive operation.

Change KEY_BLOCK_SIZEproperty    Yes    Yes    Yes    No    Data is reorganized substantially, making it an expensive operation.

Make column NULL    Yes    Yes*    Yes    No    Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.

Make column NOT NULL    Yes*    Yes*    Yes    No    Rebuilds the table in place. STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. As of 5.6.7, the server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. See Section 13.1.7, “ALTER TABLE Syntax”. Data is reorganized substantially, making it an expensive operation.

Change column data type    No    Yes    No    No    Only supports ALGORITHM=COPY

Add primary key    Yes*    Yes*    Yes    No    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 toNOT NULL.

Drop primary key and add another    Yes    Yes    Yes    No    Data is reorganized substantially, making it an expensive operation.

Drop primary key    No    Yes    No    No    Only ALGORITHM=COPY supports dropping a primary key without adding a new one in the sameALTER TABLE statement.

Convert character set    No    Yes*    No    No    Rebuilds the table if the new character encoding is different.

Specify character set    No    Yes*    No    No    Rebuilds the table if the new character encoding is different.

Rebuild with FORCEoption    Yes*    Yes    Yes    No    Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables withFULLTEXT indexes.

“null” rebuild usingALTER TABLE ... ENGINE=INNODB    Yes*    Yes    Yes    No    Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables withFULLTEXT indexes.

Set STATS_PERSISTENT,STATS_AUTO_RECALC,STATS_SAMPLE_PAGESpersistent statisticsoptions    Yes    No    Yes    Yes    Only modifie

本文由职坐标整理并发布，希望对同学们学习MySQL有所帮助，更多内容请关注职坐标数据库MySQL数据库频道！