Change Column Order MySQL via Command Line

This is an article which is made to show to alter a table in MySQL Database Server with the specific aim which is to change column order in MySQL database server via command line. The scenario is where a table exist with the order of the fields seems not right and it is going to be changed as shown below :

mysql> desc table_test;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| name      | varchar(255) | NO   |     | NULL    |       |
| id_table  | int(11)      | NO   | PRI | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0,00 sec)
mysql>

The above table is actually derived from an example created in an article named ‘Alter Table Add Primary Key MySQL Example via Command Line’ which can be accessed in this link. The primary concern is when adding a primary key field after other existing table might be uncomfortable to be read because normally a primary key field will be located in the first field order in a table.

So, to alter it into a correct order, for instance to move the field ‘id_table’ into the first order since maybe that field is a primary key field and it is normally that kind of field located in the first order. Below is the syntax which can be used to reorder the sequence of the field on the table :

alter table server_test change name name varchar(255) after id_table;

Below is the execution of the command above in a real situation :

mysql> alter table server_test change name name varchar(255) after id_table;
Query OK, 0 rows affected (0,21 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>

After successfully reorder the field of the table as shown above. Try to redescribe the order of the field at the table by executing the following command in MySQL command console :

desc table_name

Below is the output executed based on the above command :

mysql> desc table_test;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id_table  | int(11)      | NO   | PRI | NULL    |       |
| name      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0,00 sec)
mysql>

As shown above, the order of the column has already changed where the primary key field is now right on the first order.