Exporting Blob from MySQL database to file with only SQL

Question

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?

That probably depends on whether or not the user MySQL runs under has write access to the filesystem or not. — Stefan Steiger, Jan 10 '11 at 12:03
Of course, but is it even possible? Because I haven't seen a SQL command to access the fielsystem — suicide, Jan 10 '11 at 12:12

score 25 · Answer 1 · edited May 04 '19 at 08:47

25

Using INTO, and assuming you have write permission as the mysql user in the location you wish to store the files, you can do:

SELECT id, blob INTO DUMPFILE '/tmp/path' FROM table;

Unfortunately, in MySQL it is not possible to specify the dumpfile as an expression/variable. However, you could achieve this if you wrapped it in a stored procedure and use variables.

edited May 04 '19 at 08:47

kara

3,205
4
20
34

answered Jan 10 '11 at 12:52

Shaun Hare

3,771
2
24
36

2

It seems using a `dumpfile` instead of `outfile` helps with serialized Java objects. – Boris Pavlović Jun 11 '13 at 16:26
You're right, it seems that DUMPFILE works better than outfile – rasputino Feb 25 '16 at 12:44
In case you are on Unix and have `perl` and `xxd` and just need to extract a single "cell", do this: 1) Get it out in hexdump format: `echo "SELECT cell FROM stuff WHERE id = '5656'" | mysql -b -u root -p muhdatabase > OUT.raw` 2) Extract the hexstring and binarize: `cat OUT.raw | perl -n -e 'if (/^0x([0-9A-F]+)$/) { print $1 }' | xxd -r -p > OUT.bin` 3) To check: `file OUT.bin` – David Tonhofer Oct 16 '18 at 13:46
Why do you have to SELECT id? Why can't it be SELECT blob INTO DUMPFILE '/tmp/path' FROM table; – SJHowe Sep 15 '22 at 22:28

ajreal · Accepted Answer · 2011-01-10T13:09:18.277

16

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*

edited Jan 10 '11 at 13:09

answered Jan 10 '11 at 12:39

ajreal

46,720
11
89
119

Where did you set `this_id`? You didn't pass in anything, and it's not set in the proc, so the query's going to fail. Otherwise this looks like it'd work – Marc B Jan 10 '11 at 12:50
using the cursor `fetch cur1 into this_id;` – ajreal Jan 10 '11 at 13:02
3

Agree don't like the idea myself, you need to be very careful re security etc. But elegent solution - outfile as oppossed to dumpfile led to a corrupt file for me – Shaun Hare Jan 10 '11 at 13:11
2

+1 Very nice way to do it. As Shaun commented already, if the images are not showing after using this procedure, use "dumpfile" instead of "outfile". – lepe Aug 23 '11 at 02:41
1

The server needs to run w/o the 'secure-file-priv' option: `The MySQL server is running with the --secure-file-priv option so it cannot execute this statement`. Also, according to the secription of [SELECT ... INTO Syntax](https://dev.mysql.com/doc/refman/8.0/en/select-into.html), the keyword `OUTFILE` will add formatting, as opposed to `DUMPFILE`, so use the latter. – David Tonhofer Oct 16 '18 at 12:26

Exporting Blob from MySQL database to file with only SQL

2 Answers2

Linked