Menu
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?
330
To store a BLOB using FILESTREAM you must have a column of type VARBINARY(MAX) with the FILESTREAM attribute specified. In addition the table must have a UNIQUEIDENTIFIER column with the ROWGUIDCOL attribute. To add a BLOB from T-SQL, you execute an INSERT statement, and simply CAST the BLOB contents to VARBINARY(MAX).
To Store BLOB data in a MySQL table, we need to create a table containing binary data. Alternatively, if you have a table, then modify it by adding one extra column with BLOB as its data type. You can use the following query to create a table with a BLOB column. This table contains the following two BLOB columns.
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.
75
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
