MySQL alter table to re-order columns

MySQL alter table to re-order columns

Sometimes because of my completely anal nature, I want the fields that I’ve already added to a table in MySQL to be in a different order. To do this you can simply alter that table column, without changing any of its properties, then adding “AFTER column_3”.

So for instance you had a table like this:

mysql> describe test;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| column_id | int(11) | NO   | PRI | NULL    | auto_increment |
| column_1  | int(11) | NO   |     | NULL    |                |
| column_2  | int(11) | NO   |     | NULL    |                |
| column_3  | int(11) | NO   |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

But you really want column_1 to be ordered after column_3 because you are as ridiculous as I am. You can run this query:

ALTER TABLE `test` CHANGE `column_1` `column_1` INT( 11 ) NULL AFTER column_3;

Now your table looks like this:

mysql> describe test;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| column_id | int(11) | NO   | PRI | NULL    | auto_increment |
| column_2  | int(11) | NO   |     | NULL    |                |
| column_3  | int(11) | NO   |     | NULL    |                |
| column_1  | int(11) | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

I find this to be useful for me on larger tables that have grown over time and I want some relevant columns grouped together for when I am viewing data in phpMyAdmin or whatever.