MySQL ADD COLUMN Statement with Examples


How to Add Columns in a Table in MySQL

In this tutorial, we'll learn how to add columns to a table in MySQL using the MySQL ADD COLUMN statement. We can add a single or multiple columns in a single MySQL statement.

MySQL ADD COLUMN Syntax

To add a column to a table, we use the following statement:

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

  • table_name: The name of a table you want to alter.
  • column_name: A column that you to add to a table.
  • If you want to add a new column at the 1st position of a table, use FIRST. To add new column after the existing column, use AFTER.
  • MySQL adds a new column to the last position in the table if you don't
    explicitly specify the position of the new column.

Remark

  • You can't add a new column with the same name as the existing column.

MySQL ADD COLUMN Examples

Let's say we already created the following customers table with 3 columns, id, customer_name, and customer_code.

idcustomer_namecustomer_code

Example 1: Add a Column at the Last Position

In this example, I show you how to add a column the last position in a table.

The following statement adds new column named address to the customers table at the last position:

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:

ALTER TABLE customers
ADD address VARCHAR(100) NOT NULL FIRST;

Example 3: Add a Column between 2 Other Columns

In MySQL, we also can add a column between 2 other columns.

Now let's say we want to add a new column, named address, between the customer_name and customer_code columns. We can use the following statement:

ALTER TABLE customers
ADD address VARCHAR(100) NOT NULL AFTER customer_name;

This statement adds a column named address after the customer_name column. Since the customer_code column is located after the customer_name column, the address column will be located between the customer_code and customer_name columns.

Example 4: Add Multiple Columns

In this example, I show you how to add more than columns to a table a single statement.

The following statement adds 2 columns, named address and mobile_phone at the last position, to the customers table:

ALTER TABLE customers
ADD address VARCHAR(100) NOT NULL,
ADD mobile_phone VARCHAR(20) NULL;

The following statement adds a column, named address, at the 1st position and another column, named 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;

For example, let's say we already created a column named customer_name before, and now we're trying to add a new column with the same name. MySQL issues an error since the column with the same name already exists.

In this tutorial, we've learned how to add columns to a table in MySQL using the ADD COLUMN statement. MySQL allows us to add one or more columns in a single statement. We can add a column, at the last position, at the first position or between 2 other columns.

Leave a Reply

Your email address will not be published. Required fields are marked *