Reorder mysql table columns

How to reorder MySQL table columns in MySQL databases

The data structure of any application is the hardest part to change. Nevertheless, new needs arise all the time, and so, adding new table columns to an existing MySQL database is a common task.

But what about reordering the table column order in MySQL after you have already created and added the new columns to an existing table? Doing so is an advisable procedure in order to keep together very related data fields in your MySQL table structure.

While I tend to perform many MySQL database management tasks using phpMyAdmin, I haven't found any phpMyAdmin feature to change the column order of an existing database. Fortunately, you just need to execute a very simple MySQL instruction to specify a new column order:

ALTER table `table_name`
       MODIFY COLUMN `column_name` your_data_type
       AFTER `other_column_name`

Just replace the following fields by your actual MySQL data:

• table_name: the name of your MySQL table being modified.
• column_name: the name of the MySQL table column that you want to reorder.
• your_data_type: the MySQL data type of the data stored in the moved column, such as int, varchar(lenght), text, etc.
• other_column_name: the column name that will be just before the new position of your reordered column.

A MySQL table column reorder example

Here is an example of an actual MySQL query to change the table column order. Let's say I want to move the column called "user_password" just after the "user_name" column to keep strongly related columns grouped:

ALTER table `registered_users`
       MODIFY COLUMN `user_password` varchar(25)
       AFTER `user_name`

This will reorder the columns in your current MySQL table structure, but it will not alter the row order of your table records: the stored data order will remain unaltered. Reorder MySQL table columns to visually group strongly related fields. So reordering is just a way of changing the column order of an existing MySQL table, and it won't modify your query performance.

Column reorder is mainly a matter of coherence and logical organization. On the contrary, altering the stored data order may optimize (or negatively impact) such MySQL query performance.

Permalink

Posted by ESV