How do I convert from BLOB to TEXT in MySQL

Question

I have a whole lot of records where text has been stored in a blob in MySQL  For ease of handling I d like to change the format in the database to TEXT    Any ideas how easily to make the change so as not to interrupt the data - I guess it will need to be encoded properly

User · Answer

Here s an example of a person who wants to convert a blob to char 1000  with UTF-8 encoding   CAST a ar options AS CHAR 10000  CHARACTER SET utf8    This is his answer  There is probably much more you can read about CAST right here  I hope it helps some

User · Answer

None of these answers worked for me  When converting to UTF8  when the encoder encounters a set of bytes it can t convert to UTF8 it will result in a   substitution which results in data loss  You need to use UTF16   SELECT     blobfield      CONVERT blobfield USING utf16       CONVERT CONVERT blobfield USING utf16   BINARY       CAST blobfield  AS CHAR 10000  CHARACTER SET utf16       CAST CAST blobfield  AS CHAR 10000  CHARACTER SET utf16  AS BINARY    You can inspect the binary values in MySQL Workbench  Right click on the field -  Open Value in Viewer-  Binary  When converted back to BINARY the binary values should be the same as the original   Alternatively  you can just use base-64 which was made for this purpose   SELECT     blobfield      TO BASE64 blobfield       FROM BASE64 TO BASE64 blobfield

User · Answer

If you are using MYSQL-WORKBENCH  then you can select blob column normally and right click on column and click open value in editor  refer screenshot

User · Answer

Or you can use this function   DELIMITER     CREATE FUNCTION BLOB2TXT  blobfield VARCHAR 255   RETURNS longtext DETERMINISTIC NO SQL BEGIN        RETURN CAST blobfield AS CHAR 10000  CHARACTER SET utf8   END      DELIMITER

User · Answer

SELECCT TO BASE64 blobfield    FROM the Table  worked for me  The CAST blobfield AS CHAR 10000  CHARACTER SET utf8  and CAST blobfield  AS CHAR 10000  CHARACTER SET utf16  did not show me the text value I wanted to get

User · Answer

I have had the same problem  and here is my solution     create new columns of type text in the table for each blob column convert all the blobs to text and save them in the new columns remove the blob columns rename the new columns to the names of the removed ones    ALTER TABLE mytable ADD COLUMN field1 new TEXT NOT NULL  ADD COLUMN field2 new TEXT NOT NULL   update mytable set field1 new   CONVERT field1 USING utf8   field2 new   CONVERT field2 USING utf8    alter table mytable drop column field1  drop column field2   alter table mytable change column field1 new field1 text  change column field2 new field2 text

User · Answer

That s unnecessary  Just use SELECT CONVERT column USING utf8  FROM      instead of just SELECT column FROM

User · Answer

You can do it very easily   ALTER TABLE  table name  CHANGE COLUMN  column name   column name  LONGTEXT NULL DEFAULT NULL     The above query worked for me  I hope it helps you too

User · Answer

Using phpMyAdmin you can also set the options to show BLOB content and show complete text

[sql] How do I convert from BLOB to TEXT in MySQL?

Examples related to sql

Examples related to mysql

Examples related to blobs