saving blob field to disk from bash

I have a mysql database with a blob field containing a zip and I need to save it as a file on disk, from bash. I'm doing the following but the end result doesn't read as a zip... Am I d开发者_C百科oing something wrong or is the file stored not actually a zip (the entry in the database is actually created by a seismological station, so I have no control over it)?

echo "USE database; SELECT blobcolumn FROM table LIMIT 1" | mysql -u root > file.zip

then I open file.zip with a file editor and remove first line which contains the column header. Then 'unzip' doesn't recognize it as a zip file.

---

For a gzipped blob you can use:

echo "use db; select blob from table where id=blah" | mysql -N --raw -uuser -ppass > mysql.gz

I have not tried this with a zip file.

---

The proper way to do this would be to use DUMPFILE, otherwise mysql will mess up your data.

mysql -uroot -e "SELECT blobcolumn INTO DUMPFILE '/tmp/file.zip' FROM table LIMIT 1" database

---

I know this is an old question, but I needed the answer myself, so this is what worked for me.

I found that mysql appends a newline character at the end, which needs to be removed before the correct binary value remains.

echo "USE database; SELECT blobcolumn FROM table LIMIT 1" | mysql -N --raw -u root | head -c -1 > file.zip

---

you would need to skip column, like

sql="USE database; SELECT blobcolumn FROM table LIMIT 1"
mysql -u root -N <<< $sql > file.zip