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