MySQL Alter Table Column

Last updated on July 26th, 2024 at 07:47 am

Many times you may need to modify column data type or rename column in MySQL. You can easily change data type of column or change column in MySQL using ALTER TABLE command. Here’s how to alter table column in MySQL.

MySQL Alter Table Column In MySQL

We will look at how to alter table column in MySQL using ALTER TABLE command with MODIFY clause.

Here’s the syntax of ALTER TABLE command to modify column in MySQL.

ALTER TABLE table_name
MODIFY column_name column_definition
[ FIRST | AFTER column_name];

In the above query, table_name is your MySQL database table name, column_name is the name of the column to be modified, and column_definition is the new data type of your column.

Let’s say you have the following orders table

mysql> create table orders(id int, order_date date);

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

Modify Column Data Type

Let’s say you want to change the data type of column order_date from date to datetime. Here’s the SQL query to change column data type.

mysql> alter table orders
       modify column order_date datetime;

mysql> describe orders;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| id         | int(11)  | YES  |     | NULL    |       |
| order_date | datetime | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+

Modify Multiple Columns in MySQL

If you want to modify multiple columns in MySQL, you need to use separate MODIFY clauses with ALTER TABLE.

Here’s the SQL query to modify both columns (id and order_date) of orders table.

mysql> alter table orders
       modify column id float,
       modify order_date date;

mysql> describe orders;
+------------+-------+------+-----+---------+-------+
| Field      | Type  | Null | Key | Default | Extra |
+------------+-------+------+-----+---------+-------+
| id         | float | YES  |     | NULL    |       |
| order_date | date  | YES  |     | NULL    |       |
+------------+-------+------+-----+---------+-------+

Rename Column in MySQL

Here’s the SQL query to change column name in MySQL. In this case, we use ALTER TABLE with CHANGE COLUMN clause to rename columns.

Here’s its syntax

ALTER TABLE table_name
    CHANGE COLUMN original_name new_name column_definition
    [FIRST | AFTER column_name];

In the above query, table_name is the name of the table whose column we want to change. original_name is the name is the old name of your column and new_name is its new name

Let’s say you want to rename order_date column to new_order_date

mysql> alter table orders
       change column order_date new_order_date date;

mysql> describe orders;
+----------------+---------+------+-----+---------+-------+
| Field          | Type    | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| id             | int(11) | YES  |     | NULL    |       |
| new_order_date | date    | YES  |     | NULL    |       |
+----------------+---------+------+-----+---------+-------+

Please note, ALTER TABLE statement is also used to rename table in MySQL. But this has slightly different syntax and a completely different result.

Hopefully, now you can easily alter table column in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.