Mysql – How to search in blob binary wise, and how to export data binary wise

blobMySQLmysql-workbenchmysqldump

I've a table with a blob field for each row.
The blob contains binary data, which can be represented as bytes or text ( using mysql workbench – open value in viewer)

  1. Does anybody know how can I search specific sequence of bytes in a given bytes chunk(blob field),
    for example search for the sequence of bytes: 00 38 a7 in blob's binary representation

  2. Is there any possibility to export all table values to csv with blob values in binary representation When I tried to export using mysql workbench I got for each blob … as a value in csv.

Best Answer

One way...

SELECT ... FROM ... WHERE HEX(blob_col) REGEXP '(..)*0038A7';

The (..)* skips over any number of pairs of the hex digits.

For dumping, see mysqldump --hex-blob (not good for csv)

See SELECT ... INTO OUTFILE

Related Question