I want a UPSERT (UPDATE if exists, else insert) on a MySQL table with these fields:
CREATE TABLE item (
uid int(11) NOT NULL AUTO_INCREMENT,
timestamp int(11) NOT NULL DEFAULT '0',
category1 int(11) NOT NULL DEFAULT '0',
category2 int(11) NOT NULL DEFAULT '0',
counter int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`uid`)
)
This table is already in productive use, and the combination of (timestamp,category1,category2) is for my INSERT unique, but not for the whole table content. That's the reason why I can't use the "ON DUPLICATE KEY" feature.
For clarification about the UNIQUE problem:
If category1=9 then this is my insert, and (timestamp,category1,category2) is unique. But the table is in productive use, and if category1=1,2 then (timestamp,category1,category2) is not unique.
As far as I know I can't add a UNIQUE key, as it would create problems with the categories 1+2.
What I need is something like this:
IF (timestamp,category1,category2) exists
counter=existingCount + newCount
ELSE
insert new record
I use PHP and MySQL, so any combination of both languages is fine.
What I tried already, but takes too much time is:
Single PHP SELECT statement, then UPDATE or INSERT
INSERT everything then UPDATE and DELETE duplicate rows
Thanks to answers I created now this Stored Procedure, syntax is OK but procedure is not working:
DROP PROCEDURE IF EXISTS myUpsert;
DELIMITER |
CREATE PROCEDURE myUpsert (IN v_timestamp INT, IN v_category1 INT, IN v_category2 INT, IN v_counter INT)
BEGIN
DECLARE existingCounter INT;
SELECT COUNT(counter) AS existingCounter from item
WHERE timestamp =v_timestamp AND category1=v_category1 AND category2=v_category2;
IF existingCounter=0 THEN
INSERT INTO item (timestamp,category1,category2,counter)
VALUES (v_timestamp,v_category1,v_category2,v_counter);
ELSE
UPDATE item SET count=v_counter+existingCounter
WHERE timestamp=v_timestamp AND category1=v_category1 AND category2=v_category2;
END IF;
END
|
DELIMITER ;
解决方案
DROP PROCEDURE IF EXISTS myUpsert;
DELIMITER |
CREATE PROCEDURE myUpsert (IN v_timestamp INT, IN v_category1 INT, IN v_category2 INT, IN v_counter INT)
BEGIN
DECLARE existingCounter INT;
SET existingCounter = (SELECT COUNT(counter) from item
WHERE timestamp =v_timestamp AND category1=v_category1 AND category2=v_category2);
IF existingCounter=0 THEN
INSERT INTO item (timestamp,category1,category2,counter)
VALUES (v_timestamp,v_category1,v_category2,v_counter);
ELSE
UPDATE item SET count=v_counter+existingCounter
WHERE timestamp=v_timestamp AND category1=v_category1 AND category2=v_category2;
END IF;
END
|
DELIMITER ;