MySQL ADD COLUMN

Sometimes you may need to add columns to existing tables. Here’s how to add column in MySQL using MySQL ADD COLUMN statement.

Table of Contents

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!

How to find largest table in MySQL database?

How to calculate age from date of birth in SQL

How to do a bulk insert in MySQL?