How to change the MySQL column order in a table

First, consider whether you really need to change the order of columns in the table. The core point of SQL is getting applications from the data storage format. You should always specify the order in which data is retrieved. In the 1th statement below, the columns are returned in col_name1, Col_name2, Col_name3 order, and in the 2nd statement, the columns are returned in col_name1, Col_name3, col_name2 order:

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 the table columns, you can do the following:

Creates a new table with a column with a new order.

Execute the statement:

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

Undo or rename the old_table.

Rename the new table to the original name:

mysql> ALTER TABLE new_table RENAME old_table;

SELECT * is ideal for testing queries. However, in your application, never rely on the use of select *, and do not rely on retrieving columns based on their location. If you add, move, or delete columns, the order and position of the columns returned will not remain the same. Simple changes to the table structure can also cause the application to fail.