3

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.

3
  • 2
    Add it using ALTER TABLE ADD ... FIRST? dev.mysql.com/doc/refman/5.1/en/alter-table.html
    – pascal
    Jul 28, 2010 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, 2010 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! Jul 29, 2010 at 1:07

4 Answers 4

3

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.

1

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.

2
  • 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, 2016 at 13:25
  • Yeah. That works too.
    – loco.loop
    Oct 26, 2016 at 13:26
0
ALTER TABLE `onlineau_pharmacy`.`users` 
CHANGE COLUMN `status` `status` INT(1)  NULL DEFAULT NULL AFTER `mobile_number`;
1
  • 1
    Thanks, I am asking about MySQL Workbench though. Oct 26, 2016 at 9:17
0

If you want to insert it at the first position:

ALTER TABLE `schame_name`.`table_name` 

ADD COLUMN `column_name` FIRST;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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