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

With the ALTER TABLE command, you can change the column positions of a MySQL table without losing data. 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 certain 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 the 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 moved the "StudentAddress" column after the "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 set the column StudentAddress after the column name StudentAge.

The following is the query to check whether the above two columns have been changed 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)

The above is the detailed content of How to change column position of MySQL table without losing column data?. For more information, please follow other related articles on the PHP Chinese website!