How to change the Mysql column sequence in a table

To learn how to change the Mysql column sequence in a table, first consider whether to change the column sequence in the table. The core point of SQL is to obtain applications from the data storage format. The order of data retrieval should always be specified. In the following 1st statements, columns are returned in the order of col_name1, col_name2, and col_name3. in the 2nd statements, columns are returned in the order of col_name1, col_name3, and 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 the table columns, you can perform the following operations:

Create a new table with columns in the new order.

Run the following statement:

Mysql> insert into new_table
-> SELECT columns-in-new-order FROM old_table;

Undo or rename old_table.

Rename the new table to the original name:

Mysql> alter table new_table RENAME old_table;

SELECT * is very suitable for test queries. However, in an application, do not rely on the use of SELECT * or retrieve columns based on their locations. If a column is added, moved, or deleted, the sequence and position of the returned column are not the same. A simple change to the table structure may also cause application failure.