MySQL ADD Column

Summary: in this tutorial, you will learn how to add one or more columns to an existing table using the MySQL ADD COLUMN statement.

MySQL allows us to add a column to an existing table using the MySQL ALTER TABLE ADD COLUMN statement. You can also add more than one column to a table using this statement.

MySQL ADD COLUMN Syntax

The below syntax is to add a column to a table in MySQL.

ALTER TABLE table_name 
 ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];

Let’s understand the above syntax in more detail.

First, you specify the name of the column after the ALTER TABLE keywords.

Second, specify the name of the new column along with the column definition after the ADD COLUMN keywords. Here, the COLUMN keyword is optional, you can omit it if you want.

Third, optionally, you can specify the position of the new column. That means after which column you want to add the new column. You can use FIRST to insert the new column in the first position. Also, you can specify the name column after the AFTER keyword if you want to insert the new column after a specific column. By default, MySQL adds a column to the last position.

To add more than one column, you use the following 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],
...;

Here, each ADD COLUMN statement is separated by a comma(,).

MySQL ADD COLUMN Example

Let’s understand the concept of MySQL ADD COLUMN with some examples to understand the topic in depth.

First, we will create a table named Products with two columns: product_id and product_name.

CREATE TABLE IF NOT EXISTS Products (
 product_id int AUTO_INCREMENT PRIMARY KEY,
 product_name varchar(100)
);

Second, we will add one column named product_price.

ALTER TABLE Products 
 ADD COLUMN product_price decimal(10,2) NOT NULL;

Here, we haven’t mentioned the position of the new column. So MySQL will add it to the last.

Third, we add a new column product_description.

ALTER TABLE Products
 ADD COLUMN product_description varchar(255) AFTER product_name;

In the above statement, we have added a new column product_description after the product_name column.

Now insert some records into the Products table.

INSERT INTO Products (product_name, product_description, product_price)
 VALUES('Keyboard','Wireless keyboard',20),
       ('Mouse','Optical Mouse',8.5);

We can check the Products table using the SELECT statement to see the changes.

MySQL add column

Fourth, we will add two columns product_stock and product_date to the Products table using the single statement.

ALTER TABLE Products 
  ADD COLUMN product_stock int DEFAULT '0',
  ADD COLUMN product_date date NOT NULL;

Please note that the column product_date is defined as NOT NULL  and we haven’t provided any default value for that. However, the Products table already has data. In this case, MySQL adds the column using default values.

Now we can check the Products table again.

SELECT * FROM Products;

Output:

mysql add multiple column

As you can see the product_date column is populated with the default value which is 0000-00-00.

The column you are adding to the table must not exist otherwise MySQL will issue an error. For example, we try to add the column product_stock which already exist in the table.

ALTER TABLE Products 
ADD COLUMN product_stock int DEFAULT '0';

MySQL will throw an error as below.

MySQL add column error