备注:
MySQL版本 5.7
一. 问题描述
今天帮朋友写了一个存储过程，通过主键id分批更新一个大表，里面的update及delete语句大概有30多个，开启事务，一个批次更新完成后，再提交。
突然存储过程运行的时候出现如下报错:
2021-12-18T22:24:05.171910-05:00 22592 [ERROR] Event Scheduler: [root@localhost][**.****] Incorrect DECIMAL value: '0' for column '' at row -1
2021-12-18T22:24:05.171924-05:00 22592 [ERROR] Event Scheduler: [root@localhost][**.****] Truncated incorrect DECIMAL value: 'N'
2021-12-18T22:24:05.171930-05:00 22592 [ERROR] Event Scheduler: [root@localhost][**.****] Incorrect DECIMAL value: '0' for column '' at row -1
2021-12-18T22:24:05.171936-05:00 22592 [ERROR] Event Scheduler: [root@localhost][**.****] Truncated incorrect DECIMAL value: 'N'
2021-12-18T22:24:05.171940-05:00 22592 [ERROR] Event Scheduler: [root@localhost][**.****] Incorrect DECIMAL value: '0' for column '' at row -1
2021-12-18T22:24:05.171945-05:00 22592 [ERROR] Event Scheduler: [root@localhost][**.****] Truncated incorrect DECIMAL value: 'N'
二. 解决方案
2.1 修改sql_mode
将配置文件sql_mode修改如下:
sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
然后重启msyql服务，重试，问题依旧。
2.2 修改SQL语句
这个问题最坑之处在于，没有报具体是哪个sql语句的问题，给出的是一个模糊的报错。
首先我们查看表结果，发现没有decimal类型的字段，字段都是int或varchar类型，这就尴尬了。
后面全部复盘了一次修改的update语句，发现有个地方使用到rand()来生成随机数，虽然后floor函数处理成了整数。
UPDATE tab_name SET MM=FLOOR( 1 + RAND() * (12 - 1) WHERE MM is NULL and data_id between l_beg and l_end;
UPDATE tab_name SET MM=concat('0',mm) WHERE LENGTH(mm)=1 and data_id between l_beg and l_end;
UPDATE tab_name SET dd=FLOOR( 1 + RAND() * (28 - 1) WHERE dd is NULL and data_id between l_beg and l_end;
UPDATE tab_name SET dd=concat('0',dd) WHERE LENGTH(dd)=1 and data_id between l_beg and l_end;
MM 和 DD 两列都是varchar类型， 这个业务的逻辑是，如果 MM和DD是空值的话，就刷成符合规范的随机数。
将rand() 生成的数值类型通过case函数转变为字符类型后，问题解决。
UPDATE tab_name SET MM=cast(FLOOR( 1 + RAND() * (12 - 1)) as char) WHERE MM is NULL and data_id between l_beg and l_end;
UPDATE tab_name SET MM=concat('0',mm) WHERE LENGTH(mm)=1 and data_id between l_beg and l_end;
UPDATE tab_name SET dd=cast(FLOOR( 1 + RAND() * (28 - 1)) as char) WHERE dd is NULL and data_id between l_beg and l_end;
UPDATE tab_name SET dd=concat('0',dd) WHERE LENGTH(dd)=1 and data_id between l_beg and l_end;
网友评论