How to add column using alter in MySQL?

Following is the syntax to add column using alter in MySQL:

alter table yourTableName add column yourColumnName yourDataType default yourValue;

Let us first create a table:

mysql> create table alterTableDemo
   -> (
   -> Id int,
   -> Name varchar(10)
   -> );
Query OK, 0 rows affected (0.69 sec)

Let us check the description of the table using DESC command. This displays Field, Type, Key, etc. of the table:

mysql> desc alterTableDemo;

This will produce the following output

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Id    | int(11)     | YES  |     | NULL    |       |
| Name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Now, add column Age with default value 18. If user won’t supply value for column Age then MySQL will use the default value for Age column. Following is the query to add column using alter command.

mysql> alter table alterTableDemo add column Age int default 18;
Query OK, 0 rows affected (0.67 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us check the table description once again:

mysql> desc alterTableDemo;

This will produce the following output

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Id    | int(11)     | YES  |     | NULL    |       |
| Name  | varchar(10) | YES  |     | NULL    |       |
| Age   | int(11)     | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Let us insert record in the table using insert command.

Following is the query

mysql> insert into alterTableDemo(Id,Name,Age) values(100,'Chris',24);
Query OK, 1 row affected (0.16 sec)

mysql> insert into alterTableDemo(Id,Name) values(101,'Robert');
Query OK, 1 row affected (0.25 sec)

Following is the query to display all records from the table using select statement:

mysql> select *from alterTableDemo;

Following is the output. Since we haven’t set age for ‘Robert’, therefore the default 18 would be set for Age:

+------+--------+------+
| Id   | Name   | Age  |
+------+--------+------+
| 100  | Chris  | 24   |
| 101  | Robert | 18   |
+------+--------+------+
2 rows in set (0.00 sec)