CODEWITHSUNDEEP
sqlmysqlblobs
Jeffrey
Jeffrey

Reputation: 1691

MySQL Blob to Text (data and datatype)

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?

Upvotes: 2

Views: 4730

Answers (2)

Andrew
Andrew

Reputation: 716

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'

Upvotes: 1

John Boker
John Boker

Reputation: 83709

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)

Upvotes: 3

Related Questions