MySQL: change column type to VARCHAR without losing data

This article will see how to convert the column type to VARCHAR without losing the data.

Table of Contents:

• Change column type to VARCHAR without losing data : MODIFY clause
• Change column type to VARCHAR without losing data : CHANGE clause

We will get started by creating a sample table student_enroll_data.

CREATE TABLE student_enroll_data (
student_id INT,
student_name VARCHAR(50),
enroll_date DATE,
student_ssn_no INT,
fee_submitted DECIMAL(10,2)
);

Let us insert some data as well to the table student_enroll_data.

INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) 
VALUES(1,"Daniel",'2021-12-12',1147483782,12378.90),
(2,"Sandy",'2021-10-12',1147483788,14578.90),
(3,"Ryma",'2021-11-22',1147483789,22378.90),
(4,"Jennifer",'2021-12-02',1147483790,12378.90),
(5,"Austin",'2021-11-12',1147483791,12378.90),
(6,"George",'2021-10-10',1147483792,12788.90),
(7,"Veronica",'2021-02-13',1147483793,12378.90);

View the snapshot of the table data by executing:

SELECT * FROM student_enroll_data;

Output:-

Change column type to VARCHAR without losing data : MODIFY clause

The below query is to modify the data type of column student_ssn_no from INT to VARCHAR.

ALTER TABLE student_enroll_data MODIFY student_ssn_no VARCHAR(50);

Action Output:-

Let us run the below query to confirm if the type is changed.

SELECT 
   COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = 'student_enroll_data';

Output:-

Output in image_3 shows that the datatype of student_ssn_no is now varchar with size 50. Let us verify if the data is preserved as well by executing:

SELECT * FROM student_enroll_data;

Output:-

Output in image_4 shows that the data is not lost.

READ MORE THROUGH: MySQL : Change Datatype of a Column

Change column type to VARCHAR without losing data : CHANGE clause

Observe the table definition below for the table student_enroll_data by executing:

 DESC student_enroll_data;

Output:-

Also, see the data in the table student_enroll_data by executing:

SELECT * FROM student_enroll_data;

Output:-

The below query is to change the datatype of column student_ssn_no from INT to VARCHAR.

ALTER TABLE student_enroll_data CHANGE student_ssn_no student_ssn_no VARCHAR(50);

Action Output:-

Verify the type of the column student_ssn_no by executing:

 DESC student_enroll_data;

Output:-

SELECT * FROM student_enroll_data;

Output:-

Output in image_9 shows that data is not lost.

We hope this article helped in changing the column data type to VARCHAR. Good Luck !!!