MySQL re-arranging order of columns with phpmyadmin
MysqlPhpmyadminMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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.
Solution 3 - Mysql
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!)
Solution 4 - Mysql
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.
Solution 5 - Mysql
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