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?

http://stackoverflow.com/questions/948174/how-do-i-convert-from-blob-to-text-in-mysql — OMG Ponies, Oct 27 '09 at 02:23

score 3 · Accepted Answer · answered Oct 27 '09 at 02:20

3

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)

answered Oct 27 '09 at 02:20

John Boker

82,559
17
97
130

I've gone from text to blob and back with no change in the data. – Daren Schwenke Oct 27 '09 at 02:31

score 1 · Answer 2 · answered May 23 '11 at 16:33

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:

Create temp field as text
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
Convert actual field to text datatype
Copy temp to actual field
Remove temp column

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

MySQL Blob to Text (data and datatype)

2 Answers2