How to Add Columns to a Table Using MySQL

In this tutorial, you will learn how do you create a new column in the MYSQL table using ADD COLUMN statement.

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

Let’s take a look at the SQL statement above.

The statement starts with ALTER TABLE clause by targeting the table name.
Then the column name is added after ADD COLUMN clause.
Finally, MySQL allows you to add a new column as the first column of the table by specifying the FIRST keyword. It also allows you to add a new column after an existing column using the AFTER existing_column clause. If you don’t explicitly specify the position of the new column, MySQL will add it as the last column.

Example

Let’s create a table named robots by using CREATE TABLE clause.

CREATE TABLE IF NOT EXISTS robots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

Now let’s take a look at, how to add a new column in the robots table right after the column name.

ALTER TABLE robots
ADD COLUMN active VARCHAR(15) AFTER name;

And in case you don’t want to mention the position of a new column skip AFTER clause, and SQL will add it at the end default.

In this tutorial, you have learned how to add a column to a table using MySQL ADD COLUMN statement.

Post Views: 601