reorder mySQL columns?

Posted on 2007-10-04
Last Modified: 2009-04-26
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.
Question by:MichaelEvangelista
    LVL 5

    Accepted Solution

    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;
    LVL 28

    Expert Comment

    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;

    LVL 5

    Expert Comment

    Thank you for the grade

    Author Comment

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



    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now