Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I convert from BLOB to TEXT in MySQL?

Tags:

sql

mysql

blobs

People also ask

How can I change BLOB data in MySQL?

BLOB Editor is invoked from data grid of any table editor or the result tab of SQL Editor and Visual Query Builder by double clicking of the BLOB field to be edited or with the Edit BLOB link of the field's popup menu. The editor also can be called from BLOB Viewer with the Edit current BLOB button.

What is difference between BLOB and TEXT in MySQL?

BLOB stands for Binary Large Objects and as its name suggests, it can be used for storing binary data while TEXT is used for storing large number of strings. BLOB can be used to store binary data that means we can store pictures, videos, sounds and programs also.


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


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.


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;

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