Advanced SQL Tutorial

Atomic Counters through Unique Key Checks

Imagine the following table:

CREATE TABLE u(id INT PRIMARY KEY, cnt INT);

INSERT INTO u VALUES (1, 2), (2, 4);

SELECT * FROM u;

+------+------+
| id   | cnt  |
+------+------+
|    1 |    2 |
|    2 |    4 |
+------+------+
3 rows in set (0,001 sec)

If you want to add to cnt whenever an event occurs, add a unique key over id and do the following:

INSERT INTO u VALUES (2, 1) ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);


+------+------+
| id   | cnt  |
+------+------+
|    1 |    2 |
|    2 |    5 |
+------+------+
3 rows in set (0,001 sec)