首页 > MySQL > Mysql存储过程参数名和表列明相同引起的问题

Mysql存储过程参数名和表列明相同引起的问题

2012年3月9日 发表评论 阅读评论

下面存储过程原本是想根据传入的status值来更新对应列的name,代码如下:

drop table if exists test_tbl;

create table test_tbl (
	name varchar(20),
	status int(2)
);

insert into test_tbl values
('abc', 1),
('edf', 2),
('xyz', 3);

drop procedure IF EXISTS pro_test_1;
delimiter //
create procedure pro_test_1(in status int(2))
begin	
	update test_tbl set name = concat(name, '_new') where test_tbl.status = status;
end
//
delimiter ;

call pro_test_1(1);

select * from test_tbl;

调用上述存储过程后发现所有记录的name都被更新了,存储过程就只有一个update语句,而且执行了,问题出在where条件上,原意是where的第一个status为表的列名,第二个是参数,此处被存储过程都理解为参数,所以where条件永远是true。因此一定要注意列名和参数名相同问题,否则在执行delete或update时会酿成大祸。解决方法有3中,一是修改参数的名字,二是在字段前加上表名,第三种是update使用预处理语句的方式。代码如下:

第二种方法代码:

update test_tbl set name = concat(name, '_new') where status = status;
修改为:
update test_tbl set name = concat(name, '_new') where test_tbl.status = status;

第三种方法代码:

	set @t = status;
	PREPARE STMT FROM "
		update test_tbl  set name = concat(name, '_new') where status = ?
	";
	EXECUTE STMT USING @t;

另外定义游标时候可能也会出现这样的问题,解决方法跟上面类似,注意游标在申明前不能使用set,示例代码:

drop table if exists test_tbl;

create table test_tbl (
	name varchar(20),
	status int(2)
);

insert into test_tbl values
('abc', 1),
('edf', 2),
('xyz', 3);

drop procedure IF EXISTS pro_test_2;
delimiter //
create procedure pro_test_2(in status int(2))
begin	
	DECLARE done INT DEFAULT 0;
	DECLARE na varchar(50) DEFAULT NULL;

	DECLARE cur CURSOR FOR 
		select name from test_tbl where test_tbl.status = status;
--  或
--  	select name from test_tbl t where t.status = status;
--  再或, 注意后面需要为 @s 赋值
--  	select name from test_tbl where test_tbl.status = @s;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

--  set @s = status;

	open cur;
	REPEAT
		FETCH cur INTO na;
		if not done then
			select na;				
		end if;
 	UNTIL done END REPEAT;
	CLOSE cur;	
end
//
delimiter ;

call pro_test_2(1);


欢迎转载,转载请注明文章出处,谢谢!
垃圾有点差凑合看还不错很精彩 (2 人打了份: 平均分:5.00)
Loading...Loading...
  1. gqqnb
    2014年7月10日03:18 | #1

    有用!我就遇到了这样的问题

  1. 本文目前尚无任何 trackbacks 和 pingbacks.

无觅相关文章插件,快速提升流量