Question

这个mysql 存储过程 哪里有问题 如果存在记录就 更新 count + 1，不存在就插入

Delimiter //
CREATE PROCEDURE updateSearchRecord(usernamep char(32),typep char(16))
BEGIN
declare p_isexist,p_count,p_count int;

set p_isexist = 0;
set p_count = 0;
set p_id = 0;
select count(*),id,count INTO p_isexist,p_id,p_count from searchrecord Where username = usernamep And type = typep;
if (p_isexist> 0) Then
update searchrecord set opdate=NOW(),count = p_count +1 where id = p_id
else
insert into searchrecord(username,type,count) values(usernamep ,typep ,1);
end if;
END;
//

Answer 1

searchrecord 表里面的 username 的数据类型， 是 char(32) ？

searchrecord 表里面的 type 的数据类型， 是 char(16) ？

还有就是你调用存储过程的时候， 是怎么调用的？

还有就是

select count(*),id,count INTO p_isexist,p_id,p_count from searchrecord Where username = usernamep And type = typep;

这个 sql 语句上面， 有点问题。

可以修改为
select count(*) INTO p_isexist from searchrecord Where username = usernamep And type = typep;

然后后面的
update searchrecord set opdate=NOW(),count = p_count +1 where id = p_id

修改为

update searchrecord set opdate=NOW(),count = count + 1 where username = usernamep And type = typep;