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 | |
+----------------+---------+------+-----+---------+-------+
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!
