Mysql存储过程execute结果如何赋值？

[chzbv1984]

代码如下：

set one_p_like=CONCAT('"',one_p,'%"');
SELECT count(address) into nums from visit_log where address like one_p_like; #赋值给nums总是0
set p_sql=CONCAT("select count(address) as p_nums from visit_log where address like"," ",one_p_like);
set @p=p_sql;
PREPARE p_sql FROM @p;
EXECUTE  p_sql; #执行后如何把结果赋值给nums？
INSERT INTO visit_log_provinces(province,num) VALUES(one_p,nums) ON DUPLICATE KEY UPDATE num=nums;

one_p参数像“北京市”，但直接赋值总是为0，拼接后执行可以得到正确结果，但怎么赋值给nums呢？

[chzbv1984]

,有人知道吗？吱一声呗

[shinesky]

drop procedure if exists test;
delimiter //
create procedure test(
	`one_p` varchar(10)
)
begin

	declare _one_p_like varchar(20);
    declare _nums int;
    
    create temporary table if not exists temp_test as 
    select '北京市dkdjfkdjf' as address
    union
    select '北京市12121';
    
    set _one_p_like = concat(one_p, '%');
    
    SELECT count(address) into _nums from temp_test where address like _one_p_like;
    
    set @p=CONCAT('select count(address) into @p_num from temp_test where address like', '\'', _one_p_like, '\'');
	PREPARE p_sql FROM @p;
	EXECUTE  p_sql;
    deallocate prepare p_sql;
    
    select _nums;
    select @p_num;
    
    drop temporary table if exists temp_test;
    
end; //

delimiter ;

call test('北京');

[chzbv1984]

谢谢楼上指导！