MySql中动态sql和会话变量的使用

1、概述

版本：MySql5.1.56-log

1.1背景

1. 工作中，需要使用动态Sql给变量赋值，但MySql(这个版本)里不支持。变通的使用了会话变量(@x)中转传递。
2. 测试中发现，第一次正常后，后续错误条件下也能取到正常值。这是一个严重的bug。
3. 检查发现，错误条件下，没有把上次的会话变量重置，返回时直接返回了上次的结果。是对MySql的会话变量理解的不透彻导致。

1.2主要内容

1. 动态sql的用法
2. 演示使用会话变量引入的bug
3. 解决bug的方法

2、实验

2.1基础表

#基础表
create table KEY_FILE
(   
  deviceid         VARCHAR(100),   
  md5key           VARCHAR(50)   
);
#测试数据
insert into key_file values('dev01','md5key');
delete from key_file;

select * from key_file;
#调用SP
call p_sel1("where deviceid='dev01' ",@o_id,@o_msg);
call p_sel2("where deviceid='dev01' ",@o_id,@o_msg);
#检查变量
select @o_id,@o_msg,@expt,@deviceid,@md5key;

2.2 有bug的sp

说明：

1. 不能直接在动态sql中使用普通变量,报错变量不能识别。即使预定义了变量。
2. 注意动态sql的固定语法，较为繁琐。
3. 动态sql中用select into @x，把值通过会话变量传给出参。
4. mysql的select into 空时，不报错；Oracle中会包no_data_found错误。

CREATE DEFINER=`test`@`%` PROCEDURE `p_sel1`(
	IN i_wheresql varchar (1000),
	OUT o_id varchar (200),
	OUT o_return_msg varchar (65525))
label_sp:BEGIN 
   
    declare   vs_deviceid varchar(100);
    declare   vs_md5key varchar(100);
    declare   vs_retinfo text;
	declare   lc_seltmpstr varchar(1000);	
    ##########################################	
    SET O_RETURN_MSG = 'ERROR';
    SET O_ID  = -1;
  
	set vs_retinfo ='[DeviceID,Md5Key]';
    
    ##不能直接在动态sql中使用普通变量,报错"Undeclare variable:vs_deviceid"
    #set lc_seltmpstr=concat('select DeviceID,MD5KEY into vs_deviceid,vs_md5key from key_file ',i_wheresql);
	
    set lc_seltmpstr=concat('select DeviceID,MD5KEY into @deviceid,@md5key from key_file ',i_wheresql);
	/*动态sql*/
	SET @V_SQL=lc_seltmpstr;
	PREPARE S1 FROM  @V_SQL;  /*预编译动态sql*/   
	EXECUTE S1;               /*执行动态sql*/
	DEALLOCATE PREPARE S1;    /*释放釋资源*/	    
    
    #set vs_retinfo=concat(vs_retinfo,'(',vs_deviceid,',',vs_md5key,')');
	set vs_retinfo=concat(vs_retinfo,'(',@deviceid,',',@md5key,')');
	set o_id         = 1;
	set o_return_msg = vs_retinfo;
	select concat('result:',o_id,':',o_return_msg);
	leave label_sp;
	
END

测试

call p_sel1("where deviceid='dev01' ",@o_id,@o_msg);

时机	@o_id	@o_msg	@deviceid	@ md5key	预期
有dev01的记录	1	DeviceID,Md5Key	dev01	md5kye	一致
删除dev01的记录后	1	DeviceID,Md5Key	dev01	md5kye	不一致，应该为空

问题引入点

删除dev01的记录后，动态sql的select into 没有实际效果，会话变量@deviceid、@md5key仍然保持上次的值。出参使用了会话变量，所以没有变。

2.3改进后的sp

说明

1. 提前增加了判断，如果没有数据，则直接返回结果。
2. 注意：会话变量还是没有清除，不过不影响功能了。

CREATE DEFINER=`test`@`%` PROCEDURE `p_sel2`(
	IN i_wheresql varchar (1000),
	OUT o_id varchar (200),
	OUT o_return_msg varchar (65525))
label_sp:BEGIN 
   
    declare   vs_deviceid varchar(100);
    declare   vs_md5key varchar(100);
    declare   vs_retinfo text;
	declare   lc_seltmpstr varchar(1000);	
    ##########################################	
    SET O_RETURN_MSG = 'ERROR';
    SET O_ID  = -1;
	
    ##########################################	
    #增加判断,无效时,重置会话变量
    set lc_seltmpstr=concat('select count(*) into @vi_key_exists from key_file ',i_wheresql);
	/*动态sql*/
	SET @V_SQL=lc_seltmpstr;
	PREPARE S1 FROM  @V_SQL;  /*预编译动态sql*/   
	EXECUTE S1;               /*执行动态sql*/
	DEALLOCATE PREPARE S1;    /*释放釋资源*/
	if @vi_key_exists=0 then
		select 'step2:googlekey not exists';
		set o_id         = -1;
		set o_return_msg = 'googlekey not exists';
		select concat('result:',o_id,':',o_return_msg);
		leave label_sp;
	end if;
    
	set vs_retinfo ='[DeviceID,Md5Key]';
    
    ##不能直接在动态sql中使用普通变量,报错"Undeclare variable:vs_deviceid"
    #set lc_seltmpstr=concat('select DeviceID,MD5KEY into vs_deviceid,vs_md5key from key_file ',i_wheresql);
	
    set lc_seltmpstr=concat('select DeviceID,MD5KEY into @deviceid,@md5key from key_file ',i_wheresql);
	/*动态sql*/
	SET @V_SQL=lc_seltmpstr;
	PREPARE S1 FROM  @V_SQL;  /*预编译动态sql*/   
	EXECUTE S1;               /*执行动态sql*/
	DEALLOCATE PREPARE S1;    /*释放釋资源*/	    
    
    #set vs_retinfo=concat(vs_retinfo,'(',vs_deviceid,',',vs_md5key,')');
	set vs_retinfo=concat(vs_retinfo,'(',@deviceid,',',@md5key,')');
	set o_id         = 1;
	set o_return_msg = vs_retinfo;
	select concat('result:',o_id,':',o_return_msg);
	leave label_sp;

	
END

测试

时机	@o_id	@o_msg	@deviceid	@ md5key	预期
有dev01的记录	1	DeviceID,Md5Key	dev01	md5kye	一致
删除dev01的记录后	-1	googlekey not exists	dev01	md5kye	一致;会话变量还在