MySQL re-arranging order of columns with phpmyadmin

Question

Any help on re-ordering the columns in MySQL using phpMyAdmin? Is it called cardinality? I have created tables, but need to re-arrange the order of the columns due to an export script i have. It exports based on the arrangements. E.g. I want columns:

Apple | Cherry | Banana

changed to:

Apple | Banana | Cherry

Adding the word "specific" into your question does not make it so. — Lightness Races in Orbit, Jun 02 '11 at 19:44
@FabianBlechschmidt, the OP is talking about reordering COLUMNs, you are talking about reordering ROWs. Big difference. — Johan, Jan 28 '13 at 18:28
thanks @johan you are right :-) I still have a problem to understand column/row in english :( Sorry karto, my fault — Fabian Blechschmidt, Jan 28 '13 at 19:17
Cardinality is something totally different. Cardinality refers to the number of different values a column can take. A boolean field has low cardinality, a autoincrement field has high cardinality (every row is different) — Johan, May 14 '14 at 19:31
Possible duplicate of [PHP myAdmin - Change Field Order (Move Up Or Down)](http://stackoverflow.com/questions/1962448/php-myadmin-change-field-order-move-up-or-down) — Márton Tamás, Aug 03 '16 at 14:17
I put a graphic answer below. In case you get stuck on this. Took me ages to find out how to do this. — BeNice, Feb 23 '17 at 11:30

score 70 · Answer 1 · edited Aug 14 '13 at 11:30

70

phpMyAdmin has finally included this feature in the most recent version (4.0 and up).

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

edited Aug 14 '13 at 11:30

ajcw

23,604
6
30
47

answered Jul 22 '13 at 18:41

DisgruntledGoat

70,219
68
205
290

1

Yay. This answer belongs on http://stackoverflow.com/q/1962448/673991 too. With a screenshot FTW. – Bob Stein Jul 31 '13 at 15:55

score 43 · Accepted Answer · answered Jun 02 '11 at 20:06

43

Use the ALTER TABLE with MODIFY COLUMN command. Something like:

ALTER TABLE foo MODIFY COLUMN Hobby VARCHAR(20) FIRST;

I don't know whether or not there's a GUI way to do it in phpmyadmin, but normal SQL queries should work, too.

answered Jun 02 '11 at 20:06

King Skippus

3,801
1
24
24

↓↓↓ There now is a very very easy GUI solution to this in phpMyAdmin now! (As I noted in my answer below ↓↓↓. 7 YEARS ago!!! ↓↓↓) I'm just adding this as this is a LONG lived question that people are STILL coming to this and phpMyAdmin makes it very very easy to reorder columns now. **(PS I hope this is considered OK. If this is bad form please delete this message. *No disrespect meant to @King Skippus who gave an excellent answer in 2011.*)** – BeNice Aug 14 '22 at 12:08

BeNice · Answer 3 · 2019-11-14T17:50:03.770

42

OP asked how to change column order in phpMyAdmin.

NB: phpMyAdmin keeps making changes but, as of Nov 2019, this is still correct.

1) Click on "Structure".

2) Next click on "Move columns" at the bottom.

3) and voila just drag and drop! (Very modern for dear old myPhpAdmin!)

edited Nov 14 '19 at 17:50

answered Sep 25 '15 at 06:39

BeNice

2,165
2
23
38

2

The move column feature wasn't available yet at the time of the question. Great they brought it. – karto Sep 30 '15 at 11:48
1

Yep I just added that in case another poor soul in trouble like me happened by. That is why I called it the ever changing answer. Lots has changed since the OP. Oh and I added a pic cause I could not see the damned thing. – BeNice Jan 11 '16 at 03:22
1

Thanks, @BeNice for this! I didn't see that option. – shenn Feb 08 '18 at 20:03

score 22 · Answer 4 · edited Jan 26 '13 at 23:07

22

To reorder columns, pop-up a query window and use the statement:

ALTER TABLE ... MODIFY COLUMN ... FIRST|AFTER ...

Unfortunately you will have to retype the entire column definition. See http://dev.mysql.com/doc/refman/5.1/en/alter-table.html Example:

ALTER TABLE t MODIFY COLUMN cherry VARCHAR(255) NULL AFTER banana;

May vary depending on your MySQL version, but this syntax appears to work since version 3.23.

edited Jan 26 '13 at 23:07

Bob Stein

16,271
10
88
101

answered Jun 02 '11 at 19:30

Halcyon

57,230
10
89
128

Thanks; but can it help re-arrange the order of the columns? – karto Jun 02 '11 at 19:51

score 6 · Answer 5 · edited Jan 26 '13 at 22:50

Unfortunately, you will have to (1) pop up a query window, and (2) respecify the attributes of each column you rearrange. For example:

ALTER TABLE test.`new table`
  MODIFY COLUMN cherry unsigned int(10) NOT NULL AUTOINCREMENT PRIMARY KEY 
  AFTER banana

Table layout before change:

`apple`  varchar(45) NOT NULL,
`cherry` int(10) unsigned NOT NULL AUTO_INCREMENT,
`banana` varchar(45) NOT NULL

Table layout after change:

`apple`  varchar(45) NOT NULL,
`banana` varchar(45) NOT NULL,
`cherry` int(10) unsigned NOT NULL AUTO_INCREMENT

MySQL re-arranging order of columns with phpmyadmin

5 Answers5