How to Convert From BLOB to Text in MySQL?
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.
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.
CREATE DATABASE geeks;
Step 2: Use database
Use the below SQL statement to switch the database context to geeks:
Step 3: Table definition
We have demo_table in our geek’s database.
CREATE TABLE demo_table( NAME VARCHAR(20), AGE INT, CITY VARCHAR(20), FILE BLOB);
Step 4: Insert data into a table
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
SELECT * FROM demo_table;
We can see that content of the FILE column is in encoded format.
Step 6: Conversion from BLOB to TEXT.
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
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;
Please Login to comment...