CODEWITHSUNDEEP
mysqlphpmyadmin
Haroldo
Haroldo

Reputation: 37377

Easy way to re-order columns?

I'm working on a database. On most of the tables, 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?

Upvotes: 27

Views: 21550

Answers (8)

Black Mamba
Black Mamba

Reputation: 15545

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.

Upvotes: 0

DisgruntledGoat
DisgruntledGoat

Reputation: 72530

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.

Upvotes: 5

Dee
Dee

Reputation: 15

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

Upvotes: 2

amaster
amaster

Reputation: 2163

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.

Upvotes: 0

zahid9i
zahid9i

Reputation: 596

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;

Upvotes: 6

Alex Pliutau
Alex Pliutau

Reputation: 21957

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

Upvotes: 4

Petah
Petah

Reputation: 46050

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;

Upvotes: 1

Eran Galperin
Eran Galperin

Reputation: 86805

Use an ALTER TABLE ... MODIFY COLUMN statement. 

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

Upvotes: 54

Related Questions