Extract binary data from DB to file

Question

How do I extract binary data from DB to file

CREATE TABLE `mail_attachs` (
  `attachid` int(15) NOT NULL auto_increment,
  `filecontent` longtext,
  PRIMARY KEY  (`attachid`),
) ENGINE=MyISAM 

The binary data has been saved in the filecontent column. I want to extract the files and text saved in this column and save it to a file on the same server. The filename will contain the attachid so that I can identify and relate the record found in the DB.

Answer 1

Just read from the database using mysql_query, mysql_fetch_assoc and store to file using file_put_contents

$result = mysql_query("select attachid, filecontent from mail_attachs");
while($row = mysql_fetch_assoc($result))
      file_put_contents($row['attachid'], $row['filecontent']);

Answer 2

You tagged this with 'perl' too, so here's a perl solution. The "BETWEEN" clause is an easy way to only process so much data at a time, since the selectall_arrayref call will (under current versions of DBI modules) load all the data you ask for into memory.

mysql> insert into mail_attachs values ( 1, 'abc' ), ( 2, 'def' );
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

$ cat extract.pl 
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $output_dir = '/home/jmccarthy/mail_attachs';
my $dbh = DBI->connect_cached("DBI:mysql:database=test;host=dbmachine",
    'username', 'password');
die "cannot connect, $DBI::err" if ! $dbh;
my $rows = $dbh->selectall_arrayref("SELECT attachid, filecontent FROM mail_attachs
    WHERE attachid BETWEEN ? AND ?",
    { Slice => {} }, # get back better-self-documenting hashrefs, instead of arrayrefs
    1, 1000); # values for the ?'s above
die "selectall_arrayref failed, $DBI::err" if $DBI::err;
for my $row (@$rows) {
    open my $fh, '>', "$output_dir/file$row->{attachid}"
        or die "cannot write $row->{attachid}, $!";
    print $fh $row->{filecontent}
        or die "cannot print to $row->{attachid}, $!";
    close $fh
        or die "cannot close $row->{attachid}, $!";
    print "wrote $row->{attachid}\n";
}

$ ./extract.pl 
wrote 1
wrote 2

$ head mail_attachs/file*
==> mail_attachs/file1 <==
abc
==> mail_attachs/file2 <==
def