导航：首页 > 数据库 >

怎么理解MySQL 5.7 Online DDL Overview

发表于：2024-11-26 作者：千家信息网编辑

千家信息网最后更新 2024年11月26日，这期内容当中小编将会给大家带来有关怎么理解MySQL 5.7 Online DDL Overview，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。Operation

千家信息网最后更新 2024年11月26日怎么理解MySQL 5.7 Online DDL Overview

这期内容当中小编将会给大家带来有关怎么理解MySQL 5.7 Online DDL Overview，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

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.
ADD SPATIAL INDEX	Yes	No	No	No
RENAME INDEX	Yes	No	Yes	Yes	Only modifies table metadata.
DROP INDEX	Yes	No	Yes	Yes	Only modifies table metadata.
OPTIMIZE TABLE	Yes*	Yes	Yes	No	In-place operation is not supported for tables with FULLTEXT 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.ALGORITHM=INPLACE is not supported for renaming a generated column.
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. ALGORITHM=INPLACE is supported for adding a virtual generated column but not for adding a stored generated column. Adding a virtual generated column does not require a table rebuild.
Drop column	Yes	Yes*	Yes	No	Data is reorganized substantially, making it an expensive operation. ALGORITHM=INPLACE is supported for dropping a generated column. Dropping a virtual generated column does not require a table rebuild.
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. The server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. SeeSection 13.1.8, "ALTER TABLE Syntax". Data is reorganized substantially, making it an expensive operation.
Change column data type	No*	Yes	No	No	VARCHAR size may be increased using online ALTER TABLE. See Modifying Column Properties for more information.
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 same ALTER 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 FORCE option	Yes*	Yes	Yes	No	Uses ALGORITHM=INPLACE. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.
"null" rebuild using ALTER TABLE ... ENGINE=INNODB	Yes*	Yes	Yes	No	Uses ALGORITHM=INPLACE. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.
Set STATS_PERSISTENT,STATS_AUTO_RECALC,STATS_SAMPLE_PAGESpersistent statistics options	Yes	No	Yes	Yes	Only modifies table metadata.
ALTER TABLE … ENCRYPTION	No	Yes	No	Yes
Drop a STORED column	Yes	Yes*	Yes	No	Rebuilds the table in place.
Modify STORED column order	Yes	Yes*	Yes	No	Rebuilds the table in place.
Add a STORED column	Yes	Yes*	Yes	No	Rebuilds the table in place.
Drop a VIRTUAL column	Yes	No	Yes	Yes
Modify VIRTUAL column order	Yes	No	Yes	Yes
Add a VIRTUAL column	Yes	No	Yes	Yes