mysql Insert Row if not exists

[Jörg]

Hallo,

wie fügt man in mysql eine zeile ein, wenn es sie nocht nicht gibt?

Ich habe versucht:

INSERT INTO table(
    ID,
    Bla,
    Blu
)
VALUES(
    NULL,
    1,
    0
)
WHERE NOT
    EXISTS(
    SELECT
        ID
    FROM
        table
    WHERE
       Bla = 1 AND Blu = 1
)

Das mag mysql aber nicht. Alternative zum INSERT soll übrigens nichts sein, also kein UPDATE. Das erwähne ich wegen insert on duplicate key update.

Jörg

[dedlfix]

Tach!

wie fügt man in mysql eine zeile ein, wenn es sie nocht nicht gibt?

Man legt einen Unique Key über die Felder, die den Datensatz eindeutig machen. (Einen Key über alle Felder, nicht jedem Feld einen eigenen Key geben!) Dann fügt man den Datensatz ganz normal mit Insert ein. Wenn das klappt, gab es den Datensatz noch nicht. Wenn der Vorgang mit Fehler wegen Unique-Key-Verletzung abbricht, gab es den schon. Man muss die Fehlernummer genau auswerten, um diese Meldung von anderen zu unterscheiden. Denn diesen Fehler erwartet man ja und muss ihn nicht dem Administrator oder Logfile melden, die anderen aber schon.

dedlfix.

[Rolf B]

Hallo dedlfix,

Zeit für den Klassiker:

Rolf

[Jörg]

Tach!

wie fügt man in mysql eine zeile ein, wenn es sie nocht nicht gibt?

Man legt einen Unique Key über die Felder, die den Datensatz eindeutig machen. (Einen Key über alle Felder, nicht jedem Feld einen eigenen Key geben!) Dann fügt man den Datensatz ganz normal mit Insert ein. Wenn das klappt, gab es den Datensatz noch nicht. Wenn der Vorgang mit Fehler wegen Unique-Key-Verletzung abbricht, gab es den schon. Man muss die Fehlernummer genau auswerten, um diese Meldung von anderen zu unterscheiden. Denn diesen Fehler erwartet man ja und muss ihn nicht dem Administrator oder Logfile melden, die anderen aber schon.

Hi dedlfix,

du spielst vermutlich auf einen error1062 an. Habe ich auch schon drüber nachgedacht. Muss ich mal ausprobieren.

Jörg

[dedlfix]

Tach!

du spielst vermutlich auf einen error1062 an.

Das weiß ich nicht auswendig. Wenn ich das Verhalten brauche, bekomme ich die Nummer ja vom System präsentiert, und kann sie dann in die Fehlerauswertung einbauen.

dedlfix.

[Jörg]

Auch Tach,

du spielst vermutlich auf einen error1062 an.

Das weiß ich nicht auswendig. Wenn ich das Verhalten brauche, bekomme ich die Nummer ja vom System präsentiert, und kann sie dann in die Fehlerauswertung einbauen.

Der 1062er ist auch der einzige, den ich auswenig kenne 😉

Jörg

[Rolf B]

Hallo Jörg,

dafür gibt es meines Wissens keine Zusatzklausel. Und INSERT WHERE gibt's eh nicht.

Deine Abfrage ist aber nicht logisch. Du fügst Bla=1 und Blu=0 ein, aber im WHERE fragst Du Bla=1 und Blu=1 ab. Ist das das, was Du willst? Oder ein Tippfehler?

Wenn Du sicherstellen willst, dass die gleiche Kombination von BLA und BLU Werten nicht doppelt in der DB vorkommt, könntest Du einen UNIQUE INDEX über BLA und BLU anlegen. Das schlägt zwei Klappen mit einer Fliege: Die Kollisionsprüfung muss keinen Table Scan machen, und das Einfügen einer existierenden BLA/BLU Kombination wird verhindert, ohne dass Du es abfragen musst. Statt dessen sollte Fehlercode 1062 (Duplicate Key) kommen.

Dass Du Die ID auf NULL setzt, ok, da hab ich mal wieder was gelernt. Nach meiner Kenntnis muss man AUTO INCREMENT Spalten beim INSERT weglassen, wenn man den Auto-Inkrement haben will. Aber es ist schon OK, MYSQL unterstützt das so (solange ID eine NOT NULL Spalte ist).

Rolf

[Jörg]

Hallo Rolf,

Deine Abfrage ist aber nicht logisch. Du fügst Bla=1 und Blu=0 ein, aber im WHERE fragst Du Bla=1 und Blu=1 ab. Ist das das, was Du willst? Oder ein Tippfehler?

Ja, Tippfehler.,

Wenn Du sicherstellen willst, dass die gleiche Kombination von BLA und BLU Werten nicht doppelt in der DB vorkommt, könntest Du einen UNIQUE INDEX über BLA und BLU anlegen.

Ja, dedlfix schlägt das auch schon vor. Und ja, das sollte einen 1062er im Fehlerfall auswerfen. Da ich Nummernkreise hochzähle, kann man das sogar ganz nett mit einer do...while (mysqli_error($con) == 1062)-Schleife lösen.

Dass Du Die ID auf NULL setzt, ok, da hab ich mal wieder was gelernt. Nach meiner Kenntnis muss man AUTO INCREMENT Spalten beim INSERT weglassen, wenn man den Auto-Inkrement haben will. Aber es ist schon OK, MYSQL unterstützt das so (solange ID eine NOT NULL Spalte ist).

Genau. Weglassen ginge natürlich auch. Und auch ich habe was gelernt, wenn das "Nullen" in anderen Datenbanksystemen nicht zulässig ist 😉

Jörg

[Jörg]

Korrektur:

Da ich Nummernkreise hochzähle, kann man das sogar ganz nett mit einer do...while (mysqli_error($con) == 1062)-Schleife lösen.

do...while ($db->errno == 1062)-Schleife

Jörg

[Jörg]

Korrektur:

Da ich Nummernkreise hochzähle, kann man das sogar ganz nett mit einer do...while (mysqli_error($con) == 1062)-Schleife lösen.

do...while ($db->errno == 1062)-Schleife

oder natürlich auch do...while(mysqli_errno($con) == 1062)-Schleife

(ich verwende wahlweise $db und $con)

[Rolf B]

Hallo Jörg,

Da ich Nummernkreise hochzähle

Was zum grundgütigen Geier tust Du da? Probierst Du durch, ob eine bestimmte Kombi von "Bla" und "Blu" noch frei ist und machst dafür dann einen INSERT?

Das riecht nach ineffizientem Algorithmus und Bedarf nach besseren Ideen. Magst Du uns erzählen, wofür Du das brauchst?

Rolf

[Jörg]

Hi Rolf,

Da ich Nummernkreise hochzähle

Was zum grundgütigen Geier tust Du da? Probierst Du durch, ob eine bestimmte Kombi von "Bla" und "Blu" noch frei ist und machst dafür dann einen INSERT?

Das riecht nach ineffizientem Algorithmus und Bedarf nach besseren Ideen. Magst Du uns erzählen, wofür Du das brauchst?

Vergiss die Blu und Bla, die waren im Beispiel einfach "konstruiert".

Was das Nummernkreis hochzählen angeht, da geht es um den Prozess der Rechnungsnummernerstellung, die einer race condition unterliegt, die ich mit einem einfachen INSERT on DUPLICATE UPDATE nicht einfangen kann, also muss der komplette Prozess im Falle des Falles neu durchlaufen werden.

Im aktuellen Beispiel geht es wieder um etwas anderes, nämlich das automatische Erstellen von Vorgängen, zugehörigen Rechnungen und deren Zertifizierung und Versand. Läuft monatlich über cron, aber ab und an ist mal ein Haken drin und dann ist es relativ aufwändig, den Prozess entweder manuell zuende zu führen oder automatisiert an der Stelle fortzuführen, die für den Abbruch sorgte.

Deshalb habe ich eine Kontrolltabelle angelegt, in die vorab alle benötigten Dokumente und deren Zeitpunkt der Fertigstellung eingetragen werden. Wenn dann mal irgendwan ein Abbruch erfolgt, kann ich deutlich leichter der autom. Prozess wieder anstoßen.

Jörg

[Mitleser 2.0]

Was das Nummernkreis hochzählen angeht, da geht es um den Prozess der Rechnungsnummernerstellung, die einer race condition unterliegt, die ich mit einem einfachen INSERT on DUPLICATE UPDATE nicht einfangen kann, also muss der komplette Prozess im Falle des Falles neu durchlaufen werden.

Klingt nach einer Transaktion

[Rolf B]

Hallo Mitleser,

stimmt wohl, race conditions rufen nach Transaktionen. Man muss nur aufpassen, dass man dann die race condition nicht gegen einen Deadlock eintauscht. Oder gegen einen Flaschenhals.

Und die Frage, warum es beim Generieren einer Rechnungsnummer überhaupt zu Duplikaten kommen kann, ruft die Folgefrage auf: Warum keine AUTO INCREMENT Rechnungsnummer? Aber das ist vermutlich die alte Seuche, die aus manuell geführten Verwaltungen kommt: Solche Nummern müssen sprechend sein und mindestens mal mit der Jahreszahl anfangen, z.B. 210017. Natürlich ohne Jahrhundert, um der Enkelgeneration ein weiteres Y2100^[1]-Problem zu vererben. Und mit zu wenig Stellen (10000 Rechnungen im Jahr? Hamma nich, höchstens 1000 oder so). Und wenn so ein Prozess erstmal da ist, wird man ihn nur noch los, wenn man die Bude abbrennt und neu gründet.

• es sollte eine DB-Engine sein, die Row Locks kennt (bei MySQL/Maria: InnoDB, MyISAM kennt nur Table Locks)
• die Sätze, die auf die Erstellung der Rechnungsnummer Einfluss haben, sollten mit SELECT ... FOR UPDATE gelesen werden, so dass Folge-SELECTs von anderen Connections warten müssen. Wenn gar nichts anderes geht, macht man für solche Zwecke eine separate Semaphoren-Tabelle, die man mit table lock reserviert oder in der man einzelne Sätze per row lock sperrt, die für den jeweiligen zu serialisierenden Ablauf vorgesehen sind.
• Es sollte nach aller Möglichkeit nur ein einziger Satz FOR UPDATE gelockt werden, denn ein zweiter Lock immer zu einem Deadlock führen wenn ein anderer Client die gleichen Objekte in anderer Reihenfolge locken will
• Die Transaktion sollte ausschließlich das Erstellen und Speichern der Rechnungsnummer umfassen, nach sollten die Locks erstmal wieder freigegeben werden, um die DB nicht zu lange zu blockieren.

Rolf

---

1. Nach "Y2K" die übernächste Goldmine für Sanierungsprogrammierer. Sprechende Nummernkreise ohne Jahrhundert laufen über, und der 29.02.2100 wird auch spannend. Vorher ist noch "Y2K38" dran - der time_t Überlauf 2038. Aber die Kisten, die dafür empfänglich sind, versagen vermutlich schon 2036, wenn NTP überläuft, weil sie zum veralteten time_t garantiert auch einen veralteten NTP-RfC implementieren. ↩︎

[Jörg]

Hallo Rolf,

Und mit zu wenig Stellen (10000 Rechnungen im Jahr? Hamma nich, höchstens 1000 oder so). Und wenn so ein Prozess erstmal da ist, wird man ihn nur noch los, wenn man die Bude abbrennt und neu gründet.

😂😂 Solche Schätzchen kenne ich auch. Mein System gehört nicht dazu.😊

Nach "Y2K" die übernächste Goldmine für Sanierungsprogrammierer. Sprechende Nummernkreise ohne Jahrhundert laufen über, und der 29.02.2100 wird auch spannend. Vorher ist noch "Y2K38" dran - der time_t Überlauf 2038. Aber die Kisten, die dafür empfänglich sind, versagen vermutlich schon 2036, wenn NTP überläuft, weil sie zum veralteten time_t garantiert auch einen veralteten NTP-RfC implementieren.

Sehr schön zusammengefasst. 👍

Jörg