SQLite INSERT - ON DUPLICATE KEY UPDATE (UPSERT)
SqlMysqlDatabaseSqliteUpsertSql Problem Overview
MySQL has something like this:
INSERT INTO visits (ip, hits)
VALUES ('127.0.0.1', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;
As far as I know this feature doesn't exist in SQLite, what I want to know is if there is any way to achive the same effect without having to execute two queries. Also, if this is not possible, what do you prefer:
- SELECT + (INSERT or UPDATE) or
- UPDATE (+ INSERT if UPDATE fails)
Sql Solutions
Solution 1 - Sql
INSERT OR IGNORE INTO visits VALUES ($ip, 0);
UPDATE visits SET hits = hits + 1 WHERE ip LIKE $ip;
This requires the "ip" column to have a UNIQUE (or PRIMARY KEY) constraint.
EDIT: Another great solution: https://stackoverflow.com/a/4330694/89771.
Solution 2 - Sql
Since 3.24.0 SQLite also supports upsert, so now you can simply write the following
INSERT INTO visits (ip, hits)
VALUES ('127.0.0.1', 1)
ON CONFLICT(ip) DO UPDATE SET hits = hits + 1;
Solution 3 - Sql
I'd prefer UPDATE (+ INSERT if UPDATE fails)
. Less code = fewer bugs.
Solution 4 - Sql
The current answer will only work in sqlite OR mysql (depending on if you use OR or not). So, if you want cross dbms compatibility, the following will do...
REPLACE INTO `visits` (ip, value) VALUES ($ip, 0);
Solution 5 - Sql
You should use memcached for this since it is a single key (the IP address) storing a single value (the number of visits). You can use the atomic increment function to insure there are no "race" conditions.
It's faster than MySQL and saves the load so MySQL can focus on other things.