Altering table to add column before specific column

Question

Say I have this structure:

col1 | col2 | col3 | col4 | created | createdby

I want to add a column after col4, but there are a few problems. There could be any number of 'Col' columns, so using AFTER isn't an option (Ill never know what it comes after). So, naturally, I though I can just do BEFORE created, right? Well this doesn't work:

ALTER TABLE table ADD extracol VARCHAR(255) BEFORE created

Is there a way to get this working? I just get an invalid syntax error. I would have though if I can add a column AFTER a specific column, then I can do it BEFORE but apparently that's not the case?

Answer 1

It is possible, but it takes two queries and a little 'out of the box' thinking:

First insert the new column after the column we really want it before:

ALTER TABLE newtable ADD extracol VARCHAR(255) AFTER created

Then use another alter command to move the previous column after the new one. You'll need the right column type for the other column here:

ALTER TABLE newtable MODIFY created DATETIME AFTER extracol

Untested. Backup all your data before testing or running.

Answer 2

Unfortunately, you cannot do that.

If you really want them in that specific order, you will have to create a new table with the columns in that order and copy the existing data or rename columns.

There is no easy way.