How to add columns at specific position in existing table in MySQL?

To add columns at a specific position in existing table, use after command. The syntax is as follows −

ALTER TABLE yourTableName
ADD COLUMN yourColumnName data type AFTER yourExistingColumnName;

To understand the above syntax, let us first create a table. The query to create a table is as follows.

mysql> create table changeColumnPosition
-> (
-> Id_Position1 int,
-> Name_Position2 varchar(100),
-> Address_Position4 varchar(200)
-> );
Query OK, 0 rows affected (0.53 sec)

Now you can check the description of existing table using desc command. The syntax is as follows −

desc yourTableName;

The following is the query to check the description.

mysql> desc changeColumnPosition;

The following is the output.

+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| Id_Position1      | int(11)      | YES  |     | NULL    |       |
| Name_Position2    | varchar(100) | YES  |     | NULL    |       |
| Address_Position4 | varchar(200) | YES  |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

I have three columns and I want to add another column after Name_Position2 Field. The query is as follows.

mysql> alter table changeColumnPosition
-> add Age_Position3 int after Name_Position2;
Query OK, 0 rows affected (1.34 sec)
Records: 0 Duplicates: 0 Warnings: 0

We added column Age_Position3 successfully after the Name_Position2. Check the existing table again now for the changes we did above.

mysql> desc changeColumnPosition;

The following is the output.

+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| Id_Position1      | int(11)      | YES  |     | NULL    |       |
| Name_Position2    | varchar(100) | YES  |     | NULL    |       |
| Age_Position3     | int(11)      | YES  |     | NULL    |       |
| Address_Position4 | varchar(200) | YES  |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)