借助 alter table 命令，您可以更改 mysql 表的列位置而不会丢失数据。语法如下 -
alter table yourtablename modify yourcolumnname1 data type after yourcolumnname2;
为了理解上述概念，让我们创建一个表。创建包含某些列的表的查询如下 -
mysql> create table changecolumnpositiondemo−> (−> studentid int,−> studentaddress varchar(200),−> studentage int,−> studentname varchar(200)−> );query ok, 0 rows affected (0.72 sec)
让我们在表中插入一些数据。插入记录的查询如下-.
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)
现在您可以借助 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;query ok, 0 rows affected (2.27 sec)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)
以上就是如何改变mysql表的列位置而不丢失列数据？的详细内容。