Selecting many rows of binary data from MySQL, into dumpfiles

Question

I have to pull files stored in binary format from a MySQL database. There are thousands of files, so this needs to be done dynamically. Table structure looks like this:

__________________________________________
| Filename    |  Content                 |
------------------------------------------
| file1.pdf   | dasdsod1i9j09wej9nd...   |
| file2.pdf   | )(!)J(Jdjw19ddjw9d\00... |
| picture1.jpg| djw10dj0))!)JDFH*(#@NF...|
------------------------------------------

I know you can SELECT Content INTO DUMPFILE... but you can only do that when you select a single row. Is there a quick, easy way to do this to multiple rows?

Example of what I want: SELECT Content INTO DUMPFILE Filename, so it just takes the Content column, and dumps it into a file.

no quick/"easy" way. you'd need a loop and `select into outfile ...` each record individually. — Marc B, Nov 23 '15 at 21:51

score 1 · Accepted Answer · edited May 23 '17 at 12:15

Hope you will get small idea with this to continue your code.

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 ;

You can execute;

mysql> call dump_image();

See the source

score 0 · Answer 2 · answered Nov 23 '15 at 23:15

I thought MySQL could use a UDF to do this, so I just wrote one:

https://github.com/spachev/mysql_udf_bundle

For security reasons I restricted it to dumping into /tmp which you can change - OUTPUT_DIR variable in write_to_file.cc. To use, follow the build/install instructions, then:

SELECT write_to_file(Filename,Content) FROM tbl;

Also note that it does not check for FILE privilege, so if you deploy it somewhere important, make sure to set OUTPUT_DIR to something where you are OK if any user can dump random data. It has a security precaution to make sure the file name does not have / in it.

score 0 · Answer 3 · answered Jul 17 '20 at 21:13

Code below save all attachments from blob column in otrs to files. As there is no possible to use expression in outfile procedure is needed.

DELIMITER $$
DROP PROCEDURE IF EXISTS  cursor_proc $$
 
CREATE PROCEDURE cursor_proc()
 BEGIN
   DECLARE mail         VARCHAR(50);
   DECLARE id           VARCHAR(10);
   DECLARE file_date    VARCHAR(19);
   DECLARE file_name    VARCHAR(255);
   DECLARE exit_loop BOOLEAN;         
   DECLARE file_cursor CURSOR FOR SELECT 
        IFNULL(ticket.customer_user_id, 'other') as mail,
        article_data_mime_attachment.id as id, 
        article_data_mime_attachment.create_time as file_date, 
        article_data_mime_attachment.filename as file_name
        FROM
            article_data_mime_attachment,
            article,
            ticket
        WHERE
            article_data_mime_attachment.article_id = article.id AND
            article.ticket_id = ticket.id;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
   OPEN file_cursor;
   file_loop: LOOP
     FETCH file_cursor INTO mail, id, file_date, file_name;
     SET @queryString = (SELECT CONCAT('select content from article_data_mime_attachment where id = ', id ,' into outfile "/var/lib/mysql-files/', mail, '-', id, '-', file_date, '-', file_name, '";'));
     SELECT @queryString;
     PREPARE  stmt FROM @queryString;
     EXECUTE  stmt;
     DEALLOCATE PREPARE stmt; 
     
     IF exit_loop THEN
         CLOSE file_cursor;
         LEAVE file_loop;
     END IF;
   END LOOP file_loop;
 END $$
 
 DELIMITER ;
 
 CALL cursor_proc();

Selecting many rows of binary data from MySQL, into dumpfiles

3 Answers3