changing order of columns in table

[LordRogaine]

I am using mysql with php. I have about 4,000 - 5,000 members accessing my site. I want to rearrange the order in which the columns appear in a table in my database to make things more efficient. An example would be--- ColumnBlue, ColumnRed, ColumnGreen. How would I change it to say Column Red, Column Blue, ColumnGreen.

Thanks in advance!

Lord Rogaine

[laserlight]

You could read the docs:
http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html

[richie]

I can't think of a simple way to do this.

But one way is to create another table with the correct column order, and populate it with data from old the table.

A query like this will do it:

insert into newTable (Column Red, Column Blue, ColumnGreen) select (Column Red, Column Blue, ColumnGreen) from oldTable

Next, verify the integrity of the data in the new table, then drop old table, and finally rename new table to the proper name.

Richie.

[mhalloran]

You can also do a mysqldump using the following options:
--add-drop-table
--complete-insert

and then vi the table creation to switch the order and then put it back.

Similar to above, just a different method.

[LordRogaine]

I'll give it a try thanks!!!