I need to move the position of existing columns (for better visibility).
How can this be done without affecting the data?
Asked
Active
Viewed 5.7k times
78
-
6Not dupes: the first is about swapping data, the second is about "sqlyog". – P Shved May 29 '10 at 10:26
-
A save way is to use PHPMyAdmin's "Move Columns" feature: https://stackoverflow.com/a/34682789/1066234 – Avatar Oct 16 '20 at 05:36
-
Duplicate of https://stackoverflow.com/questions/6805426/how-to-move-columns-in-a-mysql-table – dolmen Dec 14 '21 at 11:03
6 Answers
91
Modify also works. Have a look:
ALTER TABLE table_name MODIFY column_name column_definition AFTER other_column;
The full definition of the column must be repeated exactly if you are just changing the order.
The best way to get the column_definition is to dump the table definition with SHOW CREATE TABLE table_name.
dolmen
- 8,126
- 5
- 40
- 42
vkGunasekaran
- 6,668
- 7
- 50
- 59
-
1Could anyone explain the difference between CHANGE COLUMN and MODIFY please? Is MODIFY a lighter weight operation? Does it not lock the table? Thanks. UPDATE: It looks a lot like CHANGE and MODIFY are synonyms, where MODIFY was added for Oracle users. https://dev.mysql.com/doc/refman/5.7/en/alter-table.html – jjohn Apr 21 '17 at 13:52
-
3This works but be aware that (on my setup at least) moving a column removes any auto_increment settings from that column. – Matt Saunders Oct 15 '18 at 13:50
-
4You will also lose your default value if you don't specify it in the alter statement. – Vincent Mar 28 '19 at 19:25
-
You must put the whole column definition including AUTOINCREMENT, NULL / NOT NULL,DEFAULT value, COMMENT... – dolmen Apr 06 '23 at 07:47
57
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;
soulmerge
- 73,842
- 19
- 118
- 155
-
1
-
9There is no BEFORE in MySQL syntax [link](http://dev.mysql.com/doc/refman/5.7/en/alter-table.html), only AFTER and FIRST – DocGono Jan 23 '15 at 11:09
-
6If you only want to reorder columns `ALTER TABLE ... MODIFY` is preferable as it doesn't ask you for column name again in the syntax format, which you can rename a column accidentally whereas `MODIFY` doesn't. – Devy Jul 16 '15 at 13:57
6
- Alter Table table_name modify column_name column_datatype first;
- Alter Table table_name modify column_name column_datatype After other_column_name;
sideshowbarker
- 81,827
- 26
- 193
- 197
shubham
- 69
- 1
- 1
-
This is not just `column_datatype` but the full column definition including collation, default value, comment... – dolmen Apr 06 '23 at 07:44
3
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 instead of description, execute this:
ALTER TABLE language MODIFY description varchar(100) AFTER name;
Don't forget type of the column.
JRichardsz
- 14,356
- 6
- 59
- 94
-
With modify you must put the whole column definition including `NULL / NOT NULL`,`DEFAULT value`, `COMMENT`... – dolmen Dec 14 '21 at 11:01
1
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;
-2
Go to Structure and click Move Columns and then just drag the columns to rearrange.
Nimantha
- 6,405
- 6
- 28
- 69
