官方文档:
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,锁表,Query,统计,ddl,table,NULL,root,localhost From: https://www.cnblogs.com/nanxiang/p/17106668.html