SQL Query to Alter Column Size in MySQL

To Alter the Column size in MySQL use the ALTER TABLE statement with MODIFY Clause.

The ALTER TABLE statement allows users to add, delete, or modify columns in an existing table in MySQL.

Syntax to Alter column size in MySQL is:

ALTER TABLE table_name
MODIFY column_name
varchar(new_length);

Let us look at some examples on how to alter column size in MySQL.

First, we will create a demo table on which we will alter the column length.

Let’s create a table with table_name; “student”

-- create a table
CREATE TABLE students (Sr_No integer,Name varchar(20), Gender integer);

DESCRIBE is used to describe something. Since in database, we have tables, that’s why we use DESCRIBE or DESC(both are the same) commands to describe the structure of a table.

Query:

DESCRIBE student;
 OR
DESC student;

Using this command for the above table (in Xampp);

Output:

Now change the size of the column using ALTER TABLE command with MODIFY clause.

Query:

ALTER TABLE student
MODIFY Name
varchar(50);

To see the table structure, use Describe command:

DESCRIBE student;

Output:

Column size is altered from 20 to 50.

Decrease column size in MySQL example

In this example, we will reduce the column size of “Gender” column.

Query:

ALTER TABLE student
MODIFY Gender
varchar(9);

To see the table structure, use Describe command:

DESCRIBE student;

Output:

Column size is altered from 11 to 9.