MySQL 5.6 原生Online DDL解析(7)

看到，默认不加 ALGORITHM=INPLACE 就已经允许ddl期间并发DML操作。但是会有一个小临时文件产生：

1
2   -rw-rw---- 1 mysql mysql 8.6K May 23 21:42 #sql-7055_5.frm
-rw-rw---- 1 mysql mysql 112K May 23 21:42 #sql-ib21-16847116.ibd  

当指定copy时，就会锁表了（一般你不想这样做）：

1
2   ALTER TABLE `sbtest2`
4DROIP COLUMN `f_new_col1`, algorithm=copy;  
3.3 修改字段类型

修改列类型与添加新列不一样，修改类型需要rebuild整个表：
(select ok, update waiting)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26   **SESSION1**
mysql> ALTER TABLE sbtest2
4 CHANGE f_new_col2 f_new_col2 varchar(50) NULL DEFAULT '', algorithm=inplace ;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
不支持INPLACE

mysql> ALTER TABLE sbtest2
4 CHANGE f_new_col2 f_new_col2 varchar(50) NULL DEFAULT '';

**SESSION2**
mysql> update sbtest2 set c="update when add colomun ddl start" where c='33333';

mysql> select * from sbtest2 where c='select when add colomun ddl start';
Empty set (3.79 sec)

mysql> show processlist;
+-----+-----------------+-----------+------------+---------+------+---------------------------------+----------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------+------------+---------+------+---------------------------------+----------------------------------------------------------------------------------+
| 5 | root | localhost | confluence | Query | 5 | copy to tmp table | ALTER TABLE sbtest2
CHANGE f_new_col2 f_new_col2 varchar(50) NULL DEFAULT '' |
| 7 | root | localhost | NULL | Query | 0 | init | show processlist |
| 161 | root | localhost | confluence | Query | 4 | Waiting for table metadata lock | update sbtest2 set c="update when add colomun ddl start" where c='33333' |
| 187 | root | localhost | confluence | Query | 3 | Sending data | select * from sbtest2 where c='select when add colomun ddl start' |
+-----+-----------------+-----------+------------+---------+------+---------------------------------+----------------------------------------------------------------------------------+
5 rows in set (0.00 sec)  
3.4 Waiting for table metadata lock