Easy way to re-order columns?

I'm working on a database. On most of the tabl开发者_运维技巧es, 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?

Use an ALTER TABLE ... MODIFY COLUMN statement.

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

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;

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.

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

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

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;

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

1. Open table view
2. Open Properties tab
3. Click Reorder fields from the sidebar
4. Click on the field you want moved and then click the up or down green arrows
5. 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.

Easy method for the newer version:

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