Adding new column after a specific column and defining a default in MySQL?

You need to follow some steps to add a new column after a specific column and defining default value. In order to achieve this, you need to use ALTER command. Let us first create a table −

mysql> create table DemoTable    (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentFirstName varchar(20),    StudentAge int,    StudentCountryName varchar(100)    ); Query OK, 0 rows affected (0.21 sec)

Let us check the description of table −

mysql> desc DemoTable;

This will produce the following output −

+--------------------+--------------+------+-----+---------+----------------+ | Field              | Type         | Null | Key | Default | Extra          | +--------------------+--------------+------+-----+---------+----------------+ | StudentId          | int(11)      | NO   | PRI | NULL    | auto_increment | | StudentFirstName   | varchar(20)  | YES  |     | NULL    |                | | StudentAge         | int(11)      | YES  |     | NULL    |                | | StudentCountryName | varchar(100) | YES  |     | NULL    |                | +--------------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.15 sec)

Following is the query to add a new column after a specific column and defining a default. Let us add a new column “StudentLastName” after column name “StudentFirstName”. The default value of StudentLastName column is “Doe”.

mysql> alter table DemoTable add StudentLastName varchar(20) NOT NULL after StudentFirstName; Query OK, 0 rows affected (0.91 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table DemoTable alter StudentLastName set default 'Doe'; Query OK, 0 rows affected (0.32 sec) Records: 0 Duplicates: 0 Warnings: 0

Let us check the description of table once again.

mysql> desc DemoTable;

This will produce the following output −

+--------------------+--------------+------+-----+---------+----------------+ | Field              | Type         | Null | Key | Default | Extra          | +--------------------+--------------+------+-----+---------+----------------+ | StudentId | int(11) | NO | PRI | NULL | auto_increment | | StudentFirstName | varchar(20) | YES | | NULL | | | StudentLastName | varchar(20) | NO | | Doe | | | StudentAge | int(11) | YES | | NULL | | | StudentCountryName | varchar(100) | YES | | NULL | | +--------------------+--------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)