Exporting multiple blobs/images from an SQL database

MySqlExportBlob.md

Exporting multiple blobs from an SQL database.

This is tested for MySQL with Linux or OSX

I was in a situation where I had images saved into an SQL table, as "blobs" and needed to export them into proper jpg files.

This relies on the SQL syntax SELECT INTO DUMPFILE. However this command can only export one row at a time.

1. Get the IDs of the rows we want to export

select id from photo where home_id in (32,37,34,45,38,39,30,1,4,2,9,17)

+-----+
| id  |
+-----+
|  44 |
|  45 |
|  47 |
... 

I happy with the output then it can be used as a subquery for the next step.

2. Generate the SQL queries

...which will export binary blobs from these rows. One query per row. We modify the above query to output a series of queries instead of IDs.

select 
 CONCAT("select `original` into dumpfile '/tmp/dbDump/", `id`, ".jpg' from photo where `id` = ", `id`,';') as queries
 from photo where home_id in (32,37,34,45,38,39,30,1,4,2,9,17)

It might be better to add some meaningful information in the filename:

select 
 CONCAT(
 	"select `original` into dumpfile '/tmp/dbDump/",
 	home.label,
 	".", 
 	photo.id, 
 	".jpg' from photo where `id` = ", 
 	photo.id,
 	';') 
 as queries
 from photo left join home on photo.home_id = home.id 
 where home_id  in (32,37,34,45,38,39,30,1,4,2,9,17)

We would get:

+-----------------------------------------------------------------------------------+
| queries                                                                           |
+-----------------------------------------------------------------------------------+
| select `original` into dumpfile '/tmp/dbDump/Maison.44.jpg' from photo where `id` = 44   |
| select `original` into dumpfile '/tmp/dbDump/Maison.45.jpg' from photo where `id` = 45   |
| select `original` into dumpfile '/tmp/dbDump/Maison.47.jpg' from photo where `id` = 47   |
...

If using the command line mysql client, then the tabular output will contain some formatting pipe characters (|) as above, which need to be removed:

I used Sublime Text to run a regex replace, using the following regular expression:

(^\| |\s\|$)

...to find all formatting pipe characters and then deleted them

3. Save output

Make sure output directory exists and is writable

mkdir /tmp/dbDump
chmod 777 /tmp/dbDump

Now we just have to run these queries and pick up our blobs from /tmp/dbDump

The output can be zipped into a single file:

zip -3 -r photos.zip /tmp/dbDump

Excellent. Exactly my problem too. I was using digikam on Linux and my drive died but my mariadb/mysqldb was elsewhere so I have the thumbnails as blobs. Now I want to retrieve the thumbnails as jpgs or as PGFs (Progressive Graphics files) which is what digikam uses.

Excellent. Exactly my problem too. I was using digikam on Linux and my drive died but my mariadb/mysqldb was elsewhere so I have the thumbnails as blobs. Now I want to retrieve the thumbnails as jpgs or as PGFs (Progressive Graphics files) which is what digikam uses.

Thank you!
Happy to hear that!
Hopefully you were able to adapt the solution to your problem without too much trouble.
The idea is simple, we use MySQL to generate SQL queries which are then fed back into MySQL to get the files.

Cheers!