I'm trying to avoid the massiveness autoincrement (a little bit here and there is fine) when i do an insert on duplicate key.
I wrote it out as separate queries. Check if record exist using key (can be primary or foreign key). If exist, update. If not, insert.
Which kinds works except that not all my schema are structured the same way so for one of my tables, it rewrote all the records that had the same foreign key because it was not unique (but I do not have this problem when i do insert on duplicate key).
So I was wondering there's a good way to recreate the Insert Update On Duplicate Key as separate queries so it can avoid the unnecessary auto increments.
IODKU requires a UNIQUE
key, usually not an AUTO_INCREMENT
.
IODKU, if not already in a transaction, can be simulated with
START TRANSACTION;
SELECT ... FOR UPDATE; -- note
if row exists
UPDATE ...;
else
INSERT ...;
COMMIT;