Easy way to re-order columns in MySQL?

To re-order columns in MySQL, use the ALTER TABLE MODIFY COLUMN. The syntax is as follows -

ALTER TABLE yourTableName MODIFY COLUMN yourColumnName data type after yourColumnName.

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

mysql> create table reOrderColumn
-> (
-> ProductId int,
-> DeliveryDate datetime,
-> ProductName varchar(100)
-> );
Query OK, 0 rows affected (0.76 sec)

Now check the description of the table. The query is as follows.

mysql> desc reOrderColumn;

The following is the output.

+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ProductId | int(11) | YES | | NULL | |
| DeliveryDate | datetime | YES | | NULL | |
| ProductName | varchar(100) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.10 sec)

Now re-order the column using ALTER MODIFY command. I will reorder the DeliveryDate column after the ProductName column. The query is as follows.

mysql> alter table reOrderColumn modify column DeliveryDate datetime after ProductName;
Query OK, 0 rows affected (1.61 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us now check the column have been reordered or not. The query is as follows.

mysql> desc reOrderColumn;

The following is the output displaying the columns have been reordered.

+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ProductId | int(11) | YES | | NULL | |
| ProductName | varchar(100) | YES | | NULL | |
| DeliveryDate | datetime | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)