备注:
MySQL版本 5.7

Table of Contents

• 一. 问题描述
• 二. 解决方案
• 2.1 修改sql_mode
• 2.2 修改SQL语句

一. 问题描述

今天帮朋友写了一个存储过程，通过主键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; 
复制