Reorder Columns in a Table

: Joel Lipman; Databases; 22 July 2013; Hits: 5664

What?
Just a quick note to myself on how to reorder columns as I was having difficulty using a phpMyAdmin interface to do this.

How?
Taken from the best forum for programming Qs&As: http://stackoverflow.com/questions/4095481/easy-way-to-re-order-columns

Method: phpMyAdmin
So in the phpMyAdmin interface, apparently, you can drag the columns by clicking and holding on the headers when viewing a table structure... Though maybe this is derived from a template and depending on if you are using MS Internet Explorer...

Can't get it working? I use whatever is most useful and Google's Chrome is the fastest browser I have. Here are some ways to do this:

Method: MySQL

copy raw

ALTER TABLE table_name MODIFY COLUMN misplaced_column AFTER other_column;

ALTER TABLE table_name MODIFY COLUMN misplaced_column AFTER other_column;

NOTE that annoyingly the column doesn't retain the default properties it had before the move so I guess this has to be included:

copy raw

ALTER TABLE table_name MODIFY COLUMN misplaced_column BIT(1) NOT NULL DEFAULT b'0' AFTER other_column;

-- OR A MORE COMPLETE EXAMPLE --

ALTER TABLE table_name 
     MODIFY COLUMN misplaced_column VARCHAR(13) NOT NULL DEFAULT 'DefaultString' COMMENT 'A Comment' 
     AFTER other_column;

ALTER TABLE table_name MODIFY COLUMN misplaced_column BIT(1) NOT NULL DEFAULT b'0' AFTER other_column;
-- OR A MORE COMPLETE EXAMPLE --
ALTER TABLE table_name
MODIFY COLUMN misplaced_column VARCHAR(13) NOT NULL DEFAULT 'DefaultString' COMMENT 'A Comment'
AFTER other_column;

Alternatively

Export the file as a SQL file (data & structure),
Open it using a text editor

Modify the order of the columns, for example:

-- From
CREATE TABLE IF NOT EXISTS `Table1` (
  `Column1` int(11) NOT NULL AUTO_INCREMENT,
  `Column3` int(11) NOT NULL,
  `Column2` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`Column1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


-- To
CREATE TABLE IF NOT EXISTS `Table1` (
  `Column1` int(11) NOT NULL AUTO_INCREMENT,
  `Column2` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `Column3` int(11) NOT NULL,
  PRIMARY KEY (`Column1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;