Storing text in binary or visa versa
Posted: Thu Aug 14, 2008 2:03 am
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???
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???