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???
Storing text in binary or visa versa
Moderator: General Moderators
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
Re: Storing text in binary or visa versa
My first thought for storing binary data in a text column would be to base64 encode it before it's inserted.
Re: Storing text in binary or visa versa
So to avoid the scrambing, you specify the encoding when you're converting from binary into text.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...
Code: Select all
SET @binary_value = convert(@utf8_value USING BINARY);
SET @converted_back = convert(@binary_value USING utf8);