如何在不丢失列数据的情况下更改 MySQL 表的列位置？

我希望在不丢失数据的情况下更改数据库表的列位置。

例如:

目前表:

+----+------+-------+----------+
| id | name | email | password |
+----+------+-------+----------+

到

+----+----------+------+-------+
| id | password | name | email |
+----+----------+------+-------+
105906 次浏览
小开
最佳答案

Try this:

ALTER TABLE table_name MODIFY password varchar(20) AFTER id
小开

Hearaman's answer is correct; but if you are using phpMyAdmin, there is a visual and practical way to do that.

  1. Open the table
  2. Choose the "Structure" tab
  3. Click on "Move columns"
  4. Drag and drop column names

Move columns link, middle of the Structure tab Move columns popup

小开

If you are using MySQL workbench,

  1. Right-click on table
  2. Alter table
  3. drag columns and re-order
  4. click apply and finish
小开

Also, you can do it like this:

ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type AFTER another_column_name;
小开

You can use modify/change keyword.

ALTER TABLE [table] CHANGE COLUMN [column] [column] [column definition] AFTER [column]


ALTER TABLE [table] MODIFY COLUMN [column] [column definition] AFTER [column]

Eg:

ALTER TABLE table_name MODIFY password varchar(20) AFTER id


ALTER TABLE table_name MODIFY password varchar(20) varchar(20) AFTER id
小开

If you are like me, using MySQL workbench and got lost trying to implement @Mohemmed Niyaz answer, you can follow these steps,

  • On your top left, click Schemas
  • find and select your database, then find and select the table you want to modify
  • When you hover over the table name you would see three icons, click on settings (that is the one in the middle) or you can right-click and select Alter Table ,
  • then click on the column and drag it to the position you want your column to be.
  • on your bottom right you will see apply (click and apply🙂).

提交答案