mysql 中 @

 
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都有了值

 

转载于:https://www.cnblogs.com/Iamlein/archive/2011/03/03/2375909.html