Open In App

How to Convert From BLOB to Text in MySQL?

Improve
Improve
Like Article
Like
Save
Share
Report

In this article, we will see the conversion of a BLOB to TEXT in MySQL.

BLOB: It stands for Binary Large Object. It is a kind of data type in MySQL that can store files or images in the database in binary format.  It has four types i.e TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. All four types are similar, the only difference among them is the amount of data they can hold. 

AS the name suggests, LONGBLOB can hold the maximum amount of data and TINYBLOB can hold the least amount of data among the four types.

TEXT datatype in MySQL is used for storing long text strings in the database. It is just like VARCHAR. In order to convert BLOB to TEXT, we will use the CONVERT statement.

Syntax:

CONVERT( column_name using utf8);

utf8 is the way of encoding Unicode characters. It is recommended to use ut8 while creating web pages and databases. For demonstration, follow the below steps:

Step 1: Create a database

we can use the following command to create a database called geeks.

Query:

CREATE DATABASE geeks;

Step 2: Use database

Use the below SQL statement to switch the database context to geeks:

Query:

USE geeks;

Step 3: Table definition

We have demo_table in our geek’s database.

Query:

 CREATE TABLE demo_table(
   NAME VARCHAR(20),
   AGE INT,
  CITY VARCHAR(20),
   FILE BLOB);

Step 4: Insert data into a table

Query:

 INSERT INTO demo_table VALUES ('Romy', 21, 
 'Delhi', 'My name is romy kumari,
 I am 21 yrs old'),
('Pushkar', 22, 'Delhi', 
'My name is Pushkar jha,
 I am 22 yrs old'),
 ('Rinkle', 22, 'Punjab',
  'My name is Rinkle Arora,
  I am 22 yrs old'),
('Ayushi', 22, 'Patna', 'My name is
 Ayushi choudhary, I am 22 yrs old');

Step 5: View the content

Execute the below query to see the content of the table

Query:

SELECT * FROM demo_table;

Output:

We can see that content of the FILE column is in encoded format.

Step 6: Conversion from BLOB to TEXT.

Query:

SELECT convert(File using utf8)
from demo_table;

If you want to update the BLOB datatype column to the TEXT datatype column. Follow these steps:

  • Alter the table and add a column having data type TEXT.
  • Add content to that column after converting BLOB data to TEXT date.
  • Drop BLOB column.

Step 1: Add column

Syntax:

ALTER Table demo_table ADD 
COLUMN AFTER_CONERSION TEXT;

Step 2: Add content to column

 UPDATE demo_table SET AFTER_CONERSION 
 = CONVERT (FILE using utf8);

Step 3: Drop BLOB column

 ALTER TABLE demo_table 
 DROP COLUMN FILE;


Last Updated : 28 Nov, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads