Add Columns with Default Values to an Existing MySQL Table

While adding columns to an existing table with the help of ALTER command we can specify the default value also.

Syntax

Alter table table-name ADD (column-name datatype default data);

Example

In the example below, with the help of ALTER Command, column ‘City’ is added with default value ‘DELHI’ to the table ‘Student’.

mysql> Alter table Student ADD(City Varchar(10) Default 'DELHI');

Query OK, 5 rows affected (0.33 sec)
Records: 5 Duplicates: 0 Warnings: 0

Now from DESCRIBE command, we can check the default value of ‘City’ column.

mysql> describe Student\g

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| RollNO  | int(11)      | YES  |     | NULL    |       |
| Name    | varchar(20)  | YES  |     | NULL    |       |
| Class   | varchar(15)  | YES  |     | NULL    |       |
| Grade   | varchar(10)  | YES  |     | NULL    |       |
| Address | varchar(25)  | YES  |     | NULL    |       |
| Phone   | int(11)      | YES  |     | NULL    |       |
| Email   | varchar(20)  | YES  |     | NULL    |       |
| City    | varchar(10)  | YES  |     | DELHI   |       |
+---------+-------------+------+-----+---------+--------+

8 rows in set (0.04 sec)