mysql修改varchar长度 mysql修改varchar长度会锁表

官方文档：

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)