在生产环境中，可能经常要做的且教烦的一个事情就是做DDL操作。最常见的就是增加字段、修改字段类型了。增加字段就不说了，从MySQL 5.6开始就支持Online DDL了。但改变字段类型，比如从CHAR变为VARCHAR；修改字段类型，比如增加VARCHAR大小，这些在MySQL 5.7之前还是会锁表的。从MySQL 5.7开始就支持了对于tinyint、int、smallint、bigint等数值类型的数据类型，自身位大小的增大或减小是支持ONLINE的(注意：不支持从tinyint变更为int)。还支持了VARCHAR类型的在线增大，但也有些限制。

支持了对于tinyint、int、smallint、bigint等数值类型的数据类型，自身位大小的增大或减小是支持ONLINE的(注意：不支持从tinyint变更为int)，如下：

mysql> alter table t1 change id id bigint(10),ALGORITHM=INPLACE,LOCK=NONE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 change id id bigint(30),ALGORITHM=INPLACE,LOCK=NONE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0


mysql>altertablet1changeididbigint(10),ALGORITHM=INPLACE,LOCK=NONE;
QueryOK,0rowsaffected(0.01sec)
Records:0Duplicates:0Warnings:0
mysql>altertablet1changeididbigint(30),ALGORITHM=INPLACE,LOCK=NONE;
QueryOK,0rowsaffected(0.01sec)
Records:0Duplicates:0Warnings:0

另外，如果是更改字段属性(类型不变)根据类型不同支持的Online DDL也是有限的，如把允许为NULL变更为不允许为NULL就不支持。

mysql> alter table sbtest change column dd dd varchar(100) not null,ALGORITHM=INPLACE,LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY.

mysql>altertablesbtestchangecolumnddddvarchar(100)notnull,ALGORITHM=INPLACE,LOCK=NONE;
ERROR1846(0A000):ALGORITHM=INPLACEisnotsupported.Reason:cannotsilentlyconvertNULLvalues,asrequiredinthisSQL_MODE.TryALGORITHM=COPY.

但是在线支持变更comment属性。

mysql> alter table info change dd dd varchar(100) null comment "test",ALGORITHM=INPLACE,LOCK=NONE;
mysql>altertableinfochangeddddvarchar(100)nullcomment"test",ALGORITHM=INPLACE,LOCK=NONE;

需要注意的是，在MySQL 5.6之前使用ALTER TABLE … ALGORITHM=INPLACE的表不支持包含时间列(DATE，DATETIME，TIMESTAMP)，不然会报错。

MySQL 5.7版本支持重命名索引和修改varchar的大小(增大不能减小)，且无需table-copy(秒级增加varchar大小)。这两项操作在之前的版本中，都需要重建索引或表，适用于各引擎。

# change its data type;
mysql> alter table sbtest ALGORITHM=INPLACE,change column dt td varchar(100);
# change its data type;
mysql>altertablesbtestALGORITHM=INPLACE,changecolumndttdvarchar(100);

但存在限制，即只支持0~255字节内的或者255以上字节间的增加，也就是说若从254增到256时不能使用INPLACE算法(增加到255可以)，必须使用COPY算法，否侧报错。这个原理就是varchar会在头部存储一个长度，如果小于255就是一个BYTES字节，8位；如果大于255当然就需要两个字节 了。头部都变了，自然要重新copy table了。另外使用INPLACE算法缩小VARCHAR的ALTER TABLE也是不支持的，必须用COPY算法。

mysql> alter table sbtest add column td varchar(10);
Query OK, 0 rows affected (10.81 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table sbtest ALGORITHM=INPLACE,change column td td varchar(254);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table sbtest ALGORITHM=INPLACE,change column td td varchar(256);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.


mysql>altertablesbtestaddcolumntdvarchar(10);
QueryOK,0rowsaffected(10.81sec)
Records:0Duplicates:0Warnings:0
mysql>altertablesbtestALGORITHM=INPLACE,changecolumntdtdvarchar(254);
QueryOK,0rowsaffected(0.01sec)
Records:0Duplicates:0Warnings:0
mysql>altertablesbtestALGORITHM=INPLACE,changecolumntdtdvarchar(256);
ERROR1846(0A000):ALGORITHM=INPLACEisnotsupported.Reason:CannotchangecolumntypeINPLACE.TryALGORITHM=COPY.

最后说一下Online DDL操作根据数据量的不同的耗时时间，我在生产环境做过两千五百万数据的ALTER操作，耗时12分钟31秒，我的硬件配置是32核32G。按照这个时间可以根据硬件不同大概估算出数据量不同时的耗时时间。比如100万数据估计耗时：1000000 / (25000000 / (12*60+30)) = 30秒。

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。