MySQL Table – Add New Column

When you create a table, it is not always obvious then about the columns that you need. Change with the requirements and time may demand for a new column to be added to the table.

In this tutorial, a detailed process is provided in steps to add a new column to an existing MySQL Table.

Syntax – Add New Column

To insert a new column to to an existing MySQL Table, following is the syntax of the query:

</>
Copy
ALTER TABLE table_name
ADD [COLUMN] new_column_name column_definition [FIRST|AFTER existing_column];

where

  • table_name is the name of the MySQL table to which new column has to be added
  • mentioning [COLUMN] is optional
  • new_column_name is the name that you would like to provide for the new column you are adding
  • column_definition include data type of the column, characterset, default value, unique or not, etc.
  • [FIRST|LAST] use FIRST if you want to place the new_column_name before all other columns or AFTER if you want to place the new_column_name after existing_column.

Example 1 – Add Column to MySQL Table

In this example, we shall add a new column to the students table whose structure is given below:

Describe MySQL Table structure

Now we shall add a new column, named id which shall be the PRIMARY key and AUTO_INCREMENT for every new record.

We shall execute the following query to add id column to students table.

</>
Copy
 ALTER TABLE students ADD id INT AUTO_INCREMENT PRIMARY KEY;
MySQL add column to table

Let us see the students table structure.

MySQL after adding a new column to the table

Now, we shall see the contents of the table.

MySQL Table data with newly added column

Conclusion

In this MySQL Tutorial, we learned to add a new column to an existing MySQL Table.