Exporting Blob from MySQL database to file with only SQL

sql mysql image export blob
suicide picture suicide · Jan 10, 2011 · Viewed 57.9k times · Source

I have a table with image data stored in a blob field in a MySQL database. Is there a way to export those images to files on the filesystem by using only SQL? The images should be named {imageId}.jpg

I know that it is easy to do this with Java or whatever but is it possible with just a SQL script?

Answer

Shaun Hare picture Shaun Hare · Jan 10, 2011

Using INTO, and assuming you have write permission as the mysql user in the location you wish to store the files, you can do:

SELECT id, blob INTO DUMPFILE '/tmp/path' FROM table;

Unfortunately, in MySQL it is not possible to specify the dumpfile as an expression/variable. However, you could achieve this if you wrapped it in a stored procedure and use variables.

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More