Can we alter order of columns in MySQL?

MySQLMySQLi Database

Yes, we can change the order of columns. This can be done using ALTER command and AFTER to set the new order of an individual column. Let us first create a table −

mysql> create table DemoTable
   -> (
   -> `Student_Key_Age` int,
   -> `Student_Key_Name` varchar(20),
   -> `Student_Key_CountryName` varchar(20)
   -> );
Query OK, 0 rows affected (0.64 sec)

Following is the query to alter order of columns −

mysql> alter table DemoTable modify column `Student_Key_Age` int after `Student_Key_Name`;
Query OK, 0 rows affected (1.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us check the table description once again −

mysql> desc DemoTable;

This will produce the following output. As you can see the order of columns changed −

+-------------------------+-------------+------+-----+---------+-------+
| Field                   | Type        | Null | Key | Default | Extra |
+-------------------------+-------------+------+-----+---------+-------+
| Student_Key_Name        | varchar(20) | YES  |      | NULL   |       |
| Student_Key_Age         | int(11)     | YES  |      | NULL   |       |
| Student_Key_CountryName | varchar(20) | YES  |      | NULL   |       |
+-------------------------+-------------+------+-----+---------+-------+
3 rows in set (0.11 sec)
raja
Published on 18-Dec-2019 10:08:19
Advertisements