29

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?

suicide
  • 760
  • 4
  • 13
  • 20

2 Answers2

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.

kara
  • 3,205
  • 4
  • 20
  • 34
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
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*
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