DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) Change column order

Info Catalog (mysql.info.gz) ALTER TABLE problems (mysql.info.gz) Table Definition Issues (mysql.info.gz) Temporary table problems
 
 A.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 will not remain the same if you add, move, or
 delete columns. A simple change to your table structure will cause your
 application to fail.
 
Info Catalog (mysql.info.gz) ALTER TABLE problems (mysql.info.gz) Table Definition Issues (mysql.info.gz) Temporary table problems
automatically generated byinfo2html