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?
-
2stackoverflow.com/questions/948174/…OMG Ponies– OMG Ponies2009-10-27 02:23:03 +00:00Commented Oct 27, 2009 at 2:23
Add a comment
|
2 Answers
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)
1 Comment
Daren Schwenke
I've gone from text to blob and back with no change in the data.
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'
