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:

reorder mysql table columns
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.

8 comments:

Jouni said...

When reordering, you must supply the full column definition, not only datatype.

"When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward."

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

E.S.V. said...

Right, Jouni: other column attributes (if any) should be specified in the modify clause, as they were used in the original definition.

solaiman ibna said...

ALTER table `site_user`
MODIFY COLUMN `country` varchar(40)
AFTER `user_email`

I used your this comand , but nothing to happend, but mysql query showed no error .....
Is this command realy work.. confused

E.S.V. said...

Hi, Solaiman.

This reorder command actually works, and according to your query, the column "country" should be appearing now next to the "user_email" column (after that column, on its right side.)

Also check that you specified the right data type and creation attributes of the modified column or the reorder could have no effect.

Take into account that this will change the printed column order when browsing MySQL columns, but the actual, internal order of stored data remains unchanged.

Kruncher said...

Thanks for the great tip!

omar said...

Thank you , this helped me very much .. Wish u nice times

omar said...

Thank you , that was Great help for me

KUMAtron said...

Super usefull :)
Thanks!