SQL Query to Alter Column Size in MySQL

In MySQL, managing the structure of a database is crucial for optimal data organization and integrity. One common task is altering the size of a column to accommodate changing the data requirements. The ALTER TABLE statement and the MODIFY clause provide a straightforward method to achieve this.

Here, we will show the process of modifying column sizes effectively. 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

ALTER TABLE table_name
MODIFY column_name
data_type(new_length);

where,

• table_name: The name of the table you want to alter.
• column_name: The column whose size you wish to change.
• data_type: The data type of the column (e.g., VARCHAR, INT, etc.).
• new_length: The new size you want to assign to the column.

Example: Increasing Column Size in MySQL

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

Step 1: Create a Sample Table

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);

Step 2: Check the Current Structure of the Table

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:

Step 3: Increase Column Size

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

Query:

ALTER TABLE student
MODIFY Name
varchar(50);

Step 4: Verify the Changes

To see the table structure, use Describe command:

DESCRIBE student;

Output:

Column size is altered from 20 to 50.

Example: Decreasing Column Size in MySQL

In some cases, you may need to reduce the size of a column to optimize storage or meet specific requirements. Let’s reduce the size of the Gender column from 11 to 9 characters.

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.

Conclusion

Altering the size of a column in MySQL is a simple process with the ALTER TABLE and MODIFY clauses. You’re either increasing or decreasing the column size, and these commands allow you to efficiently manage your database as your data requirements evolve. Always back up your data before making structural changes and carefully consider the impact of these alterations, especially when working with critical data.