ddl锁表统计
官方文档：
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
虽然官方统计了大部分DDL操作时，是否允许DML，但并不是很全，或者有些支持DML，但仅仅只支持查询，不支持增删改操作。
版本：MySQL 5.7
场景一、
修改 varchar 字段长度时，在 utf8mb4 字符集下，varhcar 长度从【1-63】长度变为 【64及以上】长度时，会锁表，只能查询。反之亦然
State：copy to tmp table
场景二、int 类型个性为 decimal(10,2)，会锁表，只能查询。
State：copy to tmp table
MySQL [(none)]> show processlist; +----+------+-----------------+-------+---------+------+---------------------------------+-------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+-------+---------+------+---------------------------------+-------------------------------------------+ | 9 | root | localhost:39614 | ceshi | Sleep | 1604 | | NULL | | 12 | root | localhost:39620 | NULL | Query | 3 | Waiting for table metadata lock | select * from ceshi.t1 limit 1 for update | | 13 | root | localhost:39622 | NULL | Query | 0 | starting | show processlist | | 17 | root | localhost:39630 | ceshi | Query | 5 | copy to tmp table | alter table t1 modify age delimal(10,2) | +----+------+-----------------+-------+---------+------+---------------------------------+-------------------------------------------+
场景三、
varchar 长度 60 -> 100 ，同时新增加一个 varchar 字段。会锁表，只能查询。
MySQL [(none)]> show processlist; +----+------+-----------------+-------+---------+------+-------------------+-------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+-------+---------+------+-------------------+-------------------------------------------------------------------------+ | 9 | root | localhost:39614 | ceshi | Sleep | 1903 | | NULL | | 12 | root | localhost:39620 | NULL | Query | 0 | starting | show processlist | | 13 | root | localhost:39622 | NULL | Sleep | 299 | | NULL | | 17 | root | localhost:39630 | ceshi | Query | 6 | copy to tmp table | alter table t1 modify name varchar(100),add column describ varchar(100) | +----+------+-----------------+-------+---------+------+-------------------+-------------------------------------------------------------------------+ 4 rows in set (0.000 sec)
场景四、
varchar 修改为 text。会锁表，只能查询
MySQL [ceshi]> show processlist; +----+------+-----------------+-------+---------+------+-------------------+------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+-------+---------+------+-------------------+------------------------------------+ | 3 | root | 127.0.0.1:33426 | ceshi | Query | 3 | copy to tmp table | alter table t1 modify describ text | | 4 | root | 127.0.0.1:33428 | ceshi | Query | 0 | starting | show processlist | +----+------+-----------------+-------+---------+------+-------------------+------------------------------------+
场景五、
varchar2048 -> 2047 减小会锁表
MySQL [ceshi]> show processlist; +----+------+-----------------+-------+---------+------+-------------------+------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+-------+---------+------+-------------------+------------------------------------------+ | 3 | root | 127.0.0.1:33426 | ceshi | Query | 3 | copy to tmp table | alter table t1 modify name varchar(2047) | | 4 | root | 127.0.0.1:33428 | ceshi | Query | 0 | starting | show processlist | +----+------+-----------------+-------+---------+------+-------------------+------------------------------------------+ 2 rows in set (0.000 sec)
场景六、
timestamp(3) -> datetime(3) 时间字段转换锁表
MySQL [ceshi]> show processlist; +----+------+-----------------+-------+---------+------+-------------------+--------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+-------+---------+------+-------------------+--------------------------------------+ | 10 | root | 127.0.0.1:40562 | ceshi | Query | 2 | copy to tmp table | alter table t1 modify sj datetime(3) | | 12 | root | 127.0.0.1:40568 | ceshi | Sleep | 3997 | | NULL | | 13 | root | 127.0.0.1:40570 | ceshi | Query | 0 | starting | show processlist | +----+------+-----------------+-------+---------+------+-------------------+--------------------------------------+ 3 rows in set (0.000 sec)
场景七、
text -> mediumtext 转换字段类型锁表
MySQL [(none)]> show processlist; +----+------+-----------------+-------+---------+------+-------------------+---------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+-------+---------+------+-------------------+---------------------------------------+ | 3 | root | 127.0.0.1:60170 | ceshi | Query | 1 | copy to tmp table | alter table t1 modify name mediumtext | | 6 | root | 127.0.0.1:60176 | NULL | Query | 0 | starting | show processlist | +----+------+-----------------+-------+---------+------+-------------------+---------------------------------------+ 2 rows in set (0.000 sec)
场景八、
decimal(10,2) -> decimal(12,3) 会锁表
+----+-----------------+-----------------+---------+---------+--------+------------------------+-----------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+---------+---------+--------+------------------------+-----------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 180122 | Waiting on empty queue | NULL | | 34 | root | 127.0.0.1:63818 | archery | Sleep | 578 | | NULL | | 35 | root | 127.0.0.1:63819 | archery | Sleep | 578 | | NULL | | 46 | root | 127.0.0.1:53880 | ceshi | Query | 0 | init | show processlist | | 49 | root | 127.0.0.1:53915 | ceshi | Query | 5 | copy to tmp table | alter table t1 modify age decimal(12,3) | +----+-----------------+-----------------+---------+---------+--------+------------------------+-----------------------------------------+ 5 rows in set (0.00 sec)
场景九、
timestamp -> datetime(3) 时间字段转换锁表
mysql> show processlist; +----+-----------------+-----------------+---------+---------+--------+------------------------+---------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+---------+---------+--------+------------------------+---------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 594208 | Waiting on empty queue | NULL | | 8 | root | 127.0.0.1:49641 | archery | Sleep | 199 | | NULL | | 9 | root | 127.0.0.1:49642 | archery | Sleep | 199 | | NULL | | 50 | root | 127.0.0.1:59815 | ceshi | Query | 1 | copy to tmp table | alter table t1 modify sj timestamp(3) | | 51 | root | 127.0.0.1:60391 | NULL | Query | 0 | init | show processlist | +----+-----------------+-----------------+---------+---------+--------+------------------------+---------------------------------------+ 5 rows in set (0.00 sec)
场景十、
timestamp类型 null -> not null 会锁表
+----+-----------------+-----------------+---------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+---------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 1123680 | Waiting on empty queue | NULL | | 8 | root | 127.0.0.1:49641 | archery | Sleep | 50 | | NULL | | 9 | root | 127.0.0.1:49642 | archery | Sleep | 50 | | NULL | | 68 | root | 127.0.0.1:51378 | ceshi | Query | 0 | init | show processlist | | 69 | root | 127.0.0.1:51423 | ceshi | Query | 3 | copy to tmp table | alter table t1 modify `create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创� | +----+-----------------+-----------------+---------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec)
timestamp类型 not null -> null 不锁表
mysql> show processlist; +----+-----------------+-----------------+--------------------+---------+--------+------------------------+----------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+--------------------+---------+--------+------------------------+----------------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 710290 | Waiting on empty queue | NULL | | 12 | root | 127.0.0.1:52642 | performance_schema | Sleep | 112 | | NULL | | 13 | root | 127.0.0.1:52643 | performance_schema | Sleep | 112 | | NULL | | 57 | root | 127.0.0.1:63559 | ceshi | Query | 2 | altering table | alter table t1 modify sj timestamp(3) default null | | 59 | root | 127.0.0.1:63730 | NULL | Query | 0 | init | show processlist | +----+-----------------+-----------------+--------------------+---------+--------+------------------------+----------------------------------------------------+ 5 rows in set (0.00 sec)
场景十一、
删除主键自增属性 会锁表
mysql> show processlist; +----+-----------------+-----------------+--------------------+---------+------+------------------------+---------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+--------------------+---------+------+------------------------+---------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 5103 | Waiting on empty queue | NULL | | 11 | root | 127.0.0.1:64374 | performance_schema | Sleep | 333 | | NULL | | 12 | root | 127.0.0.1:64375 | performance_schema | Sleep | 333 | | NULL | | 13 | root | 127.0.0.1:64377 | NULL | Sleep | 4532 | | NULL | | 14 | root | 127.0.0.1:64378 | NULL | Sleep | 0 | | NULL | | 17 | root | 127.0.0.1:52488 | ceshi | Query | 3 | copy to tmp table | alter table t1 modify id int not null | | 19 | root | 127.0.0.1:52518 | NULL | Query | 0 | init | show processlist | +----+-----------------+-----------------+--------------------+---------+------+------------------------+---------------------------------------+ 7 rows in set (0.00 sec)
场景十二、
timestamp类型 DEFAULT CURRENT_TIMESTAMP 修改为 DEFAULT NULL 不锁表
create table tt(id int primary key auto_increment,name text,`update_time` timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3)); alter table tt CHANGE update_time update_time timestamp(3) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(3);
