Reorder MySQL Table Column

Reorder MySQL Table Column? How can be happen? maybe we design a unperfect table in the first time, so we need to reorder the column.

It can be done easily by User Interface in SQL Yog,

 

Figure 1. Choose The Table

 

Figure 2. Choose the field to be Reorder.

  

Figure 3. Field has been Reorder

 

Figure 4. Field Reorder Sucessfully

but there also any syntax can solve this problem :

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

Just replace the following fields by your actual MySQL data:

* tb_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.


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.

Example by Using Syntax :

I Have a Table

CREATE TABLE `guest_book` (                             
              `id_gb` int(4) NOT NULL AUTO_INCREMENT,               
              `pengirim` varchar(20) NOT NULL DEFAULT '',           
              `email` varchar(50) NOT NULL DEFAULT '',              
              `isi` text NOT NULL,  
              `tgl1` datetime DEFAULT NULL,                              
              `tgl` datetime DEFAULT NULL,                    
              PRIMARY KEY (`id_gb`)                                 
            )

and then i use DDL syntax :

ALTER table `guest_book`
MODIFY COLUMN `tgl1` timestamp
AFTER `tgl`

(4 row(s)affected)
(0 ms taken)

You Can Check The Difference Here..

Please beware of changing datatype after the field name in second line syntax.

Related Post: