How to Add Columns to a Table in MySQL
In this tutorial, you will learn how to add columns to a MySQL table using the MySQL ADD COLUMN statement, which allows you to add single or multiple columns in a single MySQL statement.
If you want to delete the existing columns, use the DELETE COLUMN statement explained here.
MySQL ADD COLUMN Syntax
To add columns to a table, you need to use the ALTER TABLE statement.
To add a column to the table, use the following syntax:
ALTER TABLE table_name
ADD [COLUMN] column_name column_definition [FIRST | AFTER existing_column];
- Table_name: Name of the table you want to alter
- Column_name: The column to add to the table
- If you want to add a new column at the first position of the table, use FIRST. If you want to add a new column after an existing column, use AFTER.
- If you do not explicitly specify the position of the new column, MySQL will add the new column to the last position of the table.
- If you do not explicitly specify the position of the new column, MySQL will add the new column to the last position of the table.
Remark
- It is not possible to add a new column with the same name as an existing column.
MySQL ADD COLUMN Examples
Suppose you have already created a table named "customers" that consists of three columns: id, customer_name, and customer_code.
| id | customer_name | customer_code |
Example 1: Add a column at the last position
In this example, I will show you how to add a column to the last position of the table.
The following statement adds a new column named address to the last position of the table named customers:
ALTER TABLE customers
ADD address VARCHAR(100) NOT NULL;
Example 2: Add a column at the first position
The following statement adds a new column named address at the 1st position of the table named customers:
ALTER TABLE customers
ADD address VARCHAR(100) NOT NULL FIRST;
Example 3: Add a column between the other two columns
You can also add a column in between the other two columns.
Now, suppose you want to add a new column named address between the customer_name and customer_code columns. You can write a statement as follows:
ALTER TABLE customers
ADD address VARCHAR(100) NOT NULL AFTER customer_name;
This statement will add a column named address after the customer_name column.
Example 4: Add multiple columns
In this example, I will show how we add multiple columns to a table.
The following statement adds two columns named address and mobile_phone to the customers table in the last position.
ALTER TABLE customers
ADD address VARCHAR(100) NOT NULL,
ADD mobile_phone VARCHAR(20) NULL;
The following statement adds columns named address at the first position and mobile_phone after the customer_name column to the "customers" table:
ALTER TABLE customers
ADD address VARCHAR(100) NOT NULL FIRST,
ADD mobile_phone VARCHAR(20) NULL AFTER customer_name;
Add a column with the same name
For example, suppose you have previously created a column named customer_name, and now you are trying to add a new column with the same name. MySQL will give you an error because a column with the same name already exists.
Learn more: Delete a column in MySQL
Summary
In this tutorial, you have learned how to add columns to a table using ADD COLUMN statement in MySQL. It is possible to add one or more columns in a single SQL statement. You can add a new column, at the last position, the first position, or between 2 other columns.