Can Sequel be forced to always return autoincrement id with ON DUPLICATE KEY UPDATE?

Question

I'm working with Sequel (4.x) and MySQL 5.5, on a write-heavy application performing mostly INSERTs and occasionally updating existing rows. Sequel returns an AUTO_INCREMENT id following an .insert() action.

I'm currently using .on_duplicate_key_update() in an expression like:

# Insert new row into cache_table, updating any columns that change
# on a key violation
id = DB[:table_name].on_duplicate_key_update.insert(
  col1: @col1,
  col2: @col2,
  created: nil,
  updated: nil
)

This returns the auto-increment id (LAST_INSERT_ID()) into the variable id when the row is newly inserted or a column's value changes, but returns 0 if no column's value is modified.

Reading MySQL docs, I understand why. When performing a MySQL INSERT...ON DUPLICATE KEY UPDATE, MySQL cannot reliably return the AUTO_INCREMENT id of a row that already existed but was unchanged by the UPDATE. The docs suggest using an UPDATE expression like:

ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`)

I would really like to avoid an additional SELECT in this particular application. How can I force Sequel to use the same behavior, and always return the row id even absent any actual changes?

Michael Berkowski · Accepted Answer · 2015-02-25T20:01:48.327

It is possible to cause Sequel to execute a function on the RDBMS using the Sequel.function() method

To execute MySQL's native LAST_INSERT_ID() and pass the column id as its argument, pass the column name as a symbol as the second argument:

Sequel.function(:last_insert_id, :id)

The Dataset#on_duplicate_key_update method accepts a hash of columns and new values for update, so the :last_insert_id function may be passed there, along with an explicit listing of other columns to update:

id = DB[:table_name].on_duplicate_key_update(
  # Call the native LAST_INSERT_ID()
  id: Sequel.function(:last_insert_id, :id),
  col1: @col1,
  col2: @col2,
  updated: nil
).insert(
  col1: @col1,
  col2: @col2,
  created: nil,
  updated: nil
)

This method reliably returns the LAST_INSERT_ID() into id, without needing to do a SELECT query.

Note: INSERT...ON DUPLICATE KEY UPDATE will cause the table's AUTO_INCREMENT value to advance when rows are updated if the table contains any additional UNIQUE index besides the AUTO_INCREMENT primary key. This isn't specific to Sequel, just a general warning when using ON DUPLICATE KEY UPDATE.

If this is not acceptable, you may need to resort to doing a separate SELECT instead to retrieve the id when DB.insert() returns 0.

Can Sequel be forced to always return autoincrement id with ON DUPLICATE KEY UPDATE?

1 Answers1