Can we alter order of columns in MySQL?

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)

AmitDiwan

Updated on 18-Dec-2019 05:38:19

• Related Questions & Answers
• How can we alter table to add MySQL virtual GENERATED COLUMNS?
• How can we alter table to add MySQL stored GENERATED COLUMNS?
• How can we use MySQL ALTER TABLE command for adding comments on columns?
• How can we alter a MySQL stored procedure?
• How can we alter a MySQL stored function?
• Can we use ADD and CHANGE with ALTER Statement in MySQL?
• How can we list all the columns of a MySQL view as we can list the columns of a MySQL table?
• Return order of MySQL SHOW COLUMNS?
• Can we use ORDER BY NULL in MySQL?
• How can we sort MySQL output in descending order?
• How can we sort MySQL output in ascending order?
• How can we change MySQL user password by using the ALTER USER statement?
• Can we use IFNULL along with MySQL ORDER BY?
• Can we order a MySQL result with mathematical operations?
• How to alter column type of multiple columns in a single MySQL query?