In MySQL, the BLOB (Binary Large Object) data type is used to store large amounts of binary data, such as images, files, or encrypted content. However, sometimes I need to convert BLOB data into readable text.

In this guide, I will show you how to retrieve and convert BLOB data into text using MySQL queries and PHP.

How to Convert From BLOB to Text in MySQL

 

Syntax:

CONVERT( column_name using utf8);

 

Conversion from BLOB to TEXT:

SELECT convert(File using utf8)
from users;

 

Check the BLOB Data:

First, I need to check what type of data is stored in the BLOB column. I can use the following SQL query.

SELECT id, BLOB_column FROM my_table LIMIT 5;

This will return raw binary data, which is unreadable in its current form.

 

Convert BLOB to Text Using CAST():

To convert a BLOB column into text format, I can use the CAST() function:

SELECT id, CAST(BLOB_column AS CHAR) AS text_data FROM my_table;

Alternatively, I can use the CONVERT() function:

SELECT id, CONVERT(BLOB_column USING utf8) AS text_data FROM my_table;

Both methods will convert the binary data into a readable text format.

 

Retrieve BLOB Data in PHP:

If I want to fetch and display BLOB data as text in a Laravel or PHP application, I can use the following PHP script:

<?php
$pdo = new PDO("mysql:host=localhost;dbname=my_database", "username", "password");
$query = $pdo->query("SELECT id, CONVERT(BLOB_column USING utf8) AS text_data FROM my_table");

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    echo "ID: " . $row['id'] . " - Data: " . $row['text_data'] . "<br>";
}
?>

This will display the BLOB data in a readable text format.

 

Update BLOB Column to TEXT (If Needed):

If I need to convert the BLOB column into a permanently TEXT column, I can use the following query.

ALTER TABLE my_table MODIFY COLUMN BLOB_column TEXT;

This will change the data type of the column from BLOB to TEXT, making it easier to store and retrieve text-based data.

 

---

You might also like:

• Read Also: How to Integrate Google Calendar in Laravel 11
• Read Also: How To Import CSV File In MySQL Using PHP
• Read Also: How To Get Last 15 Days Records In MySQL Query
• Read Also: Node.js Express CRUD Example With MySQL