直接上脚本内容
@H_301_3@DELIMITER // CREATE PROCEDURE test_user_temp() BEGIN DECLARE done INT DEFAULT 0; DECLARE userid VARCHAR(5); DECLARE file_exist CONDITION FOR 1086; DECLARE thread_null CONDITION FOR 1064; /* 定义游标 cursor */ DECLARE userinfo_cursor CURSOR FOR SELECT distinct a.userid from test.user_post a,user_temp.user_temp b where a.userid=b.userid; /* 声明错误处理 */原文链接:https://www.f2er.com/note/421569.html-- 记录未找到 DECLARE CONTINUE HANDLER FOR <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>STATE '02000' SET done = 1; -- 导出<a href="https://www.jb51.cc/tag/wenjian/" target="_blank" class="keywords">文件</a>已存在 DECLARE CONTINUE HANDLER FOR file_exist SET done = 1; DECLARE CONTINUE HANDLER FOR 1329 SET done=1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 游标<a href="https://www.jb51.cc/tag/chaxun/" target="_blank" class="keywords">查询</a> 为空 DECLARE EXIT HANDLER FOR thread_null SET done = 1; OPEN userinfo_cursor; /* 循环 */ REPEAT FETCH NEXT FROM userinfo_cursor INTO userid; if(done <= 0) then SET @file = concat('/data/path/dir/',userid,'beautify','.html'); SET @out<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> = concat('SELECT userid,content into outfile ','\'',@file,' from test.user_post where userid = ','\''); PREPARE stmt FROM @out<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET done = 0; end if; -- select userid,done,"bb" as log into outfile 'testlog.txt'; UNTIL done = 1 END REPEAT; CLOSE userinfo_cursor;
END