Sometimes you may need to add columns to existing tables. Here’s how to add column in MySQL using MySQL ADD COLUMN statement.
MySQL ADD COLUMN
Here are the steps to add column to existing MySQL table using MySQL ADD COLUMN query.
Here’s the syntax of MySQL ADD COLUMN statement.
ALTER TABLE table ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];
In the above query, mention table name in place of table after ALTER TABLE
In the second line, COLUMN is an optional keyword. You need to specify new column name in place of column_name and its definition, that is, data type, in place of column_definition
Finally, you can also specify the position of your new column using optional arguments such as FIRST, or AFTER column_name where you specify after which column you want to add the new column.
Bonus Read : MySQL DROP UNIQUE CONSTRAINT
MySQL Add Multiple Columns
If you want to add multiple columns in MySQL, you need to specify separate ADD COLUMN statements for each new column. Here’s the syntax
ALTER TABLE table ADD [COLUMN] column_name_1 column_1_definition [FIRST|AFTER existing_column], ADD [COLUMN] column_name_2 column_2_definition [FIRST|AFTER existing_column], ...;
Bonus Read : MySQL DROP INDEX
MySQL ADD COLUMN Examples
Here’s an example of how to add column in MySQL. Let’s say you have the following table orders.
mysql> create table orders(order_id int, amount int);
Let’s say you want to add column product to this table.
mysql> alter table orders
add column product varchar(255);
mysql> describe orders;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| order_id | int(11) | YES | | NULL | |
| amount | int(11) | YES | | NULL | |
| product | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
Bonus Read : MySQL DROP FOREIGN KEY CONSTRAINT
Let’s say you want to add a new column category after order_id
mysql> alter table orders
add column category varchar(255) after order_id;
mysql> describe orders;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| order_id | int(11) | YES | | NULL | |
| category | varchar(255) | YES | | NULL | |
| amount | int(11) | YES | | NULL | |
| product | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
Bonus Read : MySQL DROP DATABASE
MySQL ADD COLUMN with Default Value
In the above examples, the new columns assume default value of null, since we have not specified it. Here’s an example of how to add column with default value, using DEFAULT keyword. Let’s say you want to add new column transactions with default value 0.
mysql> alter table orders
add column transactions int default 0;
mysql> describe orders;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| order_id | int(11) | YES | | NULL | |
| category | varchar(255) | YES | | NULL | |
| amount | int(11) | YES | | NULL | |
| product | varchar(255) | YES | | NULL | |
| transactions | int(11) | YES | | 0 | |
+--------------+--------------+------+-----+---------+-------+
Let’s say you want to add multiple columns vendor and company. Here’s the query to add multiple columns in MySQL table.
mysql> alter table orders
add column vendor varchar(255),
add column company varchar(255);
mysql> describe orders;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| order_id | int(11) | YES | | NULL | |
| category | varchar(255) | YES | | NULL | |
| amount | int(11) | YES | | NULL | |
| product | varchar(255) | YES | | NULL | |
| transactions | int(11) | YES | | 0 | |
| vendor | varchar(255) | YES | | NULL | |
| company | varchar(255) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
