Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to understand MySQL 5.7 Online DDL Overview. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

OperationIn-Place?Rebuilds Table?Permits Concurrent DML?Only Modifies Metadata?NotesCREATE INDEX, ADD INDEXYes*No*YesNoRestrictions apply for FULLTEXT indexes; see next row.ADD FULLTEXT INDEXYes*No*NoNoAdding 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 INDEXYesNoNoNoRENAME INDEXYesNoYesYesOnly modifies table metadata.DROP INDEXYesNoYesYesOnly modifies table metadata.OPTIMIZE TABLEYes*YesYesNoIn-place operation is not supported for tables with FULLTEXT indexes.Set column default valueYesNoYesYesOnly modifies table metadata.Change auto-incrementvalueYesNoYesNo*Modifies a value stored in memory, not the data file.Add foreign key constraintYes*NoYesYesThe INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only theCOPY algorithm is supported.Drop foreign key constraintYesNoYesYesforeign_key_checks can be enabled or disabled.Rename columnYes*NoYes*YesTo 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 columnYes*Yes*Yes*NoConcurrent 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 columnYesYes*YesNoData 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 columnsYesYesYesNoData is reorganized substantially, making it an expensive operation.Change ROW_FORMATpropertyYesYesYesNoData is reorganized substantially, making it an expensive operation.Change KEY_BLOCK_SIZEpropertyYesYesYesNoData is reorganized substantially, making it an expensive operation.Make column NULLYesYes*YesNoRebuilds the table in place. Data is reorganized substantially, making it an expensive operation.Make column NOT NULLYes*YesYesNoRebuilds 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 typeNo*YesNoNoVARCHAR size may be increased using online ALTER TABLE. See Modifying Column Properties for more information.Add primary keyYes*Yes*YesNoRebuilds 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 anotherYesYesYesNoData is reorganized substantially, making it an expensive operation.Drop primary keyNoYesNoNoOnly ALGORITHM=COPY supports dropping a primary key without adding a new one in the same ALTER TABLE statement.Convert character setNoYes*NoNoRebuilds the table if the new character encoding is different.Specify character setNoYes*NoNoRebuilds the table if the new character encoding is different.Rebuild with FORCE optionYes*YesYesNoUses ALGORITHM=INPLACE. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes. "null" rebuild using ALTER TABLE. ENGINE=INNODBYes*YesYesNoUses ALGORITHM=INPLACE. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.Set STATS_PERSISTENT,STATS_AUTO_RECALC,STATS_SAMPLE_PAGESpersistent statistics optionsYesNoYesYesOnly modifies table metadata.ALTER TABLE... ENCRYPTIONNoYesNoYesDrop a STORED columnYesYes*YesNoRebuilds the table in place.Modify STORED column orderYesYes*YesNoRebuilds the table in place.Add a STORED columnYesYes*YesNoRebuilds the table in place.Drop a VIRTUAL columnYesNoYesYesModify VIRTUAL column orderYesNoYesYesAdd a VIRTUAL columnYesNoYesYes

This column shows which actions allow the use of the ALGORITHM= insert clause. "Rebuilds Table?" Column shows which actions can be performed to rebuild the table. For operations that use an in-place algorithm, the table is rebuilt in place. For operations that do not support in-place algorithms, use the table replication method to regenerate the table.

"allow concurrent DML?" Column shows the actions performed completely online. You can specify LOCK=NONE to assert that concurrent DML is allowed during DDL operations. MySQL automatically allows concurrency of DML where possible.

Concurrent queries are allowed during all online DDL operations. You can specify LOCK=SHARED to assert that concurrent queries are allowed during DDL operations. MySQL automatically allows concurrent queries where possible.

The comment column provides additional information and explains exceptions and dependencies related to the Yes / No values of other columns. An asterisk indicates an exception or dependency.

The above is the editor for you to share how to understand MySQL 5.7Online DDL Overview, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.