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

前端之家收集整理的这篇文章主要介绍了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 <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

原文链接:https://www.f2er.com/note/421569.html

猜你在找的程序笔记相关文章