移除SET类型字段选项时触发数据截断错误，如何排查修复？

尝试从SET类型字段status中移除部分选项，已确认数据库中无行使用这些待移除选项，但执行以下ALTER语句时仍触发数据截断错误：

ALTER TABLE myTable
CHANGE `status` `status` set('Init','TimedOut','Processing','Unknown','Validate','Duplicate','Remove');

报错信息：

Error in query (1265): Data truncated for column 'status' at row 121393

查询报错的121393行，发现status字段显示为空：

SELECT status FROM myTable WHERE id=121393;

查询结果：

+--------+
| status |
+--------+
|        |
+--------+

补充说明：status字段初始SET类型定义包含两个额外选项，顺序如下：

set('Init','TimedOut','Processing','Unused','Unknown','Validate','Duplicate','Remove','Unused2')

问题根源 #

MySQL的SET类型采用按位存储逻辑，每个选项对应一个二进制位（从第0位开始分配）：

• Init对应2⁰=1，TimedOut对应2¹=2，Processing对应2²=4
• 原定义中的Unused对应2³=8，Unused2对应2⁸=256

当修改SET定义时，MySQL会校验每行的底层存储数值是否能匹配新选项的位组合。如果某行存储的数值包含已移除选项对应的位，即便查询时显示为空（因为新定义中没有对应选项），ALTER操作仍会判定该值无效，触发数据截断错误。

比如，若报错行的status存储值为8（对应原定义的Unused），在新SET定义中无匹配选项，查询显示为空，但ALTER时会检测到这个数值不合法，从而报错。

修复方案 #

方案1：提前清理无效数值 #

先将所有包含无效位的行（包括显示为空但底层有数值的行）修正为合法值或空：

-- 清理所有包含已移除选项或不在新SET范围内的行
UPDATE myTable 
SET `status` = '' 
WHERE FIND_IN_SET('Unused', `status`) 
   OR FIND_IN_SET('Unused2', `status`) 
   OR (`status` != '' AND `status` NOT IN ('Init','TimedOut','Processing','Unknown','Validate','Duplicate','Remove'));

或者精准定位显示为空但底层有数值的行：

UPDATE myTable 
SET `status` = '' 
WHERE `status` = '' AND CAST(`status` AS UNSIGNED) != 0;

执行完更新后，重新运行ALTER语句即可。

方案2：临时关闭严格模式执行修改 #

若无法提前更新数据，可临时关闭SQL严格模式，让MySQL自动将无效数值转为空，完成修改后再恢复严格模式：

-- 临时关闭严格模式
SET sql_mode = '';
-- 修改SET字段定义
ALTER TABLE myTable
MODIFY `status` set('Init','TimedOut','Processing','Unknown','Validate','Duplicate','Remove');
-- 恢复原有严格模式（替换为你的实际配置）
SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

注意：此方式需确认无效数值转为空不会影响业务逻辑。

方案3：简化ALTER语句（语法优化） #

无需使用CHANGE（用于字段重命名或同时修改名称+类型），直接用MODIFY更简洁，效果一致：

ALTER TABLE myTable
MODIFY `status` set('Init','TimedOut','Processing','Unknown','Validate','Duplicate','Remove');

此方案仅优化语法，核心仍需先处理无效数值或关闭严格模式。

内容的提问来源于stack exchange，提问作者Reggie