如何重新排列 MySQL 列？

我需要移动现有列的位置(为了更好的可见性)。

如何在不影响数据的情况下做到这一点？

52582 次浏览
小开

The only way I know is to change the column. You would first extract your column definition using SHOW CREATE TABLE and issue an ALTER TABLE:

ALTER TABLE foo
CHANGE COLUMN bar
bar COLUMN_DEFINITION_HERE
FIRST;

Or if you want it after a certain other column:

... AFTER OTHER_COLUMN;
小开
最佳答案

Modify also works. Have a look: 

ALTER TABLE foo MODIFY bar bartype AFTER baz;
小开

Here is the sql query:

ALTER TABLE table_name MODIFY COLUMN misplaced_column Column-definition AFTER other_column;

Here in Column-definition is full column definition. To see the column definition if you are using phpMyAdmin click on structure tab. Then click on change link on desired column. Then without modifying any thing click save. It will show you the sql. Copy the sql and just add AFTER other_column at the end.

If you like to bring the misplaced_column to the first position then:

ALTER TABLE table_name MODIFY COLUMN misplaced_column Column-definition FIRST;
小开
  1. Alter Table table_name modify column_name column_datatype first;
  2. Alter Table table_name modify column_name column_datatype After other_column_name;
小开

Based on @VKGS answer:

If your table called language is:

|      description    |         name     |
|---------------------|------------------|
|      fbi agent      |         jane     |
|---------------------|------------------|
|      mi6 agent      |         kurt     |
|---------------------|------------------|

And you want to make the column name the first column or before description, execute this:

ALTER TABLE language MODIFY description varchar(100) AFTER name;

Don't forget type of the column.

小开

Go to Structure and click Move Columns and then just drag the columns to rearrange.


提交答案