CODEWITHSUNDEEP
sqlmysqlblobs
Gwilym
Gwilym

Reputation:

How do I convert from BLOB to TEXT in MySQL?

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?

Upvotes: 294

Views: 523321

Answers (11)

JahStation
JahStation

Reputation: 917

i cannot understand why not so easy:

UUID_TO_BIN('77dea2ad-3c8c-40c6-a278-7cf1a1ac9384')

and

BIN_TO_UUID(FIELD)

Upvotes: -4

THOTH
THOTH

Reputation: 129

I had the same issue with my MariaDB records. It was solved (by my colleague) using

select
uncompress(blobfield)
from table

Upvotes: 1

Kapidis
Kapidis

Reputation: 137

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.

Upvotes: -1

marcosn
marcosn

Reputation: 71

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

Upvotes: 5

Dean Or
Dean Or

Reputation: 2911

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))

Upvotes: 9

ankit
ankit

Reputation: 2845

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:

screenshot

Upvotes: 16

Ólafur Waage
Ólafur Waage

Reputation: 69981

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.

Upvotes: 142

Jignesh Mesvaniya
Jignesh Mesvaniya

Reputation: 410

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.

Upvotes: 8

Adam Sánchez Ayte
Adam Sánchez Ayte

Reputation: 31

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 ;

Upvotes: 2

Yuma
Yuma

Reputation: 3709

That's unnecessary. Just use SELECT CONVERT(column USING utf8) FROM..... instead of just SELECT column FROM...

Upvotes: 370

Asped
Asped

Reputation: 3103

I have had the same problem, and here is my solution:

  1. create new columns of type text in the table for each blob column
  2. convert all the blobs to text and save them in the new columns
  3. remove the blob columns
  4. 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;

Upvotes: 19

Related Questions