Search code examples
mysqlalter

Altering table to add column before specific column

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?

Solution

  • 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.