Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

ALTER TABLE ADD COLUMN with AFTER gives a syntax error

ErrorERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AFTER'

Short answer

The parenthesized form ADD COLUMN (a INT, b INT) cannot take FIRST or AFTER. Write one ADD COLUMN clause per column, each with its own AFTER, and separate the clauses with commas in a single ALTER TABLE statement.

  • Applies to MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Look at your statement. If the new columns sit inside one pair of parentheses, that is the cause.

    SHOW CREATE TABLE t1\G
  2. 2Match what you see, then jump to its fix

    1. 1ALTER TABLE t1 ADD COLUMN (a INT, b INT) AFTER cAFTER used with the parenthesized column listFix
    2. 2You wanted all new columns together, directly after one existing columnThe same AFTER applied to the whole group by mistakeFix

What does the error look like?

  • ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AFTER'

Cause 1 of 2

AFTER used with the parenthesized column list

1Check

The syntax for the list form has no position clause. Only the single-column form has one.

SHOW COLUMNS FROM t1;

2Fix

Split the list. Repeat ADD COLUMN for each column and give each its own position. A later column can be placed after one added earlier in the same statement.

ALTER TABLE t1
  ADD COLUMN a INT AFTER c,
  ADD COLUMN b VARCHAR(50) AFTER a;

3Verify

The columns appear in the order you asked for.

SHOW COLUMNS FROM t1;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

The same AFTER applied to the whole group by mistake

1Check

Decide the order you want, for example c, a, b.

SHOW COLUMNS FROM t1;

2Fix

Chain them: put the first after the existing column, and each next one after the previous new column.

ALTER TABLE t1
  ADD COLUMN a INT AFTER c,
  ADD COLUMN b INT AFTER a,
  ADD COLUMN d INT AFTER b;

3Verify

The order is c, a, b, d.

SHOW COLUMNS FROM t1;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Use the parenthesized list only when column position does not matter; the default is to add columns last.
  • Do not depend on column order in application code: select columns by name.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.