mySQL: Manuelles Hochzählen via INSERT - wie vermeide ich "0 ist nicht erlaubt"-Warnung?

[WulfmanGER]

Hallo,

ich kann für die Tabelle kein AI nutzen - daher muss ich händisch hoch zählen. Mein Query dafür schaut so aus:

INSERT IGNORE INTO rollen_test (ordering, fid, pid, category)
VALUES((SELECT MAX(ordering) FROM rollen_test rt WHERE fid=12349 AND category='abc')+1, 12349, 999, 'abc');

Auf fid+pid+category ist zusammen als unique definiert.
fid, pid und category kommen separat doppelt vor - das liegt in der Natur der Daten.
ordering soll manuell hochgezählt werden (ordering ist für fid+category einmalig)

Daher nutze ich das INSERT oben mit einem SELECT welches mir den bisherigen MAX(ordering)-Wert anzeigt. +1 = Fertig
Das klappt ... wenn es aber bisher die Category für diese fid nicht gab, kommt als WARNUNG

Warning: #1048 Feld 'ordering' darf nicht NULL sein

Der Eintrag erfolgt trotzdem (mit 0 als Wert). Kann das somit natürlich einfach ignorieren.

Mit IF sieht das ganze etwas monströs aus

INSERT IGNORE INTO rollen_test (ordering, fid, pid, category)
VALUES(
    IF((SELECT MAX(ordering) FROM rollen_test rt WHERE fid=12349 AND category='uuu')>=1,
	(SELECT MAX(ordering) FROM rollen_test rt WHERE fid=12349 AND category='uuu')+1,1), 
	12349, 9999, 'uuu')

Klappt (bisher) ...

Aber ist das sauberer? Das Feld ordering ist übrigens int und enthält seitens der fütternden Scripte IMMER ein Ordering (daher kein defaultwert gesetzt - mit 1 hab ich es aber schon probiert - klappt natürlich nicht)

Danke schon mal

 

[kieleich]

ifnull(max(ordering)+1, 1) ?

bzw. ifnull(max(ordering), 0)+1

auto increment macht vieles einfacher und die nummerierung pro kategorie hast du mit order, limit usw. ja trotzdem

ohne unique id musst du sonst bei jeder änderung auch alle anderen konditionen mitliefern, nerfig

 

[WulfmanGER]

NULL hab ich für ordering nicht gesetzt - weil es kein NULL gibt: Jeder ordering-Eintrag in der DB hat min. eine 1 stehen.
Und wäre am Ende ja trotzdem wieder so ein Query-Monster. Zum Ifnull gehört ja letztendlich das ganze SELECT wenn ich das richtig verstehe.

 

[kieleich]

?

ist dein 1. query nur eben der ausdruck der null zurückliefert mit ifnull um klammert

das ist noch eine größen ordnung weg von dem anderen murks den du da ausgegraben hast

ansonsten musst du eben die nummer selbst mit liefern ist dann halt nicht thread safe oder sonst etwas

das ordering pro kategorie+kombination führt immer zu query monstern ist eben so bei auto increment bekommst du die logik umsonst ansonsten musst du sie dir selber zaubern

du kannst dir auch die auto increment dokumenttion anschauen bei myisam kann man das abhängig von einer anderen spalte machen https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

vielleicht reicht dir dies ja doch aus

 

[Hancock]

Mal ne dumme Idee, ordering wird für eine Reihenfolge verwendet? Wenn mir jetzt die absolute Position nicht so wichtig ist (weil z.B. dann die Zeilennummer verwendet wird beim select), würde für ordering ja auch ein AI oder z.B. ein timestamp funktionieren.

Das wäre dann halt statt
ordering,fid,category,
1,1,1
2,1,1
3,1,1

(999,123,123),
1000,1,1
(1001,1,2)
...
1020,1,1
(1021,343,123),
...
3400,1,1

 

[RalphS]

Aha. Und wenn da zur selben Zeit welche eine Zeile einfügen, dann passiert was? Genau, die Scheiße fliegt einem um die Ohren.

Bitte NICHT so einen Murks machen! Einself!

Wenn autoincrement nicht geht, warum auch immer:

A Einen Trigger basteln;
B Mit Transaktionen arbeiten.

Besser noch, überlegen, was überhaupt erreicht werden soll, und dann umsetzen. Wird vermutlich wieder auf einen Trigger herauslaufen, aber das ist ja erstmal okay.

 

[WulfmanGER]

Hallo zusammen,

ist dein 1. query nur eben der ausdruck der null zurückliefert mit ifnull um klammert

da hab ich dich dann wohl missverstanden. Würde das fehlende Ergebnis (0 Zeilen) also als NULL interpretiert? Das wusste ich nicht.
Würde mein Monsterquery also nur anders schreiben - ich denke das wäre jetzt eher philosophisch was besser ist? Oder ist ifnull bei >100.000 Datensätzen besser als if >= wie ich es versucht habe? Wobei ich das script mittlerweile eh nur noch via Console laufen lassen kann und es ein einmaliger Import in der größe ist würde ich jetzt nicht sekunden pimpen wollen.

das ordering pro kategorie+kombination führt immer zu query monstern ist eben so

Mir ging es mehr um die Übersichtlichkeit. Aber ist dann halt so - ich dachte da gäbe es noch einen Trick. Bei php kann ich eine variable ja z.b. mit $variable++; hochzählen statt $variable = $variable+1. Aber mySQL ist ja keine Programmiersprache oder dergleichen

Mal ne dumme Idee, ordering wird für eine Reihenfolge verwendet? Wenn mir jetzt die absolute Position nicht so wichtig ist (weil z.B. dann die Zeilennummer verwendet wird beim select), würde für ordering ja auch ein AI oder z.B. ein timestamp funktionieren.

Ordering ist eine Sortierung innerhalb einer FID+Category-Kombi.

Natürlich kann ich jetzt beim SELECT das AI berücksichtigen ... das geht aber nur falls ich einen wirklich "abgeschlossenen "fid"-Datesatz habe den ich in richtiger Reihenfolge importiere (mit z.b. 20 sids). Wenn ich jetzt aber eine sid+category innerhalb einer fid einfügen möchte ... würde das AI stören. Der neue Wert hat dann halt 40205 statt 5 (innerhalb des gleichen fid-Datensatzes gibt es aber 10 Eintragungen ... mit 40205 würde der neue Datensatz also ans Ende meiner fid-Anzeige angezeigt. Falsch.

Wenn autoincrement nicht geht, warum auch immer:

A Einen Trigger basteln;
B Mit Transaktionen arbeiten.

Super wäre natürlich ein AI welches über mehrere Felder geht: Wenn innerhalb einer fid eine vorhandene category (bei gleicher sid) kommt, zähle ordering hoch:

Besser noch, überlegen, was überhaupt erreicht werden soll, und dann umsetzen. Wird vermutlich wieder auf einen Trigger herauslaufen, aber das ist ja erstmal okay.

... und ich denke das ist sowas? da hab ich nur noch überhaupt keine Ahnung wie sowas geht. Trigger hab ich in einigen sqllite-Datenbanken gesehen - aber so recht entschlüsseln konnte ich die nicht.

Ich hab das jetzt aber erstmal so mit dem Query gelassen (Laufzeit von 1min ist auch "ok"). Das mit dem Triggern gucke ich mir aber mal an!

 

[Phrasendreher]

Bei einer Laufzeit von 1 Minute:
Sieh zu, dass ein Index auf der Ordering-Spalte gesetzt ist! Falls das nicht der Fall ist, bremst das den Query aus.

Um das zu analysieren/optimieren, führe den Query in einem Client (mysql-Client auf Kommandozeile, phpmyadmin oder auch mein Favorit: HeidiSQL) mit explain davor aus, das zeigt Dir welche Indizes genutzt werden.

Edit. Je ein Index auf "fid" und "category" kann idR. auch nicht schaden.

 

[RalphS]

Vielleicht steh ich auf dem schlauch, aber was willst du mit der "ordering"Spalte? Damit verletzt du ja erstmal Normalformen.

Bin sowieso etwas verwirrt. Was für einen Zugriff auf die DB hast Du?

Im Sinne des Increments macht MySQLs AUTO_INCREMENT auch nicht viel mehr als einen mehr oder weniger impliziten Trigger.

Aber ich würde die Ordering-Spalte einfach weglassen und dann ORDER BY verwenden wo nötig, plus wie angedeutet Indices wo erforderlich. Wenn die Ordnung "immer" benötigt wird: View drauf und fertig.