Is it impossible to add a column in MySQL specifically before another column?


No, you can easily add a column before another column using ALTER.

Note − To add a column at a specific position within a table row, use FIRST or AFTER col_name Let us first create a table −

mysql> create table DemoTable
   -> (
   -> Id int,
   -> Name varchar(20),
   -> CountryName varchar(100)
   -> );
Query OK, 0 rows affected (0.67 sec)

Let us check all the column names from the table −

mysql> show columns from DemoTable;

This will produce the following output −

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

Following is the query to add a column before another column in MySQL.

mysql> alter table DemoTable add Age int AFTER Name;
Query OK, 0 rows affected (1.50 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us check all the column names from the above table once again −

mysql> show columns from DemoTable;

This will produce the following output. We have successfully added a column name −

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| Id          | int(11)      | YES  |     | NULL    |       |
| Name        | varchar(20)  | YES  |     | NULL    |       |
| Age         | int(11)      | YES  |     | NULL    |       |
| CountryName | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
raja
Published on 12-Jul-2019 10:36:32
Advertisements