如何翻译PostgreSQL的“ merge_db” （aka upsert）函数到MySQL [英] How to translate PostgreSQL "merge_db" (aka upsert) function into MySQL

问题描述

直接从手册开始，这里是 PostgreSQL中merge_db的规范示例：

Straight from the manual, here's the canonical example of merge_db in PostgreSQL:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

在MySQL中可以将其表示为用户定义的函数吗？相对于MySQL标准的 INSERT ... ON DUPLICATE KEY UPDATE 有什么优势吗？

Can this be expressed as a user-defined function in MySQL, and if so, how? Would there be any advantage over MySQL's standard INSERT...ON DUPLICATE KEY UPDATE?

注意：专门在寻找用户定义的函数，而不是 INSERT ... ON DUPLICATE KEY UPDATE 。

Note: I'm specifically looking for a user-defined function, not INSERT...ON DUPLICATE KEY UPDATE.

推荐答案

在MySQL 5.5.14上进行了测试。

Tested on MySQL 5.5.14.

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

DELIMITER //
CREATE PROCEDURE merge_db(k INT, data TEXT) 
BEGIN
    DECLARE done BOOLEAN;
    REPEAT
        BEGIN
            -- If there is a unique key constraint error then 
            -- someone made a concurrent insert. Reset the sentinel
            -- and try again.
            DECLARE ER_DUP_UNIQUE CONDITION FOR 23000;
            DECLARE CONTINUE HANDLER FOR ER_DUP_UNIQUE BEGIN
                SET done = FALSE;
            END;

            SET done = TRUE;
            SELECT COUNT(*) INTO @count FROM db WHERE a = k;
            -- Race condition here. If a concurrent INSERT is made after
            -- the SELECT but before the INSERT below we'll get a duplicate
            -- key error. But the handler above will take care of that.
            IF @count > 0 THEN 
                UPDATE db SET b = data WHERE a = k;
            ELSE 
                INSERT INTO db (a, b) VALUES (k, data);
            END IF;
        END;
    UNTIL done END REPEAT;
END//

DELIMITER ;

CALL merge_db(1, 'david');
CALL merge_db(1, 'dennis');

一些想法：

• 您不能先进行更新，然后检查 @ROW_COUNT（） ，因为它返回实际更改的行数。如果该行已经具有您要更新的值，则该值为0。


• 此外， @ROW_COUNT（）不是复制安全。


• 您可以使用 REPLACE ... INTO 。


• 如果使用InnoDB或带有事务支持的表，您也许可以使用 SELECT ... FOR UPDATE （未经测试）。

• You can't do an update first and then check @ROW_COUNT() because it returns the number of rows actually changed. This could be 0 if the row already has the value you are trying to update.
• Also, @ROW_COUNT() is not replication safe.
• You could use REPLACE...INTO.
• If using InnoDB or a table with transaction support you might be able to use SELECT...FOR UPDATE (untested).

我认为仅使用 INSERT ... ON DUPLICATE KEY UPDATE ，此解决方案没有任何优势。

I see no advantage to this solution over just using INSERT...ON DUPLICATE KEY UPDATE.

这篇关于如何翻译PostgreSQL的“ merge_db” （aka upsert）函数到MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！