Skip navigation links
A.5.7.3 TEMPORARY Table Problems »
Section Navigation      [Toggle]

A.5.7.2. How to Change the Order of Columns in a Table

First, consider whether you really need to change the column order in a table. The whole point of SQL is to abstract the application from the data storage format. You should always specify the order in which you wish to retrieve your data. The first of the following statements returns columns in the order col_name1, col_name2, col_name3, whereas the second returns them in the order col_name1, col_name3, col_name2: 

mysql> SELECT col_name1, col_name2, col_name3 FROM tbl_name;
mysql> SELECT col_name1, col_name3, col_name2 FROM tbl_name;

If you decide to change the order of table columns anyway, you can do so as follows:

  1. Create a new table with the columns in the new order.

  2. Execute this statement: 

    mysql> INSERT INTO new_table
    -> SELECT columns-in-new-order FROM old_table;

  3. Drop or rename old_table.

  4. Rename the new table to the original name: 

    mysql> ALTER TABLE new_table RENAME old_table;

SELECT * is quite suitable for testing queries. However, in an application, you should never rely on using SELECT * and retrieving the columns based on their position. The order and position in which columns are returned does not remain the same if you add, move, or delete columns. A simple change to your table structure could cause your application to fail.


User Comments

Posted by Aaron Powers on September 3 2004 4:04pm[Delete] [Edit]

There is an easier way to reorder columns without creating a new table. In fact, the way recommended above is a bad idea if your database is live -- creating an alternate table and copying back in means you lose changes that happened during the reordering process.

Just use the "alter table" command:

ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext AFTER ColumnNameToPutAfter
ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext BEFORE ColumnNameToPutBefore
ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext FIRST

Note that you need to put in the correct column_definition ("longtext", above). It would be a nice MySQL feature if ALTER TABLE didn't require the column_definition in the query, since the query does not need to change the type of the column.

As a comment on the description above, there's also a perfectly legit other reason to reorder your columns besides programmatical use -- usability of the table. It's easier to skim the table when it's in the right order to begin with.

Posted by Ali Irfan Ustek on March 21 2006 7:53pm[Delete] [Edit]

Well I have tried above tip but BEFORE keyword is removed but AFTER works just fine

Posted by Scott on July 20 2006 4:42am[Delete] [Edit]

Ali is right - use "AFTER" as "BEFORE" does not work. I tried Aaron's query in MySQL 4.1 (substituting in the correct field value), and it worked like a charm saving me some time.

Posted by John Walker on May 16 2008 10:16pm[Delete] [Edit]

The CHANGE syntax seems to be a little different but also works:

ALTER TABLE table CHANGE columnNameBefore ColumnNameBefore ColNameBeforeColType AFTER ColumnNameToMoveAfter

I couldn't get anything shorter than this to work but this did.

Posted by Brian McCain on August 22 2008 3:13pm[Delete] [Edit]

Yep, the above comment worked fine, in my case I had a table called "stats" and I wanted to move the column "timevisited" (of type bigint) after "ip", so the following command worked fine:

alter table stats change timevisited timevisited bigint after ip;

Add your own comment.