mysql> CREATE PROCEDURE `NewProcedure`(in int_role_id int(11)) -> BEGIN -> declare str_sql,str_h1,str_camp1 varchar(100) default ''; -> declare int_1 ,int_tmp int(11) default 0; -> select @int_role_id,@int_tmp,int_role_id,int_tmp; -> SET int_tmp=1; -> select @int_role_id,@int_tmp,int_role_id,int_tmp; -> SET @int_tmp=2; -> select @int_role_id,@int_tmp,int_role_id,int_tmp; -> SET @str_sql=concat('SELECT id,name,camp into @int_1,@str_h1,@str_camp1 FROM hero WHERE `role_id`=? AND `status`=0 LIMIT ?,1;'); -> PREPARE stmt FROM @str_sql; -> EXECUTE stmt USING @int_role_id,@int_tmp; -> delete from err_log; -> insert into err_log set data=concat('int_1=',@int_1,',',int_1,',str_h1=',@str_h1,',int_role_id=',int_role_id,',int_tmp=',int_tmp); -> -> END//Query OK, 0 rows affected (0.00 sec) mysql> call NewProcedure(46)//+--------------+----------+-------------+---------+| @int_role_id | @int_tmp | int_role_id | int_tmp |+--------------+----------+-------------+---------+| 46 | 1 | 46 | 0 | +--------------+----------+-------------+---------+1 row in set (0.00 sec) +--------------+----------+-------------+---------+| @int_role_id | @int_tmp | int_role_id | int_tmp |+--------------+----------+-------------+---------+| 46 | 1 | 46 | 1 | +--------------+----------+-------------+---------+1 row in set (0.00 sec) +--------------+----------+-------------+---------+| @int_role_id | @int_tmp | int_role_id | int_tmp |+--------------+----------+-------------+---------+| 46 | 2 | 46 | 1 | +--------------+----------+-------------+---------+1 row in set (0.00 sec) Query OK, 1 row affected (0.00 sec) mysql> select * from err_log order by id desc//+----+-------------+------+------------------------------------------------------+---------+| id | create_time | type | data | user_id |+----+-------------+------+------------------------------------------------------+---------+| 4 | 0 | | int_1=82,0,str_h1=王语嫣1_4,int_role_id=46,int_tmp=1 | 0 | +----+-------------+------+------------------------------------------------------+---------+1 row in set (0.00 sec)
1 存储过程如果传进一个参数 int_id，值为1
则在存储过程中int_id和@int_id的值都是1
2 如果在存储过程中 set @int_id2=1;
则@int_id2=1， int_id2 如果没有declare要报错，定义了此变量则它的值还是它的默认值
3 如果在存储过程中 set int_id3=1
则@int_id3=NULL， int_id2 =1
4 @int_id3不需要declare就可以使用，而int_id3必须先定义
总结：@a_var和a_var是两个不同的变量，当存储过程中传入变量a_var则 a_var和@a_var都有了值
