Upserts with version awareness in a relational database - Part Two: MariaDB / MySQL
Insert, or Conditional Update I started off trying out MariaDB as a comparison to how PostgreSQL supports upserts with version awareness. MariaDB started off as a fork of MySQL, so it makes sense that the same approach is also applicable to MySQL. From part one of this blog post series, we have a simple table definition: CREATE TABLE event (id UUID PRIMARY KEY, name varchar (255) NOT NULL, version bigint NOT NULL DEFAULT 0) Unlike PostgreSQL, we don't have: ON CONFLICT as a clause to fall back from insert to update. Instead there is: ON DUPLICATE that can be applied as follows (once again, the syntax is JDBC, where each '?' character represents a value that is passed in at runtime): INSERT INTO event (id, name, version) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE name = (IF(version < VALUES(version), VALUES(name), name)), version = (IF(version < VALUES(version), VALUES(version), version)) Stylistically it doesn't seem like a scalable appro...