MySQL How to Add a Column - alter table

Adding a column to a table in a MySQL database is easy. We’re going to show you what you need to know here.

This is the syntax:


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

Basic example of adding a column:


ALTER TABLE food
ADD COLUMN flavor_code INT;

You can leave out the “COLUMN” keyword if you want:


ALTER TABLE food
ADD flavor_code INT;

If the position is not specified the column will be placed last, after all other columns.

Add a column, specify the position after another column:


ALTER TABLE food
ADD COLUMN texture VARCHAR(15) AFTER name;

Add a column, specify the position as the first column:


ALTER TABLE food
ADD COLUMN texture VARCHAR(15) FIRST

If you use “NOT NULL” existing rows will be populated with default values:


ALTER TABLE food
ADD COLUMN flavor_code INT NOT NULL;

Add two columns at once:


ALTER TABLE food
ADD COLUMN description VARCHAR(100) NOT NULL,
ADD COLUMN calorie_code decimal(10,2) NOT NULL;

You will receive an error if the column that you are trying to add already exists.

Video Instructions

Adding a column in MySQL is really easy.

Here is an example assuming that you have a table named “employees”.

You could add a column like this:



ALTER TABLE employees ADD email VARCHAR(255);

Or like this:



ALTER TABLE employees ADD department VARCHAR(255);

You can also set the column to NOT NULL or add a default value:



ALTER TABLE employees  ADD salary DECIMAL(10, 2) DEFAULT 0.00 NOT NULL;