MySQL Blob to Text (data and datatype)

Question

I have a mysql database that holds content as a blob, for whatever reason those developers chose to use a blob is out of my control. Is it possible to convert the data to text and the data type to text?

Answer 1

have you tried the alter table command ?

alter table mytable change mycolumn mycolumn text; 

from http://forums.mysql.com/read.php?103,164923,167648#msg-167648 it looks like you can use CAST.

you could create a new (TEXT) column, then fill it in with an update command:

update mytable set myNewColumn = CAST(myOldColumn AS CHAR(10000) CHARACTER SET utf8) 

Answer 2

Converting the field from blob to text truncates all characters > 127. In my case we have lots of european characters, so this was not an option. Here's what I did:

1. Create temp field as text
2. Copy the blob field to the temp field: UPDATE tbl SET col_temp = CONVERT(col USING latin1); In this case my blob held latin1 encoded chars
3. Convert actual field to text datatype
4. Copy temp to actual field
5. Remove temp column

Not exactly straightforward but it worked and no data loss. I'm using Version: '5.1.50-community'