How to Change Column Size in MySQL

Last updated on August 21st, 2024 at 05:05 am

Sometimes you may need to change column size or change field length in MySQL. In this article, we will look at how to change column size in MySQL. You can use these commands to increase column size in MySQL or decrease it.

How to Change Column Size in MySQL

Here is how to increase field length in MySQL. Let us say you have a VARCHAR column with length 20, and want to increase its length to 255.

In this case, you need to use ALTER TABLE statement along with MODIFY clause to increase column size.

Here is the syntax for it

ALTER TABLE table_name
MODIFY column_name
varchar(new_length);

In the above command, you need to specify table_name whose column you want to modify, column_name of column whose length you want to change, and new_length, new size number.

Also read : How to Store JSON data in MySQL

Here is an example to increase column size in MySQL. Let us say you have a table sales with column product_name of length 20 characters.

mysql> create table sales(
       id int, 
       product_name varchar(20), 
       order_date date
       );
mysql> describe sales;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | YES  |     | NULL    |       |
| product_name | varchar(20) | YES  |     | NULL    |       |
| order_date   | date        | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

Also read : How to Compare Null Values in MySQL

Increase Column Size in MySQL

Let us increase size of product_name from varchar(20) to varchar(255). Here is the SQL query to do this.

mysql> alter table sales
       modify product_name varchar(255);

mysql> describe sales;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | YES  |     | NULL    |       |
| product_name | varchar(255) | YES  |     | NULL    |       |
| order_date   | date         | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

In the above query, we mention the new column type varchar(20) after column name in the SQL query. We also use DESCRIBE TABLE statement to get a list of column names.

Decrease Column Size in MySQL

On the other hand, if you want to decrease the column size, you still need to use the ALTER TABLE…MODIFY COLUMN statement.

Here is the SQL query to reduce column size of product_name from 20 to 10.

mysql> alter table sales
       modify product_name varchar(10);

mysql> describe sales;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | YES  |     | NULL    |       |
| product_name | varchar(10)  | YES  |     | NULL    |       |
| order_date   | date         | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

In this article, we have learnt how to increase as well as decrease column size in MySQL. In both cases, you need to use ALTER TABLE…MODIFY statement and specify the new full length. Hopefully, this article will help you change column size in MySQL.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.