Unsere MySQL-Webanalysedatenbank enthält eine Übersichtstabelle, die im Laufe des Tages aktualisiert wird, wenn neue Aktivitäten importiert werden. Wir verwenden ON DUPLICATE KEY UPDATE, damit die Zusammenfassung frühere Berechnungen überschreibt, haben jedoch Schwierigkeiten, da eine der Spalten im UNIQUE KEY der Übersichtstabelle ein optionaler FK ist und NULL-Werte enthält.

Diese NULL-Werte bedeuten "nicht vorhanden und alle diese Fälle sind gleichwertig". Natürlich behandelt MySQL NULL-Werte normalerweise als "unbekannt, und alle diese Fälle sind nicht gleichwertig".

Grundstruktur ist wie folgt:

Eine Tabelle "Aktivität" mit einem Eintrag für jede Sitzung, die jeweils zu einer Kampagne gehört, mit optionalen Filter- und Transaktions-IDs für einige Einträge.

CREATE TABLE `Activity` (
    `session_id` INTEGER AUTO_INCREMENT
    , `campaign_id` INTEGER NOT NULL
    , `filter_id` INTEGER DEFAULT NULL
    , `transaction_id` INTEGER DEFAULT NULL
    , PRIMARY KEY (`session_id`)
);

Eine Übersichtstabelle mit täglichen Aufsätzen der Gesamtzahl der Sitzungen in der Aktivitätstabelle und der Gesamtzahl der Sitzungen, die eine Transaktions-ID enthalten. Diese Zusammenfassungen sind aufgeteilt, wobei eine für jede Kombination aus Kampagne und (optionalem) Filter gilt. Dies ist eine nicht-transaktionale Tabelle, die MyISAM verwendet.

CREATE TABLE `Summary` (
    `day` DATE NOT NULL
    , `campaign_id` INTEGER NOT NULL
    , `filter_id` INTEGER DEFAULT NULL
    , `sessions` INTEGER UNSIGNED DEFAULT NULL
    , `transactions` INTEGER UNSIGNED DEFAULT NULL
    , UNIQUE KEY (`day`, `campaign_id`, `filter_id`)
) ENGINE=MyISAM;

Die eigentliche Zusammenfassungsabfrage sieht ungefähr so ​​aus: Sie zählt die Anzahl der Sitzungen und Transaktionen auf und gruppiert sie nach Kampagne und (optionalem) Filter.

INSERT INTO `Summary` 
    (`day`, `campaign_id`, `filter_id`, `sessions`, `transactions`)
    SELECT `day`, `campaign_id`, `filter_id
        , COUNT(`session_id`) AS `sessions`
        , COUNT(`transaction_id` IS NOT NULL) AS `transactions`
    FROM Activity
    GROUP BY `day`, `campaign_id`, `filter_id`
ON DUPLICATE KEY UPDATE
    `sessions` = VALUES(`sessions`)
    , `transactions` = VALUES(`transactions`)
;

Alles funktioniert einwandfrei, bis auf die Zusammenfassung der Fälle, in denen die filter_id NULL ist. In diesen Fällen stimmt die ON DUPLICATE KEY UPDATE-Klausel nicht mit der vorhandenen Zeile überein, und es wird jedes Mal eine neue Zeile geschrieben. Dies liegt an der Tatsache, dass "NULL! = NULL". Was wir jedoch brauchen, ist "NULL = NULL", wenn wir die eindeutigen Schlüssel vergleichen.

Ich bin auf der Suche nach Ideen für Problemumgehungen oder Feedback zu denjenigen, die wir bisher entwickelt haben. Es folgen Problemumgehungen, an die wir bisher gedacht haben.

Löschen Sie alle Zusammenfassungseinträge, die einen NULL-Schlüsselwert enthalten, bevor Sie die Zusammenfassung ausführen. Dies hat den negativen Nebeneffekt, dass Ergebnisse mit fehlenden Daten zurückgegeben werden, wenn während des Zusammenfassungsprozesses eine Abfrage ausgeführt wird.

Ändern Sie die Spalte DEFAULT NULL in DEFAULT 0, damit der EINZIGARTIGE SCHLÜSSEL konsistent zugeordnet werden kann. Dies hat den negativen Nebeneffekt, dass die Entwicklung von Abfragen für die Übersichtstabelle zu kompliziert wird. Es zwingt uns, eine Menge von "CASE filter_id = 0 THEN NULL ELSE filter_id END" zu verwenden, und erschwert das Verbinden, da alle anderen Tabellen tatsächliche NULLs für die filter_id haben.

Erstellen Sie eine Sicht, die "CASE filter_id = 0 THEN NULL ELSE filter_id END" zurückgibt, und verwenden Sie diese Sicht anstelle der Tabelle direkt. Die Übersichtstabelle enthält einige hunderttausend Zeilen, und mir wurde mitgeteilt, dass die Anzeigeleistung ziemlich schlecht ist.

Lassen Sie zu, dass die doppelten Einträge erstellt werden, und löschen Sie die alten Einträge, nachdem die Zusammenfassung abgeschlossen ist. Hat ähnliche Probleme wie beim vorzeitigen Löschen.

Fügen Sie eine Ersatzspalte hinzu, die 0 für NULL enthält, und verwenden Sie diese Ersatzspalte im EINZIGARTIGEN SCHLÜSSEL (tatsächlich könnten wir PRIMARY KEY verwenden, wenn alle Spalten NICHT NULL sind).
Diese Lösung erscheint vernünftig, mit der Ausnahme, dass das obige Beispiel nur ein Beispiel ist. Die eigentliche Datenbank enthält ein halbes Dutzend Übersichtstabellen, von denen eine vier nullfähige Spalten im EINZIGARTIGEN SCHLÜSSEL enthält. Einige befürchten, dass der Overhead zu hoch ist.

Haben Sie eine bessere Problemumgehung, Tabellenstruktur, einen besseren Aktualisierungsprozess oder eine bewährte Methode für MySQL, die Ihnen helfen kann?

EDIT: Um die "Bedeutung von Null" zu klären

Die Daten in den Zusammenfassungszeilen, die NULL-Spalten enthalten, werden nur in dem Sinne als zusammengehörig betrachtet, dass sie in Zusammenfassungsberichten eine einzige "Sammel" -Zeile darstellen, in der die Elemente zusammengefasst werden, für die dieser Datenpunkt nicht existiert oder unbekannt ist. Im Kontext der Übersichtstabelle selbst ist die Bedeutung "die Summe der Einträge, für die kein Wert bekannt ist". In den relationalen Tabellen hingegen sind dies wirklich NULL-Ergebnisse.

Der einzige Grund, sie in einen eindeutigen Schlüssel in der Übersichtstabelle einzufügen, besteht darin, die automatische Aktualisierung (durch ON DUPLICATE KEY UPDATE) bei der Neuberechnung der Übersichtsberichte zu ermöglichen.

Vielleicht lässt sich dies besser anhand des konkreten Beispiels beschreiben, bei dem eine der Zusammenfassungstabellengruppen geografisch nach dem Postleitzahlpräfix der vom Befragten angegebenen Geschäftsadresse resultiert. Nicht alle Befragten geben eine Geschäftsadresse an, daher ist die Beziehung zwischen der Transaktion und der Adresstabelle ganz richtig NULL. In der Übersichtstabelle für diese Daten wird für jedes Postleitzahlpräfix eine Zeile generiert, die die Zusammenfassung der Daten in diesem Bereich enthält. Eine zusätzliche Zeile wird generiert, um die Zusammenfassung der Daten anzuzeigen, für die kein Postleitzahlpräfix bekannt ist.

Das Ändern der restlichen Datentabellen auf einen expliziten "THERE_IS_NO_ZIP_CODE" 0-Wert und das Platzieren eines speziellen Datensatzes in der ZipCodePrefix-Tabelle, der diesen Wert darstellt, ist nicht korrekt - diese Beziehung ist wirklich NULL.