Search code examples
mariadbcountersql-insertincrementon-duplicate-key

MariaDB INSERT - How can I increment a counter on duplicates?

I am writing event-log data to a MariaDB database. The primary key includes the time to one-second granularity. I sometimes get bursts of identical input data. This violates the key and is currently ignored.

I'd like to add a counter field to the table so that, if identical data arrive in the same second, the counter shows how many identical items were received.

I would like an equivalent to the following made-up syntax

INSERT INTO T (a, b, c, counter) VALUES (?, ?, ?, 1)
ON DUPLICATE KEY UPDATE T.counter = 1+T.counter;

I can do this with a stored procedure but is it possible without?

I've spent over an hour searching for answers but I believe that my keywords are too close to various common problems involving auto-increment SERIAL fields.

Solution

  • Your "made-up" syntax works exactly as is.