Mysql 使用存储过程 将数据导入文件

直接上脚本内容

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 SQLSTATE '02000' SET done = 1;
  -- 导出文件已存在
  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;
  -- 游标查询 为空
  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 @outsql = concat('SELECT userid,content into outfile ',''',@file,' from  test.user_post where userid = ',''');
      PREPARE stmt FROM @outsql;
      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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!