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], ...;
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:
CREATE TABLE IF NOT EXISTS Products ( product_id int AUTO_INCREMENT PRIMARY KEY, product_name varchar(100) );
Second, we will add one column named
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
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
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.
Fourth, we will add two columns
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;
As you can see the
product_date column is populated with the default value which is
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.