mysql开发存储过程中，select结果集再动态的条件下select count(*)统计返回都为0问题解决

喜欢ヅ旅行 2021-09-26 05:20 362阅读 0赞

在写mysql存储过程中，遇到一个坑，一般逻辑下select结果集再动态的条件下select count(\*)统计返回是能得到正确统计值。 这里写代码片但在光标嵌套和for循环中却出现动态sql的结果集始终为0，导致后续程序逻辑错误。最后通过concat方法构造动态sql来执行并获取结果集才成功。

网站找了些但总觉得不够统一，下面分析下mysql存储过程中select结果集返回值。

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_3;  
    DELIMITER //
    create procedure pro_test_3(IN p_in varchar(50),IN s_in INT) begin -- 方式 1 DECLARE cnt INT DEFAULT 0;  
            DECLARE count_x INT DEFAULT 0;  
        select count(*) into cnt from test_tbl;  
        select cnt;  
    -- select p_in;
    -- 方式 2 
        set @cnt = (select count(*) from test_tbl);  
        select @cnt;  
            select p_in;
    -- 方式 3 
        select count(*) into @cnt1 from test_tbl;  
        select @cnt1;  
    
    
    -- 方式 1 加条件 
        select count(*) into count_x from test_tbl where `NAME` = p_in AND `status` = s_in;  
        select count_x; 
    
    -- 多个列的情况下似乎只能用 into 方式 
        select max(status), avg(status) into @max, @avg from test_tbl;  
        select @max, @avg;  
    end // DELIMITER ;

上述的获取结果集都能正确，select XXX是可以调试语句的返回到界面显示。

但以下情况下却不一定能得到正确结果集返回值。

**动态sql， 即动态参数：**

在存储过程中，想要直接用表名变量做参数，动态执行sql，不能直接写

create procedure ( $tableName char(20) ) BEGIN select * from tableName;
    
    END

*  mysql 不支持表名作为变量，这样会直接将变量名“tableName”作为表名去查询，解决的方法是： 通过构造sql字符串去用方法执行。
 *  如参数命名问题，改了一些参数命名，原来where后面作为条件的变量名不能和字段名相同，而且这里是不区分大小写的。但是作为update和insert into的参数确是可以的
 *  where后面条件字段动态获取不一定能动态生成正确的语句导致执行结果错误。

select count(*) into count from pet where condition;  
    if count = 0 then   
        update pet set key1 = value1, ...  where condition;  
    else  
        insert into pet (key1, ...) values (value1, ...);  
    end if;

上述代码有可能得到count会一直是0，当用下属办法构建语句时候能正确获取返回值。

BEGIN SET @sql = concat('select * from ', $tableName);
    
     PREPARE stmt1 FROM @sql;
    
     EXECUTE stmt1;
    
     DEALLOCATE PREPARE stmt1;
    
    END;

得到动态sql， select查询的结果：

可以直接 在sql 语句中 得到， “select \* into @var” 就行了，后边就可以直接运用该变量了。

例子：

SET @sql = concat('select max(id) into @max_id from ', $tableName);
    PREPARE stmt1 FROM @sql; EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

此时，@max\_id 就保存了查询结果。