MySQL ADD COLUMN Statement with Examples

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.

idcustomer_namecustomer_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.


See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment