如何在不丢失列数据的情况下更改MySQL表的列位置？
您可以借助ALTERTABLE命令更改MySQL表的列位置，而不会丢失数据。语法如下-
ALTER TABLE yourTableName MODIFY yourColumnName1 data type AFTER yourColumnName2;
为了理解上述概念，让我们创建一个表。创建具有一些列的表的查询如下-
mysql> create table changeColumnPositionDemo −> ( −> StudentId int, −> StudentAddress varchar(200), −> StudentAge int, −> StudentName varchar(200) −> );
让我们在表中插入一些数据。插入记录的查询如下-。
mysql> insert into changeColumnPositionDemo values(101,'US',23,'Johnson'); mysql> insert into changeColumnPositionDemo values(102,'UK',20,'John'); mysql> insert into changeColumnPositionDemo values(103,'US',22,'Carol'); mysql> insert into changeColumnPositionDemo values(104,'UK',19,'Sam');
现在，您可以在select语句的帮助下显示所有记录。查询如下-
mysql> select *from changeColumnPositionDemo;
以下是输出-
+-----------+----------------+------------+-------------+ | 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)
这是在不丢失数据的情况下更改列位置的查询。我们将“StudentAddress”列移到“StudentAge”列之后-
mysql> ALTER TABLE changeColumnPositionDemo MODIFY StudentAddress varchar(200) AFTER StudentAge; Records: 0 Duplicates: 0 Warnings: 0
上面我们在列名StudentAge之后设置了列StudentAddress。
以下是查询以检查以上两列是否已更改而不丢失数据的查询-
mysql> select *from changeColumnPositionDemo;
以下是输出-
+-----------+------------+----------------+-------------+ | 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)