How to Convert MySQL Table Field Type from BLOB to JSON?

In this article, we would be learning a MySQL query to convert a field of BLOB Data Type to JSON Data Type in a table. To execute this query, we would need to alter the table and subsequently the field’s definition. We would first need to use the ALTER TABLE command to start making changes to the table.

ALTER TABLE: ALTER TABLE is a command used to add, delete, or modify fields in an existing table. It is also used to add and drop various constraints on an existing table. We have two commands for alter table, MODIFY and CHANGE.

Step 1: Database Creation

Creation of a Database is done by using the CREATE DATABASE command in MySQL.

Query: 

CREATE DATABASE geek;

Step 2: Change Database in use, to one we created.

To use a particular database among multiple ones or to specify the database whose data we are interested in,we use the USE command in MySQL.

Query: 

USE geek;

Step 3: MySQL Table creation

We are going to create a table in MySQL where one field has BLOB values. Please note you can do this with multiple fields in a table.

Query: 

CREATE TABLE table1(ID INT,
DOCS BLOB,
REVIEW VARCHAR(100));

Step 4: Showing structure of the table

We will use the DESCRIBE table command to cross-check if our table has created a field with BLOB Data Type.

Query:

DESCRIBE table1;

Output:

Step 5: Changing field Data Type from BLOB to JSON. DOCS field would be changed as it is of the BLOB Data Type.

Method 1: USING MODIFY command

To change a field definition use MODIFY along with the ALTER command.

Query: 

ALTER TABLE table1
MODIFY DOCS JSON;

Method 2: USING CHANGE  

The syntax of the CHANGE command is different. After the CHANGE keyword, we name the field that has to be changed, then specify the new definition of the field, which includes the name of the changed field. Here we are changing the name of DOCS to DOCS_CHANGE.

Query:

ALTER TABLE table1 CHANGE  
DOCS DOCS_CHANGE JSON;

Step 6: Use the DESCRIBE command to see the changes made for each of the commands : MODIFY and CHANGE.

DESCRIBE table1;

Output: Results after using MODIFY

Output: Results after using CHANGE