Joining binary data from blobs in mysql

Question

I have a mysql database table with audio data in MEDIUMBLOB fields. The thing is, one audio file is in different rows. So I want to join them. When I do this:

select data from audio where id=1 into outfile "output" fields escaped by '';

.. I get audio. When I do the same thing for id=2, I get audio. When I put them together:

select data from audio order by date, time into outfile "output" fields escaped by '';

.. I get audio for awhile, then high amplitude noise. The noise starts where id=2 would have been. If I select more than two columns to put together, sometimes the output from that particular id is noise, sometimes it's the correct audio. It's not exactly interleaved.

So, how can I extract and concatenate blobs from multiple rows into a coherent binary output file?

Edit: this is raw audio data. E.g. to read it into Audacity you would go to import->raw.

why type of 'audio' file is contained within the binary data? — Steve
– Steve, Commented Dec 28, 2014 at 19:24

Steve · Accepted Answer · 2014-12-28 19:58:27Z

1

SELECT INTO OUTFILE is intended mainly to allow you quickly dump a table to a text file, and also to complement LOAD DATA INFILE.

I strongly suspect that the SELECT INTO OUTFILE is either adding an ASCII NULL, or other formatting between columns so that it can be read back.

You could compare binary output to determine if this is true, and also pick up upon any other formatting, encoding or shaping that may also be present.

Do you have to use INTO OUTFILE? - Can you not get the binary data and create a file directly with a script or other middle tier layer rather than relying on the database?

Update

After writing this, and reading the comment below. I thought about CONCAT, MySQL treats BLOB values as binary strings (byte strings), therefore in theory a simple concatenation of multiple columns into one single one might work.

If it doesn't then it wouldn't take much to write a simple pearl, PHP, C, bash or other scripting language to query the database, join two or more binary columns and write the output to a file on the system.

edited Dec 28, 2014 at 19:58

answered Dec 28, 2014 at 19:40

Steve

3,7031 gold badge21 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ken - Enough about Monica Over a year ago

I am using 'fields escaped by' because I had a problem like you described before. I was wondering if anything else can help me (x escaped by...). Do you mean an sql script to do it? Or another programming language?

Steve Over a year ago

@horsehair i've added an update - was a bit too large for a comment

Born2Code Over a year ago

concat would not work because it contacts columns and not rows. he wants the same column, in different rows, concatenated.

Steve Over a year ago

@Born2Code concat would work because you can easily join multiple rows together via sub query or a cross tabulation (pivot) query

Born2Code · Accepted Answer · 2014-12-28 20:23:11Z

1

what you need is to run the mysql command from the command line, using -e to pass a sql statement to execute, and the appropriate flags for the binary output.

You then use group_concat with an empty separator to concat all the data.

Finally, you output to your file. The only thing you need to be aware of is the built in limit on the group_concat, which you can change in your .ini file, or by setting a global variable.

Here is how would this look:

mysql --binary-mode -e "select group_concat(data separator '') from audio order by date, time;" -A -B -r -L -N YOUR_DATABASE_NAME > output

i just tried it with binary data (not audio) in a database and it works.

answered Dec 28, 2014 at 20:23

Born2Code

1,0756 silver badges13 bronze badges

4 Comments

Ken - Enough about Monica Over a year ago

this looks promising except the output is always 1025 long, though I verify through the mysql command line that the length of the 'data' fields are much greater than that.

Born2Code Over a year ago

that's the default for the group_concat_max_len, as i noted in my answer, you will need to increase this value, either in your .ini file or by setting the global variable @@group_concat_max_len see dev.mysql.com/doc/refman/5.1/en/… for more details

Born2Code Over a year ago

If it does then you got the wrong answer marked as correct :)

Ken - Enough about Monica Over a year ago

I wish I could mark them both. They both had the same result but in the end I did it with a Python script.

Collectives™ on Stack Overflow

Joining binary data from blobs in mysql

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related