DROP TABLE IF EXISTS `t_aaaa`;
CREATE TABLE `t_aaaa` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL DEFAULT 'N/A',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;INSERT INTO `t_aaaa` VALUES (1,'aaa');
INSERT INTO `t_aaaa` VALUES (2,'bbb');
INSERT INTO `t_aaaa` VALUES (3,'ccc');CREATE DEFINER=`root`@`%` PROCEDURE `sp_aaaa`(
p_name varchar(30)
)
begin
declare v_id int;
select id into @v_id from t_aaaa where name=p_name;
set v_id=@v_id;
select v_id;
end;
call sp_aaaa('aaa');call sp_aaaa('ddd');
通过上面的代码,我第一次调用存储过程,返回1(call sp_aaaa('aaa')),正确;
第二次调用的时候个人认为应该返回NULL,但是返回的还是1;
经过多次测试,测试结果是:如果name在表中存在,则返回正确的结果,如果不存在则返回上次正确过的结果;
哪位给解释下原理,以及如何处理这种状况,谢谢!
call sp_aaaa('ddd');
为NULLCREATE PROCEDURE `sp_aaaa`(
p_name varchar(30)
)
begin
declare v_id int;
select id into @v_id from t_aaaa where name=p_name;
set v_id=@v_id;
select v_id;
end;
begin
set @v_id = 0;
end;
end if;
select id into @v_id from t_aaaa where name=p_name;
当无符合记录时,根本就不是执行 id into @v_id ,也就是@v_id根本没有被赋值。
而你刚执行过call sp_aaaa('aaa');,导致@v_id的值为1,当你call sp_aaaa('ddd');的时候没有对@v_id进行任何操作,所以@v_id仍为1你可以先试
call sp_aaaa('bbb');
call sp_aaaa('ddd');看看结果是不是2
begin
declare v_id int;
select count(*) into v_id from t_aaaa where name=p_name;
select v_id;
end;
select id into @v_id from t_aaaa where name=p_name;
SELECT CASE WHEN count(*)=0 THEN -1(这里给出默认值) ELSE id END into @v_id FROM t_aaaa WHERE name=p_name;
我再补充两条:1. SELECT if(sum(id),id,-1) into @v_id FROM t_aaaa WHERE name=p_name;缺点:如果name列的值有重复,则返回第一条记录id值。2. SELECT ifnull(sum(id),-1) into @v_id FROM t_aaaa WHERE name=p_name;缺点:如果name列的值有重复,则返回所有满足条件记录的id值总合。
begin
declare v_id int;
select id into v_id from t_aaaa where name=p_name;
select v_id;
end;写错了,有些混。应该是这个。