MySQL : Change Datatype of a Column

This article will see how to change or modify the datatype of a column in MySQL.

Table of Contents

• Change datatype of a column without losing data: MODIFY
  • Change datatype of a column using Modify : Syntax
  • Change datatype of a column using Modify: Example
• Change datatype of a column without losing data: CHANGE
  • Change datatype of a column using Change : Syntax
  • Change datatype of a column using Change: Example

Let us get started by creating a table student_enroll_data followed by inserting some rows into it.

#create the 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)
);
#insert rows
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);

To view what got inserted into the table student_enroll_data execute:

SELECT * FROM student_enroll_data;

Output:-

Change datatype of a column without losing data: MODIFY

If we see the create table statement, the student_ssn_no column has datatype INT. In this section, we will be changing the datatype of the column student_ssn_no to BIGINT by using MODIFY.

Change datatype of a column using Modify : Syntax

Syntax:-

ALTER TABLE [tableName] MODIFY [columnName] [newDataType];

The syntax shows that we are using ALTER TABLE statement.

• tableName is the name of the table whose column we want to modify.
• columnName is the name of the column subject to datatype change.
• newDataType is the new datatype to be assigned.

Change datatype of a column using Modify : Example

Observe the below query to change the datatype of column student_ssn_no from INT to BIGINT.

ALTER TABLE student_enroll_data MODIFY student_ssn_no BIGINT;

Action Output Message Response:-

3 row(s) affected Records: 3 Duplicates: 0 Warnings: 0

Let us verify if the column’s datatype has been changed by executing:

SELECT 
    DATA_TYPE
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = 'student_enroll_data'
        AND COLUMN_NAME = 'student_ssn_no';

Action Output Message Response:-

1 row(s) returned.

Output:-

As we can see in image_2, the datatype of column student_ssn_no is now changed to bigint successfully. Also, the data is not lost. Verify the same by executing:

SELECT * FROM student_enroll_data;

In image_3, we can see that the data got preserved for column student_ssn_no.

Change datatype of a column without losing data: CHANGE

In this section, we will be changing the datatype of the column student_ssn_no to BIGINT from INT using CHANGE. Use the CHANGE clause when if you want to change the column’s name along with its definition. 

Change datatype of a column using Change : Syntax

Syntax:-

ALTER TABLE [tableName] CHANGE [columnName] [columnName] [newDataType];

The syntax shows that we are using ALTER TABLE statement.

• tableName is the name of the table whose column we want to modify.
• columnName is the name of the column subject to datatype change. columnName is present twice as it can be used to simultaneously rename the column and change its datatype.
• newDataType is the new datatype to be assigned.

Change datatype of a column using Change : Example

Observe the below query to change the datatype of column student_ssn_no from INT to BIGINT.

ALTER TABLE  student_enroll_data CHANGE student_ssn_no student_ssn_no BIGINT;

Action Output Message Response:-

3 row(s) affected Records: 3 Duplicates: 0 Warnings: 0

Let us verify if the column’s datatype has been changed by executing:

SELECT 
    DATA_TYPE
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = 'student_enroll_data'
        AND COLUMN_NAME = 'student_ssn_no';

Action Output Message Response:-

1 row(s) returned.

Output:-

As we can see in image_4, the datatype of column student_ssn_no is now changed to bigint successfully. Also, let us confirm if the data is not lost by executing:

SELECT * FROM student_enroll_data;

In image_5, we can see that the data got preserved for column student_ssn_no.

We hope this article helped you with changing the datatypes of columns. Good Luck!!!