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

Answer 1

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.

Answer 2

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!)

Answer 3

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.

Answer 4

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.

Answer 5

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