I must keep a table's columns as specified in a specification that changes quite often.

In MySQL Workbench, adding a new columns after all the others is easy, but I can't find a way to insert a new column where I want.
It is a problem, because I want to keep the columns ordered just like in the specification.

So I do it the painful way:

  1. Create the new column at the bottom
  2. Repeat Right-click-"Move up" tens of times to crawl my way up to the desired position

I am sure there is a more efficient way to achieve this, with the GUI rather than writing SQL code?
Even a shortcut to avoid the right-click would save me a lot of time.

  • 2
    Add it using ALTER TABLE ADD ... FIRST? dev.mysql.com/doc/refman/5.1/en/alter-table.html – pascal Jul 28 '10 at 8:57
  • 1
    As pascal says, mysql specifically allows this - but an important feature of a relational database is that neither column nor row order is implicitly maintained for a table. – symcbean Jul 28 '10 at 11:19
  • MySQL Workbench's .mwb file contains all of the database's design. If I use an SQL command, the column will be added in the database, but it will be overwritten/erased/replaced very soon. I want the columns info to be stored in the design file. I understand order is not important to the database engine, but order is somehow maintained by MySQL Workkbench, which is very convenient for maintainability. Thanks for the feedback! – Nicolas Raoul Jul 29 '10 at 1:07

OK, I have found! It is so easy I am ashamed I didn't find earlier...

Plain old drag and drop.

Just add the column at the bottom, then move it with your mouse to the position you want.

| improve this answer | |

I can say that sorting with dragging doesn't work un MySQLWorkbench 6.2 on Mac.

I found a workaround, cut the columns previous to the ones you want to move and paste them after.

| improve this answer | |
  • I wonder if it would work the same if you instead cut the column(s) you want to move and paste it/them at the required position. – Andriy M Oct 26 '16 at 13:25
  • Yeah. That works too. – loco.loop Oct 26 '16 at 13:26
ALTER TABLE `onlineau_pharmacy`.`users` 
CHANGE COLUMN `status` `status` INT(1)  NULL DEFAULT NULL AFTER `mobile_number`;
| improve this answer | |
  • 1
    Thanks, I am asking about MySQL Workbench though. – Nicolas Raoul Oct 26 '16 at 9:17

If you want to insert it at the first position:

ALTER TABLE `schame_name`.`table_name` 

ADD COLUMN `column_name` FIRST;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.