MySQL increase VARCHAR size of column without breaking existing data

This article will see how to increase the size of a VARCHAR datatype column for a MySQL table. Also, we will ensure that we do not lose or break the column’s existing data.

Table of Content

Let us get started by making sample data. We will create a table named 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 BIGINT,
fee_submitted DECIMAL(10,2)
);
# inserting 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),
(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);

To get a snapshot of the table, student_enroll_data execute:

SELECT * FROM student_enroll_data;

Output:-

image_1: student_enroll_data

Note that in the table definition, the column student_name is of datatype VARCHAR with 50 characters.

DESC student_enroll_data;

Output:-

image_2

We will be increasing the size of column student_name to VARCHAR(250).

Syntax:-

ALTER TABLE tableName MODIFY [COLUMN] columnName VARCHAR(newSize) ;

ALTER statement MODIFY to increase column size

Observe the below ALTER query to increase the size of the VARCHAR column student_name to 250 characters.

ALTER TABLE student_enroll_data MODIFY COLUMN student_name VARCHAR(250) ;

Action Output Message:-

18:42:48 ALTER TABLE student_enroll_data MODIFY COLUMN student_name VARCHAR(250) 7 row(s) affected Records: 7 Duplicates: 0 Warnings: 0 0.025 sec

Verify if the size of the column is increased by executing:

DESC student_enroll_data;

Output:-

image_3

Output in image_3 shows that size of VARCHAR in column student_name has increased from 50 to 250 characters. Let us confirm if the data is preserved by executing:

SELECT * FROM student_enroll_data;

Output:-

image_4

Output in image_4 shows that the data is not lost.

ALTER statement CHANGE to increase column size:-

Alternatively, we can use the CHANGE clause as well to increase the size of a VARCHAR column. Observe the below query.

ALTER TABLE student_enroll_data CHANGE COLUMN student_name student_name VARCHAR(250) ;

Action Output Message:-

19:28:07 ALTER TABLE student_enroll_data CHANGE COLUMN student_name student_name VARCHAR(250) 7 row(s) affected Records: 7 Duplicates: 0 Warnings: 0 0.022 sec

Note: The column student_name is repeated twice as the CHANGE command is generally used to rename the column and modify its definition simultaneously. In case we want to rename the column, we can add the new name after the old name.

Verify if the size of VARCHAR column student_name is increased by executing:

DESC student_enroll_data;

Output:-

image_5

Also, the data is not lost. We will check the same by executing:

image_6
We hope this article helped with increasing the size of a VARCHAR column. Good Luck!!! 

You need to add a widget, row, or prebuilt layout before you’ll see anything here. 🙂

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top