no way to alter column comment only?

最新推荐文章于 2023-10-08 18:48:04 发布

qq_23204557

最新推荐文章于 2023-10-08 18:48:04 发布

阅读量515

点赞数

分类专栏：数据库文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_23204557/article/details/122329076

版权

数据库专栏收录该内容

44 篇文章

订阅专栏

在尝试更改大量数据表中某列的注释时，遇到MySQL只能通过重定义列定义来改变comment的情况，这会导致Lockwaittimeoutexceeded错误。解决方法是完整地指定新的列定义，包括所有属性。尽管这不是一个直接的解决方案，但这是MySQL的现行规定，需要在操作时注意避免长时间锁定。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

想要改一个数量量很大的表的某个字段的comment, 结果发现似乎没有办法只改comment，而必须把column definition 重新定义一遍：

stackOverFlow : Mysql alter comment column only

MySQL specification :

For column definition changes using CHANGE or MODIFY, the definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT ‘my column’ and you modify the column as follows, intending to change only INT to BIGINT:

ALTER TABLE t1 MODIFY col1 BIGINT;

That statement changes the data type from INT to BIGINT, but it also drops the UNSIGNED, DEFAULT, and COMMENT attributes. To retain them, the statement must include them explicitly:

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

结果等于把整个表都刷了一遍，导致Lock wait timeout exceeded。目前没有找到很好的办法来避免这种情况