直接上脚本内容

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;
      /* 声明错误处理 */
  -- 记录未找到
  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

1. 若导出文件已存在，则跳过，不覆盖原文件。

2. 若生成文件后，比如生成 a.html,b.html,c.html之后，把其中某个文件删除后，重新执行该存储过程，会重新生成被删除的文件。

3. 若游标查询一开始全部为空，不报错，不生成任何文件。

4. select * into outfile file from table where my_condition