没有UP DUPLICATE KEY的MySQL UPSERT [英] MySQL UPSERT without ON DUPLICATE KEY

问题描述

我希望在具有以下字段的MySQL表上使用 UPSERT （如果存在，则添加 UPDATE ，否则插入）： / p>

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`)
)

此表已经投入生产，并且（timestamp，category1，category2）的组合对于我的 INSERT 是唯一的，但不适用于整个表格的内容。这就是为什么我不能使用 ON DUPLICATE KEY （双重复制键）功能的原因。

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.

为澄清UNIQUE问题：
如果category1 = 9，那么这是我的插入内容，并且（时间戳，category1，category2） 是唯一的。但是该表已投入生产，如果category1 = 1,2，则（timestamp，category1，category2）不是唯一的。
据我所知，我无法添加 UNIQUE 键，因为这会给类别1 + 2带来麻烦。

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

我使用PHP和MySQL，因此两种语言的任何组合都可以。

I use PHP and MySQL, so any combination of both languages is fine.

我已经尝试过，但是花费太多时间是：

What I tried already, but takes too much time is:

• 单PHP SELECT 语句，然后 UPDATE 或 INSERT


• INSERT 然后执行所有操作，然后 UPDATE 和 Delete 重复的行

• 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 ;

这篇关于没有UP DUPLICATE KEY的MySQL UPSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！