【待整理】MySQL alter table modify vs alter table add产生state不一样

 

MySQL：5.6.35

OS：redhat5.8

 

今天更新数据库某些表字段，有如下两SQL：

①alter table xx modify xxxx;（表大概是77w）

②alter table sb add sbsb;（表大概是95w）

 

奇怪的是①产生的state为：copy to tmp table

②产生的state为：altering table

两表同样是百万级别的；后者执行虽慢，但是消耗时间不到1200s，而后者跑了2800s。

开始怀疑alter中的add 和modify 两个操作的实现过程有所差异；

 

官方说法：

MODIFY is an extension to ALTER TABLE for Oracle compatibility.

When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the column as follows:

ALTER TABLE t1 MODIFY col1 BIGINT;
The resulting column will be defined as BIGINT, but will not include the attributes UNSIGNED DEFAULT 1 COMMENT 'my column'. To retain them, the statement should be:

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';
When you change a data type using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible.

Warning
This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE (see Section 5.1.8, “Server SQL Modes”).

To add a column at a specific position within a table row, use FIRST or AFTER col_name. The default is to add the column last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.

If you use CHANGE or MODIFY to shorten a column for which an index exists on the column, and the resulting column length is less than the index length, MySQL shortens the index automatically.

CHANGE col_name is a MySQL extension to standard SQL.

ALTER ... SET DEFAULT or ALTER ... DROP DEFAULT specify a new default value for a column or remove the old default value, respectively. If the old default is removed and the column can be NULL, the new default is NULL. If the column cannot be NULL, MySQL assigns a default value as described in Section 11.6, “Data Type Default Values”.

　　

 

alter table add ，state状态由  waiting for table metadata lock （持续10s左右）然后到 altering table，最终完成。

 

转载于:https://www.cnblogs.com/joewan/p/6907344.html