H2 add multiple column MySQL syntax

Question

I have the following table:

CREATE TABLE users (
  id   INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(256)
);

I would like to add multiple columns in one query. I am using H2 a lot in testing but in production I use MySQL. Both are using same DDL scripts.

Is there any common syntax for H2 and MySQL that allows adding several columns?

MySQL syntax:

ALTER TABLE users
  ADD `col1` INT,
  ADD `col2` INT,
  ADD `col3` INT
  AFTER id;

H2 syntax:

ALTER TABLE users
  ADD (
    `col1` INT,
    `col2` INT,
    `col3` INT
  ) AFTER `id`;

If it helps, my H2 JDBC URL is:

// note MODE=MYSQL in the end
jdbc:h2:users;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MYSQL

Patch to H2 was applied in scope of this discussion.

EDIT:

Of course I can use this syntax (which is horrible for large tables) and I have to manage these AFTER statements to save columns order:

ALTER TABLE `users`
  ADD `col1` INT AFTER `id`;

ALTER TABLE `users`
  ADD `col2` INT AFTER `col1`;

ALTER TABLE `users`
  ADD `col3` INT AFTER `col2`;

Using two different database systems on development and live is asking for trouble annyway.. — Raymond Nijland, Feb 01 '18 at 15:04
If you want to deploy to different databases *(be they deployment or testing)* then you're going to face these dialect differences a lot. As such you're either going to have to use the lowest common denominator *(which doesn't always exist)*, or have different code branches for each dialect. Just wait until you find out that even the division operator `/` is implemented differently in different RDBMS. *(Normally `2 / 4 = 0` but in MySQL it's `0.5` and you'd use `2 DIV 4` for integer division.)* Same for modulo, implementing `FULL OUTER JOIN`, table aliases, table/column name quoting, etc, etc — MatBailie, Feb 01 '18 at 15:08
@MatBailie, thankfully to your point, I rethought our DDL scripts. Now we use two DDL sources: 1. `h2` - for developer testing (test run ~10 seconds); 2. `mysql` - same DDL scripts but optimized for MySQL migrations, they are used for migrations tests, CI builds (test run ~40 seconds). — Andrii Abramov, Sep 19 '18 at 13:54

score 6 · Accepted Answer · answered Feb 28 '19 at 17:58

As far as I know, there is no common syntax for that. There are some things that don't translate from MySQL to H2 and "multiple add columns in one statement" is one of those things. I agree that adding the individual columns is horrible for large tables, which is why when we are in situations like this on my project, we run separate migrations for test (H2) and prod (MySQL).

H2 add multiple column MySQL syntax

1 Answers1