Alter Table Statement in MySQL : How to Add Column in Table

MySQL Alter Table statement is used to add, modify, drop or delete column in a table you can also use MySQL Alter Table statement to rename the table.

In this article I will show you how to add column in a table.

ADD COLUMN IN TABLE

Syntax

Follow the below syntax to add a column in table.

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

Where;

• table_name : Name of the table to modify.
• new_column_name : Name of the new column to add to the table.
• column_definition : Data type and definition of the column such as NULL or NOT NULL.
• FIRST | AFTER column_name : This is optional, it tells where in the table to create the column. If this is not mentioned by default new column will be added to the end of the table.

Example

Let’s see how to add a column in a MySQL table using the Alter Table statement.

ALTER TABLE contacts
  ADD last_name varchar(40) NOT NULL
    AFTER contact_id;

In above example MySQL Alter TABLE will add a column called last_name to the contacts table. It will also create as a NOT NULL column and will appear after the contact_id field in the table

The last_name field will be created as a varchar(40) NOT NULL column and will appear after the contact_id column in the table. The first_name column will be created as a varchar(35) NULL column and will appear after the last_name column in the table.