mysql blob导出,将Blob从MySQL数据库导出到只有SQL的文件

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*