mysql - 用于针对重复行插入或更新记录的存储过程

Question

我试图创建一种方法，该方法根据重复行的存在来更新或插入新行。但是我又一次又出错了。

CREATE PROCEDURE import_leads(uid INT(9), pid VARCHAR(50), ctid VARCHAR(50), cid VARCHAR(50), cname VARCHAR(50), perms ENUM('0','1','',''), subs DATETIME)

IF (NOT EXISTS (SELECT COUNT(*) FROM f_list WHERE page_id = pid and user_id = uid and client_thread_id = ctid and client_id = cid ))
   BEGIN
      INSERT INTO f_list (page_id,user_id,client_thread_id,client_id,client_username,permission,subscribed_at) VALUES(pid,uid,ctid,cid,cname,perms,subs);
   END
ELSE
   BEGIN
      UPDATE f_list SET page_id=pid, user_id=uid, client_thread_id=ctid, client_id=cid, client_username=cname, permission=perms, subscribed_at=subs WHERE page_id = pid and user_id = uid and client_thread_id = ctid and client_id = cid;
   END
END IF

我得到的错误如下：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN
INSERT INTO f_list (page_id,user_id,client_thread_id,client_id,client_use' at line 3

Answer 1

首先，您的IF并没有达到您的期望。您的代码将永远不会执行ELSE，因为没有GROUP BY的聚合查询总是返回一行。

其次，MySQL具有一步完成此功能的功能：

INSERT INTO f_list (page_id, user_id, client_thread_id, client_id, client_username, permission, subscribed_at)
    VALUES (pid, uid, ctid, cid, cname, perms, subs)
    ON DUPLICATE KEY UPDATE
        -- page_id = pid, user_id = uid, client_thread_id = ctid, client_id = cid,  -- these are not needed because they are duplicates
        client_username = cname, permission = perms, subscribed_at = subs ;

为此，您需要在 page_id, user_id, client_thread_id, client_id)上使用唯一索引：

create unique index unq_f_list_4 on f_list(page_id, user_id,  client_thread_id, client_id);