怎么理解MySQL5.7OnlineDDLOverview

这期内容当中小编将会给大家带来有关怎么理解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  

此列显示哪些操作允许使用ALGORITHM=INPLACE子句。“Rebuilds Table?”列显示哪些操作可以重建表。对于使用就地算法的操作,表将就地重建。对于不支持就地算法的操作,使用表复制方法重新生成表。

“允许并发DML?”列显示完全在线执行的操作。您可以指定LOCK=NONE来断言在DDL操作期间允许并发DML。MySQL在可能的情况下自动允许并发DML。

在所有联机DDL操作期间允许并发查询。您可以指定LOCK=SHARED来断言在DDL操作期间允许并发查询。MySQL在可能的情况下自动允许并发查询。

“注释”列提供附加信息,并解释与其他列的“是/否”值相关的异常和依赖关系。星号表示异常或依赖项。

上述就是小编为大家分享的怎么理解MySQL 5.7 Online DDL Overview了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注创新互联行业资讯频道。


新闻名称:怎么理解MySQL5.7OnlineDDLOverview
网站URL:http://pwwzsj.com/article/gcsioo.html