MySQL ON DUPLICATE KEY UPDATE mit null-Spalte in eindeutiger Schlüssel

Unserer MySQL-web-analytics-Datenbank enthält eine zusammenfassende Tabelle, die aktualisiert wird, den ganzen Tag als neue Aktivität wird importiert. Wir nutzen ON DUPLICATE KEY UPDATE damit die Verdichtung überschreibt frühere Berechnungen, aber Schwierigkeiten haben, weil eine der Spalten in der Zusammenfassung Tabelle EINDEUTIGER SCHLÜSSEL ist ein optionaler Fremdschlüssel und NULL-Werte enthält.

Diese Null-Werte sind gemeint "nicht vorhanden, und alle diese Fälle sind äquivalent". Natürlich, MySQL in der Regel behandelt Null-Werte im Sinne von "unbekannt ist, und alle diese Fälle sind nicht gleichwertig".

Grundlegende Struktur ist wie folgt:

"Tätigkeit" - Tabelle enthält einen Eintrag für jede Sitzung, jede Zugehörigkeit zu einer Kampagne, 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`)
);

Einer "Zusammenfassung" Tabelle mit täglichen rollups der Gesamtzahl der Sitzungen, die in der Aktivität Tisch, ein d die Gesamtzahl der auf diese Sitzungen enthalten eine Transaktions-ID. Diese Zusammenfassungen werden aufgeteilt, mit einer für jede Kombination von Kampagne und (optional) filter. Dies ist eine nicht transaktionale Tabelle mit MyISAM.

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 tatsächliche Verdichtung Abfrage ist so etwas wie das folgende ein, wobei die Anzahl der Sitzungen und Transaktionen, dann wird die Gruppierung durch die Kampagnen-und (optional) 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 Super, außer für die Zusammenfassung von Fällen, in denen die filter_id ist NULL. In diesen Fällen werden die ON DUPLICATE KEY UPDATE-Klausel nicht mit der vorhandenen Reihe, und eine neue Zeile geschrieben wird, jedes mal. Dies ist aufgrund der Tatsache, dass "NULL != NULL". Was wir brauchen, ist jedoch "NULL = NULL" beim Vergleich der eindeutige Schlüssel.

Ich bin auf der Suche nach Ideen für workarounds oder feedback zu denen wir gekommen sind so weit. Workarounds, die wir gedacht haben, der so weit Folgen.

Löschen Sie alle summary-Einträge mit einer NULL-Schlüssel-Wert vor der Ausführung der Verdichtung. (Dies ist, was wir jetzt tun)
Dies hat den negativen Nebeneffekt der Rückgabe von Ergebnissen mit fehlenden Daten, wenn eine Abfrage ausgeführt wird, während der Verdichtung Prozess.
Ändern Sie die STANDARD-NULL-Spalte DEFAULT 0, wodurch der EINDEUTIGE SCHLÜSSEL werden konsequent abgestimmt.
Dies hat den negativen Nebeneffekt übermäßig erschwert die Entwicklung von Abfragen für die zusammenfassende Tabelle aus. Es zwingt uns, eine Menge "FALL filter_id = 0 then NULL ELSE filter_id ENDE", und macht für unangenehme Eintritt, da alle anderen Tische haben die tatsächlichen Null-Werte für filter_id.
Erstellen Sie eine Ansicht, die Renditen "FALL filter_id = 0 then NULL ELSE filter_id ENDE", und mit dieser Ansicht statt die Tabelle direkt.
Die übersichtstabelle enthält ein paar hundert tausend Zeilen, und ich habe gesagt, zeigen Sie die Leistung sehr schlecht ist.
Ermöglichen die doppelten Einträge werden erstellt, und löschen Sie alte Einträge nach Verdichtung abgeschlossen ist.
Hat der ähnliche Probleme wie löschen Sie vor der Zeit.
Fügen Sie ein Surrogat Spalte enthält 0 für NULL, und verwenden Sie, dass Ersatz in den EINDEUTIGEN SCHLÜSSEL (eigentlich könnten wir PRIMÄRE SCHLÜSSEL, wenn Sie alle Spalten, die NICHT NULL sind).

Diese Lösung scheint vernünftig, außer dass das Beispiel oben ist nur ein Beispiel; die tatsächlichen Datenbank enthält ein halbes Dutzend Zusammenfassung Tabellen, eine davon enthält vier nullwertfähige Spalten in den EINDEUTIGEN SCHLÜSSEL. Es gibt Bedenken von einigen, dass der overhead ist zu viel.

Haben Sie eine bessere Lösung, die die Struktur einer Tabelle, aktualisieren von Prozess-oder MySQL-best practice-was kann helfen?

EDIT: Zu klären, die "Bedeutung der null"

Die Daten in der Zusammenfassung die Zeilen mit NULL-Spalten werden als zusammen zu gehören, nur in dem Sinne, dass ein single "catch-all" - Zeile in der Zusammenfassung der Berichte, Zusammenfassung derjenigen Elemente, für die die Daten Punkt nicht existiert oder unbekannt ist. So im Rahmen der Zusammenfassung der Tabelle selbst, die Bedeutung ist "die Summe der Einträge, für die kein Wert bekannt". Innerhalb der relationalen Tabellen, auf der anderen Seite, diese sind wirklich NULL Ergebnisse.

Der einzige Grund, warum man Sie in einen eindeutigen Schlüssel auf der übersichtstabelle ist zu ermöglichen die automatische update (by ON DUPLICATE KEY UPDATE), wenn re-Berechnung der zusammenfassenden berichten.

Vielleicht einen besseren Weg zu beschreiben, ist es durch das spezifische Beispiel, dass man in der übersicht, Tabellen, Gruppen, Resultate geografisch durch die zip-code-Präfix des Geschäfts angegebene Adresse durch den Beklagten. Nicht alle Befragten stellen eine business-Adresse, also die Beziehung zwischen der Transaktion und die Adressen-Tabelle ist durchaus richtig NULL. In der Zusammenfassung der Tabelle für diese Daten, wird eine Zeile generiert, die für jede zip-code Präfix, mit der Zusammenfassung der Daten in diesem Bereich. Eine zusätzliche Zeile erzeugt, um die Zusammenfassung von Daten, für die keine zip-code-Präfix bekannt.

Änderung der übrigen Daten Tabellen haben eine explizite "THERE_IS_NO_ZIP_CODE" 0-Wert, und platzieren Sie einen besonderen Rekord in der ZipCodePrefix Tabelle mit diesem Wert, dass es falsch ist--, dass die Beziehung wirklich NULL ist.

InformationsquelleAutor ryandenki | 2009-08-19

4

Ich denke, dass etwas entlang der Linien von (2) ist wirklich die beste Wette — oder, zumindest, es wäre, wenn Sie wurden von Grund auf neu beginnen. In SQL, NULL bedeutet unbekannt. Wenn Sie eine andere Bedeutung, Sie sollten wirklich einen besonderen Wert verwenden, für, die, und 0 ist sicherlich eine OK-Wahl.

Sollten Sie dies tun über die gesamte Datenbank, nicht nur diese eine Tabelle. Dann sollten Sie nicht wind-up mit seltsam besonderen Fällen. In der Tat, sollten Sie in der Lage sein, um loszuwerden, eine Menge von Ihrer aktuellen (Beispiel aktuell, wenn Sie möchten, dass die Zeile mit der Zusammenfassung, wo es noch keine filter gibt, müssen Sie den Spezialfall "filter " null", im Gegensatz zu den normalen Fall "filter = ?".)

Sollten Sie auch, gehen Sie vor und erstellen Sie eine "nicht vorhanden" - Eintrag in der genannten-zu Tisch, zu halten die FK-constraint gültig (und vermeiden besonderen Fällen).

PS: Tabellen w/o Primärschlüssel sind nicht relationale Tabellen und sollte wirklich vermieden werden.

Bearbeiten 1

Hmmm, in diesem Fall, braucht man eigentlich die on duplicate key update??? Wenn Sie eine INSERT ... SELECT, dann werden Sie wahrscheinlich tun. Aber wenn Ihre app die die Daten liefern, tun Sie es einfach von hand das update durchführen (mapping zip = null zu zip is null), prüfen Sie, wie viele Zeilen geändert wurden (MySQL gibt diese), wenn 0 eine einfügen.
- Ja, die übersicht ist ganz explizit nicht um eine relationale Tabelle. Es ist einfach eine bequeme container für die Abhaltung von reporting-Ergebnisse. Meine Aussage, dass "Diese Null-Werte sind gemeint 'nicht vorhanden, und alle diese Fälle sind gleichwertig'", ist vielleicht irreführend. In den relationalen Tabellen mit den normalisierten Daten, die filter_id und andere nullable-Beziehungen, die ich Erwähnung als Teil des eindeutigen Schlüssels in der übersichtstabelle wirklich die Bedeutung "unbekannt", und sind nicht Teil einer primary-oder unique-Schlüsseln. Siehe edit, oben).
- Genau richtig. Wir verwenden von INSERT...SELECT, mit dem ON DUPLICATE KEY-Klausel gibt, zu aktualisieren Einträge im Laufe des Tages. Tatsächlich, die erste Umsetzung vor zwei Jahren war, als Sie vorschlagen--zunächst die Auswahl der Daten, die Durchführung eine zusätzliche manipulation, dann die Ausgabe einzelner EINSÄTZE, mit WHERE-Klauseln unter Berücksichtigung der IST NULL-Fall. Dieser Ansatz hat den Vorteil, dass die sperren zum einfügen einzelner Zeilen, die kürzer sind als für das INSERT...SELECT-Methode. Aber diese sperren sind nur auf dem master-Zeile der Replikation, und wir konnten alle ersetzen, die app-Seite code mit einer einzelnen SQL-Anweisung.
InformationsquelleAutor derobert
0

Ändern Sie die STANDARD-NULL-Spalte DEFAULT 0, wodurch der EINDEUTIGE SCHLÜSSEL werden konsequent abgestimmt. Dies hat den negativen Nebeneffekt übermäßig erschwert die Entwicklung von Abfragen für die zusammenfassende Tabelle aus. Es zwingt uns, eine Menge "FALL filter_id = 0 then NULL ELSE filter_id ENDE", und macht für unangenehme Eintritt, da alle anderen Tische haben die tatsächlichen Null-Werte für filter_id.

Erstellen Sie eine Ansicht, die Renditen "FALL filter_id = 0 then NULL ELSE filter_id ENDE", und mit dieser Ansicht statt die Tabelle direkt. Die übersichtstabelle enthält ein paar hundert tausend Zeilen, und ich habe gesagt, zeigen Sie die Leistung sehr schlecht ist.

Anzeigen der Leistung in MySQL 5.x wird in Ordnung sein, wie die Ansicht hat nichts, aber ersetzen eine null mit einer null. Es sei denn, Sie verwenden Aggregate/Arten in einer Ansicht, die meisten keine Abfrage gegen die Ansicht neu geschrieben werden, die von der Abfrage-Optimierer, um Sie einfach auf der zugrunde liegenden Tabelle.

Und natürlich, da es ein FK, müssen Sie erstellen Sie einen Eintrag in der genannten-zu-Tabelle mit einer id von null.

InformationsquelleAutor tpdi
0

Modernen Versionen von MariaDB (ehemals MySQL), upserts kann getan werden einfach mit insert on duplicate key update-Anweisungen, wenn Sie gehen mit Surrogat-Spalte route #5. Hinzufügen von MySQL generiert werden gespeichert Spalten-oder MariaDB persistente virtuelle Spalten anwenden der Eindeutigkeit auf die nullable Felder indirekt hält nonsense-Daten aus der Datenbank im Austausch für einige aufblasen.

z.B.
```
CREATE TABLE IF not EXISTS bar ( 
id INT PRIMARY KEY AUTO_INCREMENT, 
datebin DATE not NULL, 
baz1_id INT DEFAULT NULL, 
vbaz1_id INT WIE (COALESCE(baz1_id, -1)) GESPEICHERT sind, 
baz2_id INT DEFAULT NULL, 
vbaz2_id INT WIE (COALESCE(baz2_id, -1)) GESPEICHERT sind, 
blam DOUBLE not NULL, 
EINZIGARTIGE(datebin, vbaz1_id, vbaz2_id) 
); 

INSERT INTO bar (datebin, baz1_id, baz2_id, blam) 
VALUES ('2016-06-01', null, null, 777) 
ON DUPLICATE KEY UPDATE 
blam = WERTE(blam); 
```
Für MariaDB ersetzen GESPEICHERT, mit PERSISTENTEN, Indizes erfordern Ausdauer.

MySQL Generierte Spalten
MariaDB Virtuelle Spalten

InformationsquelleAutor JoelBondurant

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.

Bearbeiten 1