Storing text in binary or visa versa

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Storing text in binary or visa versa

Post by alex.barylski »

I need to store some binary data in an UTF text field...and the field needs to stay a TEXT field because I need FULLTEXT support.

Is it maybe possible to reverse this process and store the TEXT as BINARY data and instead of relying FULLTEXT I could implement a stored procedure which would go through the BLOB fields convert the binary data to TEXT and then perform a FULLTEXT search?

Can you access FULLTEXT from within stored procedures?

Can you convert one to the other without data loss?

I can see converting TEXT to BINARY no problems...but BINARY to TEXT might because of character encoding. If the BINARY BYTE is beyond ASCII (which is what I understand UTF supports natively?) then I assume it would become two bytes in order to be represented in UTF and when that data was converted back again...the one BYTE would now be two and the binary data would be scrambled...

I'm wondering if I could maybe use pack/unpack to convert a binary data into an array, serialize it and store it that way???
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Storing text in binary or visa versa

Post by onion2k »

My first thought for storing binary data in a text column would be to base64 encode it before it's inserted.
dml
Forum Contributor
Posts: 133
Joined: Sat Jan 26, 2008 2:20 pm

Re: Storing text in binary or visa versa

Post by dml »

Hockey wrote: I can see converting TEXT to BINARY no problems...but BINARY to TEXT might because of character encoding. If the BINARY BYTE is beyond ASCII (which is what I understand UTF supports natively?) then I assume it would become two bytes in order to be represented in UTF and when that data was converted back again...the one BYTE would now be two and the binary data would be scrambled...
So to avoid the scrambing, you specify the encoding when you're converting from binary into text.

Code: Select all

SET @binary_value = convert(@utf8_value USING BINARY);
SET @converted_back = convert(@binary_value USING utf8);
But why are you reluctant to use a TEXT field for storing textual data?
Post Reply