Как перевести функцию PostgreSQL merge_db (также известную как upsert) в MySQL

Прямо из руководства, вот канонический пример из merge_db в 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?

Примечание. Я специально ищу определяемую пользователем функцию, а не INSERT...ON DUPLICATE KEY UPDATE.


mysql postgresql sql upsert
person Seamus Abshere    schedule 07.07.2012    source источник

Ответы (1)


arrow_upward
5
arrow_downward

Протестировано на 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 (непроверено).

Я не вижу никаких преимуществ этого решения перед использованием INSERT...ON DUPLICATE KEY UPDATE.

person Dennis S Hennen    schedule 07.07.2012