Reorder Columns in a Table

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
copyraw
ALTER TABLE table_name MODIFY COLUMN misplaced_column AFTER other_column;
  1.  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:
copyraw
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;
  1.  ALTER TABLE table_name MODIFY COLUMN misplaced_column BIT(1) NOT NULL DEFAULT b'0' AFTER other_column; 
  2.   
  3.  -- OR A MORE COMPLETE EXAMPLE -- 
  4.   
  5.  ALTER TABLE table_name 
  6.       MODIFY COLUMN misplaced_column VARCHAR(13) NOT NULL DEFAULT 'DefaultString' COMMENT 'A Comment' 
  7.       AFTER other_column; 

Alternatively
  1. Export the file as a SQL file (data & structure),
  2. Open it using a text editor
  3. 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 ;
    
  4. Make sure you have the INSERT INTO commands if this includes website data!!!
  5. Drop the table
  6. Reload the script you just modified.
Category: Databases :: Article: 513

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Please publish modules in offcanvas position.