Search code examples
mysqlhsqldb

"Insert if not exists" in both MySQL and HSQLDB

Is there a SQL statement (or atomic sequence of statements) supported by both MySQL and HSQLDB to insert values if they aren't already there?

I'm working on an app that uses MySQL as its production database and HSQLDB for unit tests; I'd like to have a single "initial data import when the tables are empty" script.

MySQL supports INSERT IGNORE, REPLACE INTO and INSERT INTO ... ON DUPLICATE KEY UPDATE ..., but HSQLDB doesn't; conversely, HSQLDB supports MERGE but MySQL doesn't.

Solution

  • HSQLDb from version 2.3.4 adds support for insert ignore.

    http://hsqldb.org/

    Version 2.3.4 added the UUID type for columns, SYNONYM for tables and functions, PERIOD predicates, and auto-updated TIMESTAMP columns on row updates. Other new features included the ability to cancel long-running statements from JDBC as well as from admin sessions, and UTF-16 file support for text table sources, in addition to 8-bit text files. MySQL compatibility for REPLACE, INSERT IGNORE and ON DUPLICATE KEY UPDATE statements.

    And

    http://hsqldb.org/doc/guide/guide.pdf (page 260).

    HyperSQL supports and translates INSERT IGNORE, REPLACE and ON DUPLICATE KEY UPDATE variations of INSERT into predictable and error-free operations. When INSERT IGNORE is used, if any of the inserted rows would violate a PRIMARY KEY or UNIQUE constraint, that row is not inserted. With multi-row inserts, the rest of the rows are then inserted only if there is no other violation such as long strings or type mismatch, otherwise the appropriate error is returned. When REPLACE or ON DUPLICATE KEY UPDATE is used, the rows that need replacing or updating are updated with the given values. This works exactly like an UPDATE statement for those rows. Referential constraints and other integrity checks are enforced and update triggers are activated. The row count returned is simply the total number of rows inserted and updated.