mysql 存储过程中使用变量做表明[学习记录]

最新推荐文章于 2025-02-12 17:28:49 发布

原创最新推荐文章于 2025-02-12 17:28:49 发布 · 662 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #存储 #sql #table

mysql 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一个用于从数据库中获取特定键的总数和待处理数的过程。该过程通过定义游标遍历索引表，并使用预处理语句来动态执行SQL查询，从而实现了高效的数据统计。

create procedure stats()
begin
declare k char(32);
declare tbl char(1);
declare done int default 0;
declare cur cursor for select `key`,`table` from `index`;
declare continue handler for sqlstate '02000' set done=1;

open cur;
repeat
fetch cur into k,tbl;

set @total = 0;
set @inqueue = 0;

set @sql = concat('select count(dk) into @total from `',tbl);
set @sql = concat(@sql,'` where `k`=\'');
set @sql = concat(@sql,k);
set @sql = concat(@sql,'\'');
prepare st from @sql;
execute st;

set @sql = concat('select count(dk) into @inqueue from `',tbl);
set @sql = concat(@sql,'` where `k`=\'');
set @sql = concat(@sql,k);
set @sql = concat(@sql,'\' and f=0');
prepare st from @sql;
execute st;

select k as `key`,@total as total,@inqueue as inqueue;

until done end repeat;
close cur;
end