Change column order in MySQL

Question

Is it possible to change the order of columns in MySQL (using phpMyAdmin XAMPP) by which they appear without dropping the current table?

Dup: I think http://stackoverflow.com/questions/1962448/php-myadmin-change-field-order-move-up-or-down — Kris.Mitchell, Apr 04 '12 at 18:57

score 11 · Accepted Answer · edited Feb 14 '20 at 14:57

11

Try this

ALTER TABLE `table_name`
    MODIFY COLUMN `column_name1` varchar(100) AFTER `column_name2`;

edited Feb 14 '20 at 14:57

informatik01

answered Oct 04 '12 at 10:28

V A S

score 5 · Answer 2 · edited Feb 14 '20 at 14:56

5

I'm not sure about phpMyAdmin, but you can certainly do that with an SQL query:

ALTER TABLE `table`
CHANGE COLUMN `oldname` `newname` *column_definition* [AFTER|BEFORE] `colname`;

edited Feb 14 '20 at 14:56

informatik01

answered Apr 04 '12 at 18:58

abresas

...and you can use phpmyadmin to run that query so the answer would be yes. – d_inevitable Apr 04 '12 at 18:59
Hi, this showed errors on running the sql query ALTER TABLE 'teachers-final' CHANGE COLUMN 'Field' 'Fields' varchar(20) [AFTER] 'Name'; Any Suggestions? – manojadams Apr 05 '12 at 02:30
simply AFTER. remove brackets – abresas Apr 05 '12 at 21:50

score 2 · Answer 3 · answered Aug 30 '22 at 05:37

How to do it by using PhpMyAdmin:

Show table structure
On the relevant row (i.e. database column) click on "Modify"
On the extreme right, choose the new position form the "Move field" combo box
Optionally, click on "Show preview" to see what SQL query will be run (also useful for automatically repeating the procedure in another instance of the same database)
After closing the preview box, click on "Save"

Done!

3 Answers3