用于将mysql blob数据导出查看,对于一条记录的写一条sql语句就够了,但现在要查询一堆记录,只好写了个存储过程。
drop procedure IF EXISTS test_proc;
delimiter //
create procedure test_proc()
begin
DECLARE done INT DEFAULT 0;
declare nGuid BIGINT UNSIGNED;
DECLARE FileNo INT DEFAULT 0;
DECLARE szFileNo VARCHAR(32) DEFAULT "";
DECLARE FilePath VARCHAR(32) DEFAULT "/data/game/Quest/Q";
DECLARE FileName VARCHAR(64) DEFAULT "";
-- 声明游标对应的 SQL 语句
DECLARE cur CURSOR FOR
select Guid from t_Human where Level>=10 and Level<=11;
-- 在游标循环到最后会将 done 设置为 1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 执行查询
open cur;
-- 遍历游标每一行
REPEAT
-- 把一行的信息存放在对应的变量中
FETCH cur INTO nGuid;
if not done then
set FileNo=FileNo+1;
select FileNo into szFileNo;
set FileName = concat(FilePath, szFileNo);
set @smt = concat("select QuestData from t_Quest where Guid=",nGuid, " into dumpfile '",FileName,"';");
prepare se from @smt;
execute se;
end if;
UNTIL done END REPEAT;
CLOSE cur;
end
//
delimiter ;
-- 执行存储过程
call test_proc();