问题描述

mysql存储过程报错,报的联合主键约束错误

Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '5RgwDTn5zeAoPM1yXxeRkm-YUseiccsUNQxcQeBPK3Td3' for key 'PRIMARY' ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: {call pro_read_one (?, ?, ?)} ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '5RgwDTn5zeAoPM1yXxeRkm-YUseiccsUNQxcQeBPK3Td3' for key 'PRIMARY' ; SQL []; Duplicate entry '5RgwDTn5zeAoPM1yXxeRkm-YUseiccsUNQxcQeBPK3Td3' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '5RgwDTn5zeAoPM1yXxeRkm-YUseiccsUNQxcQeBPK3Td3' for key 'PRIMARY'

解决方案

BEGIN
DECLARE ISEXIST INT DEFAULT 0;
DECLARE nextval int DEFAULT 0;

SELECT COUNT(1) INTO ISEXIST FROM TBL_RES_READ C WHERE C.RES_ID = RID AND C.USER_ID = UID;

IF ISEXIST = 0 THEN
INSERT INTO TBL_RES_READ(RES_ID, USER_ID, CREATE_DATE) VALUES (RID, UID, NOW());
END IF;

call get_next_version('seq_res_version',@next_val);

select @next_val into nextval;

update tbl_res_user_unread u
set u.status_new = 0, u.comment_new = 0, u.version = nextval
where u.res_id = rid and u.user_id = uid;

SET RES = 1;
END
存储过程里面这么写的

解决方案二：

开启事务，判断 。你这种的 等你做判断的时候 已经insert 过了