Abgleichen von Tabelleninhalten unter mySQL (Analog MERGE INTO)

Für den performanten und sicheren Datenabgleich („UPSERT“) stehen bei den grossen Datenbanksystemen die sogenannten Merge-Statements bereit (MERGE INTO …). Dies ist unter mySQL aktuell nicht möglich, kann jedoch sinngemäss mit INSERT .. ON DUPLICATE KEY gelöst werden.

Für dieses Beispiel brauchen wir 2 Tabellen, genaugenommen je eine Quell – und Zieltabelle.

CREATE TABLE src (
    src_id INT(11) NOT NULL AUTO_INCREMENT,
    src_value1 VARCHAR(255) NOT NULL,
    src_value2 VARCHAR(255) NOT NULL,
    src_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (src_id),
    KEY idx_src_ts (src_ts)
) ENGINE=InnoDB;
 
CREATE TABLE tgt (
    tgt_id INT(11) NOT NULL,
    tgt_value1 VARCHAR(255) NOT NULL,
    tgt_value2 VARCHAR(255) NOT NULL,
    tgt_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (tgt_id),
    KEY idx_tgt_ts (tgt_ts)
) ENGINE=InnoDB;

Ein paar Testdaten dazu:

INSERT INTO src (src_value1, src_value2) 
VALUES
    ('a', 'aa'),
    ('b', 'bb'),
    ('c', 'cc'),
    ('d', 'dd'),
    ('e', 'ee'),
    ('f', 'ff'),
    ('g', 'gg'),
    ('h', 'hh');

Im Gegensatz zu MERGE ist es unter mySQL nicht möglich, die Verknüpfung direkt zu bestimmen (.. ON sourcetable.key = targettable.somekey.. ). Unter mySQL werden automatisch alle Unique Indexe angewendet (UNIQUE/PRIMARY).
In den Beispieltabellen wurden daher jeweils ein Primary Key definiert, auf welchem der „Merge“ stattfinden soll. Wird ein solcher beim Einfügen in die Zieltabelle „verletzt“, kann der Datensatz über ON DUPLICATE KEY abgefangen und weiter verarbeitet werden. Das ganze ist somit nichts anderes als ein normaler Insert mit „Errorhandling“.

Beispiel

INSERT
INTO
    tgt
    (   
        tgt_id,
        tgt_value1,
        tgt_value2
    )
SELECT
    src_id,
    src_value1,
    src_value2
FROM
    src
ON DUPLICATE KEY 
UPDATE 
    tgt_value1 = VALUES(tgt_value1),
    tgt_value2 = VALUES(tgt_value2)

Existiert beim Insert der Key in der Zieltabelle bereits, werden lediglich alle (definierten) Attribute des Datensatzes aktualisiert, und in unserem Fall der Timestamp (ON UPDATE ..) aktualisert. Sind die Attribute des Datensatzes unverändert – erfolgt kein Update.

Das Statement oben kann daher beliebig oft ausgeführt werden – solange sich der Inhalt der Quelltabelle nicht verändert bleiben auch die Informationen inklusive Timestamp in der Zieltabelle unverändert.

Zu Testzwecken verändern wir nun einen Datensatz in der Quelltabelle:

UPDATE src SET src_value2 = 'wohooooooo' WHERE src_value1 = 'a'

Wird nun das „Merge“ Statement erneut ausgeführt, wird der betroffene Datensatz auch in der Zieltabelle aktualisiert – und nur dieser.

Natürlich macht es in der Praxis keinen Sinn eine 1:1 Kopie einer Tabelle zu verwalten. Als Quelle kann natürlich auch eine komplexe Abfrage oder ein View dienen, resp. mehrere davon pro Zieltabelle. In diesem Fall muss auf der Zieltabelle der entsprechende Primary-Key, resp. besser Unique-Index angelegt werden.

Im Gegensatz zu den „echten“ MERGE-Statements sind hier leider keine zusätzlichen Bedingungen beim Update möglich.

Schlagworte: , ,

Kommentieren


Social Widgets powered by AB-WebLog.com.