MySQL: Add new Column to a Table

In this tutorial, I want to show you some commands with which we can add a new column to an existing MySQL table.

The simplest command in this context is:

ALTER TABLE tab ADD col INT;

This command adds the column "col" to the table "tab". Here we have used the data type integer (INT). The data type, we can customize as desired, for example, here we are adding a VARCHAR column with a length of 200 characters to our table:

ALTER TABLE tab ADD col VARCHAR(200);

We can use any data type that is also otherwise available to us for columns in MySQL tables. We just have to write the data type directly after the name we have chosen for our new column.

Position of the new Column

In each of the first two examples, we are adding the new column as the last column of the existing table, because we didn't specify where to insert the column.

If we want to determine the position of the new column ourselves, we can specify the column that already exists in our table, behind which the new column is to be inserted. To do this, we simply write the existing column behind which the new column is to be inserted after AFTER:

ALTER TABLE tab ADD col INT(10) AFTER oldcol;

Of course, this procedure does not work for all positions: If the new column should be added at the very beginning of the table as the first column, we are using FIRST:

ALTER TABLE tab ADD col INT(10) FIRST;

With this we achieve several things: If there are already data records in our table at the time the new column is added, each data record receives the specified default value as value in the new column . At the same time, newly added data records that do not have an explicit value for our new column are automatically given the default value for this column.

Default Values

Sometimes we would also like to define a default value for the column. The next example set the default value of the new column "col" to 1:

ALTER TABLE tab ADD col INT DEFAULT(1);

Delete Column

And if we want to delete our new column again, there is still this command remaining for us:

ALTER TABLE tab DROP spalte;

This command irrevocably deletes the column specified after DROP together with all data and values stored in it.