将Blob从MySQL数据库导出到只有SQL的文件 [英] Exporting Blob from MySQL database to file with only SQL

问题描述

我有一个表格，图像数据存储在MySQL数据库的一个blob字段中。有没有办法通过仅使用SQL将这些图像导出到文件系统上的文件？图像应该命名为{imageId} .jpg

我知道使用Java或其他方法可以轻松实现，只需使用SQL脚本即可。 p>

解决方案

我不喜欢这个想法...

  drop procedure if exists dump_image; 
分隔符// 
创建过程dump_image（）
 begin 
 
声明this_id int; 
从图像中声明选择imageId的cur1游标; 
打开cur1; 
 read_loop：loop 
将cur1转换为this_id; 
 set @query = concat（'select blob_field from image where imageId ='，
 this_id，'into outfile/ tmp / xyz-'，this_id，'。jpg'）; 
从@query准备write_file; 
执行write_file; 
结束循环; 
关闭cur1; 
 end // 
 delimiter; 
  

尽管出现错误

 
 mysql>调用dump_image（）; 
错误1329（02000）：没有数据 - 零行获取，选择或处理
 

 ls -1 / tmp / xyz * 

I have a table with image data stored in a blob field in a MySQL database. Is there a way to export those images to files on the filesystem by using only SQL? The images should be named {imageId}.jpg

I know that it is easy to do this with Java or whatever but is it possible with just a SQL script?

解决方案

I don't like the idea ...

drop procedure if exists dump_image;
delimiter //
  create procedure dump_image()
  begin

    declare this_id int;
    declare cur1 cursor for select imageId from image;
    open cur1;
      read_loop: loop
        fetch cur1 into this_id;
        set @query = concat('select blob_field from image where imageId=', 
            this_id, ' into outfile "/tmp/xyz-', this_id,'.jpg"');
        prepare write_file from @query;
        execute write_file;
      end loop;
    close cur1;
  end //
delimiter ;

Despite the error

mysql> call dump_image();
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

ls -1 /tmp/xyz*

这篇关于将Blob从MySQL数据库导出到只有SQL的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！