项目场景:
后台代码中触发调用存储过程。其中有个update语句未执行成功。
问题描述
传入参数后,过程中的update语句未成功执行
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN IN_ID VARCHAR ( 50 ), #数据日期
IN IN_INFOID VARCHAR ( 50 ), #数据日期
IN IN_DATE VARCHAR ( 10 ), #数据日期
OUT V_RESLUT VARCHAR ( 10 ))
COMMENT 'test'
BEGIN
#定义返回结果并赋初值0
DECLARE RESLUT_CODE INTEGER DEFAULT 0;
DECLARE result_code VARCHAR (8);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN GET DIAGNOSTICS CONDITION 1 result_code = RETURNED_SQLSTATE; SET RESLUT_CODE=2; END;
SET V_RESLUT = '0';#设置返回值为0
*****************逻辑语句*****************************
#执行异常时
IF
RESLUT_CODE = 2 THEN
SET V_RESLUT = '2';
UPDATE sys_mainkpi_arg s ,sys_mainkpi_info m
SET s.`status` = 'N'
,m.`status` = 'N'
,m.end_time = NOW()
,m.runtime = TIMEDIFF( NOW(),m.start_time)
WHERE s.id = IN_ID AND m.id=IN_INFOID;
ELSE
SET V_RESLUT = '0';
UPDATE sys_mainkpi_arg s ,sys_mainkpi_info m
SET s.`status` = 'Y'
,m.`status` = 'Y'
,m.end_time = NOW()
,m.runtime = TIMEDIFF( NOW(),m.start_time)
WHERE s.id = IN_ID AND m.id=IN_INFOID;
END IF ;
END
涉及到的表结构
CREATE TABLE `sys_mainkpi_info` (
`id` bigint NOT NULL,
`code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '过程编码',
`name` varchar(255) DEFAULT NULL COMMENT '过程名称',
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '备注',
`status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'Y成功N失败R正在运行',
`runtime` varchar(255) DEFAULT NULL COMMENT '运行时长',
`arg_dt` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '传入参数(日期)',
`start_time` datetime DEFAULT NULL COMMENT '启动时间',
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
`start_user` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '启动人',
`user_ip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '启动人IP',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `sys_mainkpi_arg` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`info_id` bigint DEFAULT NULL COMMENT 'sys_mainkpi_info主键ID',
`code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '过程编码',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '过程名称',
`force_start_time` int DEFAULT NULL COMMENT '强制启动时长',
`status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'Y成功N失败R正在运行',
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '备注',
`updatetime` datetime DEFAULT NULL COMMENT '更新时间',
`updateuser` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '操作人',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
原因分析:
找了很长时间没找到问题,开始以为
IF-THEN-ELSE
里只能执行一条语句或不能执行update
语句,但经过测试这些都可以正常执行。
解决方案:
经过仔细排查发现:传入的参数和update条件中的字段类型不一致,传入参数类型
varchar
,update条件中的字段类型bigint
,尝试把传入参数的类型改成和表中类型一致的bigint
。
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN IN_ID bigint ( 50 ), #数据日期
IN IN_INFOID bigint ( 50 ), #数据日期
IN IN_DATE VARCHAR ( 10 ), #数据日期
OUT V_RESLUT VARCHAR ( 10 ))
COMMENT 'test'
BEGIN
#定义返回结果并赋初值0
DECLARE RESLUT_CODE INTEGER DEFAULT 0;
DECLARE result_code VARCHAR (8);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN GET DIAGNOSTICS CONDITION 1 result_code = RETURNED_SQLSTATE; SET RESLUT_CODE=2; END;
SET V_RESLUT = '0';#设置返回值为0
*****************逻辑语句*****************************
.......
再次运行过程,结果update执行成功。
得出结论:
MySQL存储过程中UPDAET需要用到传入参数时,传入参数应和update条件中的字段类型一致,否则update语句会执行不成功。