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.
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.
...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
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.