Advanced SQL Tutorial

From MemCP
Revision as of 12:41, 20 May 2024 by Carli (talk | contribs) (→‎Atomic Counters through Unique Key Checks)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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)

This kind of behaviour is atomic, so it is safer than splitting it into two query. This behaviour also scales over multicores via Data Auto Sharding and Auto Indexing