How to change column position of MySQL table without losing column data?-Mysql Tutorial-php.cn

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

WBOY

Release： 2023-08-28 19:05:02

forward

1419 people have browsed it

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;

Copy after login

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
&minus;> (
&minus;> StudentId int,
&minus;> StudentAddress varchar(200),
&minus;> StudentAge int,
&minus;> StudentName varchar(200)
&minus;> );
Query OK, 0 rows affected (0.72 sec)

Copy after login

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

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

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

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

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

Copy after login

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

mysql> select *from changeColumnPositionDemo;

Copy after login

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)

Copy after login

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

Copy after login

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;

Copy after login

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)

Copy after login

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!