Easy way to re-order columns?

Question

I'm working on a database. On most of the tables, the column order is not what I would expect, and I would like to change it (I have the permission). For example, the primary_key's id columns are rarely the first column!

Is there an easy method of moving columns with phpMyAdmin?

score 54 · Accepted Answer · answered Nov 04 '10 at 10:01

54

Use an ALTER TABLE ... MODIFY COLUMN statement.

ALTER TABLE table_name MODIFY COLUMN misplaced_column INT(11) AFTER other_column;

answered Nov 04 '10 at 10:01

Eran Galperin

86,251
24
115
132

do i need the col type info? - INT(11) – Haroldo Nov 04 '10 at 10:03
1

Yes, you need to specify the column type – Eran Galperin Nov 04 '10 at 10:03
1

What if the misplaced_column is not of type INT(11). I am trying to reorder a DATETIME. I tried replacing "INT(11)" with "DATETIME" but it did not work. – skcin7 Aug 22 '12 at 22:42
Yeah I'm getting ERROR 1064 on `ALTER TABLE participant COLUMN last_update DATETIME AFTER home_state` ... but `MODIFY` worked https://stackoverflow.com/a/10256880/3018212 – Eric D. Johnson Feb 19 '18 at 19:23

score 6 · Answer 2 · answered Dec 17 '12 at 15:34

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 withour modifyig any things click save. It will show you the sql. Copy the sql and just add *AFTER other_column* at the end. It will be all.

If you like to bring the *misplaced_column* to the first position then ALTER TABLE table_name MODIFY COLUMN misplaced_column Column-definition FIRST;

This is perfect and even allows for the movement of a `datetime` column. Thanks @zahid9i — H. Ferrence, Jun 26 '13 at 12:44

score 5 · Answer 3 · edited Jul 20 '16 at 22:45

5

Since you mention phpMyAdmin, there is now a way to reorder columns in the most recent version (4.0 and up).

Go to the "Structure" view for a table, click the Edit (or Change) button on the appropriate field, then under "Move column" select where you would like the field to go.

edited Jul 20 '16 at 22:45

Pikamander2

7,332
3
48
69

answered Jul 22 '13 at 18:47

DisgruntledGoat

70,219
68
205
290

There is no option like that. – Foreever Sep 19 '14 at 10:47
@Foreever what version are you using? – DisgruntledGoat Sep 19 '14 at 13:25
Well there's your problem. You need to download the latest version. – DisgruntledGoat Sep 22 '14 at 14:56

score 4 · Answer 4 · answered Nov 04 '10 at 10:13

4

ALTER TABLE `table`
CHANGE COLUMN `field` `field` 
INT(11) AFTER `field2`;

answered Nov 04 '10 at 10:13

Alex Pliutau

21,392
27
113
143

Dee · Answer 5 · 2014-12-04T10:31:09.093

2

In phpMyAdmin version 3.5.5, go to the "Browse" tab, and drag the columns to your preferred location to reorder (e.g. if you have columns named A,B,C, all you need to do is drag column C between A and B to reorder it as A,C,B).

edited Dec 04 '14 at 10:31

answered Dec 02 '14 at 17:58

Dee

15
3

score 1 · Answer 6 · answered Nov 04 '10 at 10:04

1

Another approach is to:

#CREATE TABLE original (
#    id INT
#    name TEXT
#    etc...
#);

CREATE TABLE temp (
    name TEXT
    id INT
    etc...
);

INSERT INTO temp SELECT name, id FROM original;

DROP TABLE original;

RENAME TABLE temp TO original;

answered Nov 04 '10 at 10:04

Petah

45,477
28
157
213

Only if you want to do other modifications, such as types, names, order etc all in one go – Petah Nov 05 '10 at 10:20
Why would you copy all your data around like this? When you don't need to? It's inefficient and you're just asking for trouble. – Lightness Races in Orbit Aug 14 '11 at 14:18
@Tomalak, for example if you wanted to sync a schema from 2 different tables, but retain all the data. And these tables had multiple differences. This is how [Toad](http://www.quest.com/toad-for-mysql/) works – Petah Aug 15 '11 at 11:41
@Petah: OK, but he just wants to re-order his columns. That's it. That's all. – Lightness Races in Orbit Aug 15 '11 at 11:50
1

@Tomalak, and Eran's answer satisfies that requirement. But for completeness and for anyone that stumbles across this question at a later date, my answer might be useful to them. Hence the reason I said "Another approach is..." – Petah Aug 15 '11 at 21:05
Does it preserve all data? – zahid9i Mar 12 '17 at 06:53
@zahid9i in most cases yes, unless the table is under heavy load. – Petah Mar 12 '17 at 08:14

score 0 · Answer 7 · answered Dec 13 '13 at 16:12

SQL Maestro for MySQL offers tools to reorder fields as well with a GUI unfortunately it is not a drag and drop.

Open table view
Open Properties tab
Click Reorder fields from the sidebar
Click on the field you want moved and then click the up or down green arrows
Click OK to submit database update

There are probably other programs and utilities to do this as well. I found this thread from a search so I thought I would share what I found for others.

score 0 · Answer 8 · answered Sep 01 '17 at 11:48

0

Easy method for the newer version:

Open the table you want to reorder.
Go to structure tab.
Chose move column link.
Reorder the columns as you wish for.

answered Sep 01 '17 at 11:48

Black Mamba

13,632
6
82
105

Easy way to re-order columns?

8 Answers8

Linked