Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2651
  • Last Modified:

reorder mySQL columns?

I have a table that I have been adding fields to for quite a while, and it is getting hard to find what I am looking for when working in the table directly (using Navicat)

how can I write a mySQL statement that will put all of the columns in the database in alphabetical order?
I'd like to run that, then stick the current "ID" field in the first position.
0
MichaelEvangelista
Asked:
MichaelEvangelista
  • 2
1 Solution
 
fmonroyCommented:
Not sure if mySQL has such feature, but you can easily recreate your table:

rename table YOURTABLE to TEMPTABLE;

create table YOURTABLE as select ID,Col1,Col2,Col3... from TEMPTABLE;
0
 
gamebitsCommented:
If you decide to change the order of table columns anyway, you can do so as follows:

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

Execute this statement:

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

Drop or rename old_table.

Rename the new table to the original name:

mysql> ALTER TABLE new_table RENAME old_table;

From

http://dev.mysql.com/doc/refman/5.0/en/change-column-order.html
0
 
fmonroyCommented:
Thank you for the grade
0
 
MichaelEvangelistaAuthor Commented:
That was perfect.
Of course, I don't want to type all the col names in abc order to make the select list,
so I used a query of the old table, and then the query.columnlist in CF to get the list of column names for my new table query.

Thing is, I am using ColdFusion to run the query - and CF always returns the column names in all caps (argh!). So, I found a cool function here
http://www.leftcorner.com/index.cfm?Article=ListQueryColumn
and used that, after the fact, to create the list of columns.

And then, used listSort(myList) in CF to order the list by ABC.

and then... the query, as you proposed, with the new list in place on the select.

genius.

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now