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?

score 353 · Answer 1 · edited Nov 26 '16 at 13:50

353

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

edited Nov 26 '16 at 13:50

Peter Mortensen

30,738
21
105
131

answered Nov 19 '10 at 03:57

Yuma

3,539
1
14
2

30

Usage: `SELECT CONVERT(column USING utf8) FROM table;` – bmaupin Oct 02 '12 at 19:32
4

This works great for those GROUP_CONCATs that convert your output to blobs and you really want them as strings. I had an issue similar to the OP's while using Node.JS with the node-mysql library - this fixed all group_concat issues. – marksyzm Jul 26 '13 at 13:53
1

This work, and also can use with hot query like **CONVERT(LEFT(MD5([ID]),8) USING utf8)** – ZenithS Apr 28 '17 at 02:14
2

This won't work. The charset needs to be utf16 or else it will result in data loss if it encounters a set of bytes that can't be converted to utf8. It will replace those bytes with a ? character resulting in data loss. – Dean Or Aug 12 '19 at 20:21
1

`CONVERT(column USING utf8)` will return a column with the name `CONVERT(...)`. If one would like to avoid this, don't forget using `CONVERT(column USING utf8) AS column`. This will rename the column to `column`. – Parm Sep 18 '20 at 22:25

score 138 · Answer 2 · edited Jan 08 '19 at 22:45

138

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.

edited Jan 08 '19 at 22:45

Evan Conrad

3,993
4
28
46

answered Jun 04 '09 at 01:35

Ólafur Waage

68,817
22
142
198

6

Unfortunately this isnt working for me. I get empty rows and sometimes only a 1-character-output with strange symbols. – C4d Aug 06 '15 at 08:48
Worked in select query as well, select A.id,CAST(B.content AS CHAR(10000) CHARACTER SET utf8) as content, B.b from A join B ON B.content_id=A.content_id – dkb Oct 03 '16 at 11:59
Worked for me while trying to JSON_EXTRACT on a BLOB type column :) SELECT ... CAST(JSON_EXTRACT(column, '$.field') AS CHAR(10000) CHARACTER SET utf8) FROM ... – user1298923 Jan 24 '23 at 21:04

Asped · Answer 3 · 2014-03-12T11:21:08.393

18

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;

edited Mar 12 '14 at 11:21

answered Mar 12 '14 at 11:15

Asped

3,083
4
31
52

2

This is the only answer that worked for me, thanks:) – Tom Dec 04 '16 at 02:54
The intermediate column did the trick. Kept getting errors citing bad characters via all other methods and answers. Thanks – gillytech Feb 24 '19 at 20:45

score 13 · Answer 4 · answered Jan 14 '19 at 06:49

13

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:

answered Jan 14 '19 at 06:49

ankit

2,591
2
29
54

1

FYI: (So that people don't have to click on the picture above to see what this does): this shows ONE value at a time - doesn't solve the desire to see text for that column in all rows. – ToolmakerSteve Oct 23 '20 at 23:23

score 9 · Answer 5 · answered Aug 12 '19 at 20:33

9

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

answered Aug 12 '19 at 20:33

Dean Or

2,822
2
26
25

1

NOTE: `TO_BASE64` has a different purpose than the original post's question (OP). OP "knows" they have **text** stored into a blob field, and wants to see that text as text. (For that purpose, the **conversion to `utf16`** is appropriate - **if** the text was originally in `utf16`). `TO_BASE64` is a way to show an **arbitrary blob** using visible characters. It expands the original bytes, using a set of 64 characters. – ToolmakerSteve Oct 23 '20 at 23:29
MORE: If the data was originally in `utf8` (from whatever source it was received, then written into the blob), `utf16` will **not** do what you want - **you need to "know" what format the original text was in**, and use that in the `USING` clause. For example, *my general log table* stores queries that were `utf8`, `USING utf16` on `argument` column, resulted in a sequence of Asian characters, for simple queries like `SHOW SESSION VARIABLES`. In my case `USING utf8` was the correct answer. – ToolmakerSteve Oct 23 '20 at 23:39

score 8 · Answer 6 · edited Nov 26 '16 at 13:51

8

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.

edited Nov 26 '16 at 13:51

Peter Mortensen

30,738
21
105
131

answered Aug 21 '15 at 13:11

Jignesh Mesvaniya

390
5
6

marcosn · Answer 7 · 2020-04-20T07:43:39.660

3

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

edited Apr 20 '20 at 07:43

answered Apr 15 '20 at 12:38

marcosn

51
3

How exactly would someone go about doing this? Your answer will be much more helpful if you post some code and/or screenshots detailing such. – TrampolineTales Apr 15 '20 at 13:06

score 2 · Answer 8 · edited Nov 26 '16 at 13:50

2

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 ;

edited Nov 26 '16 at 13:50

Peter Mortensen

30,738
21
105
131

answered Jul 08 '13 at 15:32

Adam Sánchez Ayte

31
3

score 1 · Answer 9 · answered Oct 21 '21 at 15:15

1

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

select
uncompress(blobfield)
from table

answered Oct 21 '21 at 15:15

THOTH

109
1
12

score 0 · Answer 10 · answered Feb 22 '21 at 17:20

0

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.

answered Feb 22 '21 at 17:20

Kapidis

129
2
7

score -2 · Answer 11 · answered Oct 19 '22 at 11:31

-2

i cannot understand why not so easy:

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

and

BIN_TO_UUID(FIELD)

answered Oct 19 '22 at 11:31

JahStation

893
3
15
35

How do I convert from BLOB to TEXT in MySQL?

11 Answers11

Linked