MySQL Workbench 6.3: how should I insert a new column into a specific position (in the middle of the table)

Question

I am working on a SQL table and would like to create and insert a new column into the same table. The thing is, I would like my new column to be at a specific place in the middle of the table, rather than at the end, and there does not seem to be a method of doing this exact insertion in the documentation.

So, the name of my table is "actor" and its columns are "actor_id", "first_name", "last_name", and "last_update". I would like to create a column called "middle_name" between the columns "first_name" and "last_name". My code that I tried out was

ALTER TABLE actor
    ADD middle_name VARCHAR(25);

However this just adds the column to the end of the table and not the middle as I want. Are there any suggestions for how to correct this?

Answer 1

In MySQL Workbench you can open the table editor (via the context menu in the schema tree -> Alter table...). On the columns tab you see all currently defined columns. You can add new ones, remove unneeded ones and drag them into the order you want. Then apply the changes. The upcoming wizard with allow you to review of the code which will be sent to the server. Existing data should not be touched (unless you drop a column with data), but it's certainly safer to keep a backup around in case something goes wrong.

Answer 2

It goes simple as:

ALTER TABLE actor
    ADD COLUMN middle_name VARCHAR(25) AFTER first_name;