How to change the column position of MySQL table without losing column data?

You can change the column position of MySQL table without losing data with the help of ALTER TABLE command. The syntax is as follows −

ALTER TABLE yourTableName MODIFY yourColumnName1 data type AFTER yourColumnName2;

To understand the above concept, let us create a table. The query to create a table with some columns is as follows −

mysql> create table changeColumnPositionDemo
−> (
−> StudentId int,
−> StudentAddress varchar(200),
−> StudentAge int,
−> StudentName varchar(200)
−> );
Query OK, 0 rows affected (0.72 sec)

Let us insert some data in the table. The query to insert records is as follows -.

mysql> insert into changeColumnPositionDemo values(101,'US',23,'Johnson');
Query OK, 1 row affected (0.13 sec)

mysql> insert into changeColumnPositionDemo values(102,'UK',20,'John');
Query OK, 1 row affected (0.19 sec)

mysql> insert into changeColumnPositionDemo values(103,'US',22,'Carol');
Query OK, 1 row affected (0.39 sec)

mysql> insert into changeColumnPositionDemo values(104,'UK',19,'Sam');
Query OK, 1 row affected (0.18 sec)

Now you can display all records with the help of select statement. The query is as follows −

mysql> select *from changeColumnPositionDemo;

The following is the output -

+-----------+----------------+------------+-------------+
| StudentId | StudentAddress | StudentAge | StudentName |
+-----------+----------------+------------+-------------+
|       101 | U              | 23         | Johnson     |
|       102 | UK             | 20         | John        |
|       103 | US             | 22         | Carol       |
|       104 | UK             | 19         | Sam         |
+-----------+----------------+------------+-------------+
4 rows in set (0.00 sec)

Here is the query to change the column position without losing data. We are shifting “StudentAddress” column to be after “StudentAge” column −

mysql> ALTER TABLE changeColumnPositionDemo MODIFY StudentAddress varchar(200) AFTER StudentAge;
Query OK, 0 rows affected (2.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

Above we have set the column StudentAddress after column name StudentAge.

The following is the query to check whether the above two columns have been changed or not without losing data −

mysql> select *from changeColumnPositionDemo;

The following is the output −

+-----------+------------+----------------+-------------+
| StudentId | StudentAge | StudentAddress | StudentName |
+-----------+------------+----------------+-------------+
|       101 | 23         | US             | Johnson     |
|       102 | 20         | UK             | John        |
|       103 | 22         | US             | Carol       |
|       104 | 19         | UK             | Sam         |
+-----------+------------+----------------+-------------+
4 rows in set (0.00 sec)