Ich möchte Zeilen in eine Datenbank einfügen:
insert into rechnungen set name='emil maier', betrag=23
insert into rechnungen set name='tom schmidt', betrag=42
insert into rechnungen set name='kenny blubb', betrag=1
usw.
Jetzt gibt es noch die Spalte "rechnungsnummer". Die soll bei 1 starten
und dann lückenlos(!) fortlaufend nummeriert sein. Wie muss ich den
insert-Query abändern, um die rechnungsnummer entsprechend zu setzen?
Ich will eigentlich nicht zwei abfragen mit select und max machen
müssen...
-> rechnungsnummer AUTOINCREMENT (AI Häkchen in phpmyadmin )
und NICHT beim Insert befehl angeben, dann zählt myswl stumpfsinnin hoch
(bei eins beginnend)
wenn Du die rechnungsnummer nach dem insert haben willst brauchst Du
nur LAST_INSERT_ID() abzufragen (die wird automatisch als
'erfolgsmeldung' übergeben und steckt schon im query -result quasi..
Aber obacht: bei multiinsert immer nur die letzte!
So weit war ich auch schon, aber auto_increment garantiert keine
lückenlose Nummerierung...
Ich kriege so ständig Lücken in den IDs. Am Script liegt's nicht. Ich
kann auch in phpMyAdmin per Hand einzelne inserts einklimpern und das
Problem tritt auf.
Emil schrieb:> So weit war ich auch schon, aber auto_increment garantiert keine> lückenlose Nummerierung...> Ich kriege so ständig Lücken in den IDs. Am Script liegt's nicht. Ich> kann auch in phpMyAdmin per Hand einzelne inserts einklimpern und das> Problem tritt auf.
hmm, das wäre mir noch nie untergekommen.
zeig mal ein minimalbeispiel und sag ein paar (mehr
versions)informationen
Emil schrieb:> auto_increment garantiert keine lückenlose Nummerierung
Das kann nicht sein. Auto-Increment funktioniert einwandfrei.
Wo definierst du auto_increment ?
Emil schrieb:> So weit war ich auch schon, aber auto_increment garantiert keine> lückenlose Nummerierung...
DOCH!
ein table truncate setzt nur nicht die auto increment value zurück.
das musst du beim testen händisch machen falls Du das brauchst.
Du darfst NIEMALS eine rechnungsnummer manuell angeben bei einem insert,
und du darfst niemals ein replace machen
das ist das einzige was Du Dir merken musst.
replace löscht die alte ID und erstellt eine Neue.
(dazu braucht es stattdessen ein on duplicate key update)
beim insert die ID 'setzen' erzeugt entweder einen Fehler (ID vorhanden)
oder überspringt alle Nummern bis zur gesetzten ID und erhöht dann die
auto increment value.
wenn Du das beachtest ist auto inccrement exakt das was Du suchst.
Du musst halt nur aufpassen, daß Du dir das nicht mit schlechtem SQL
Code selber versaust.
(was ich fürchte hier der Fall sein wird)
svensson schrieb:> Falls mit einer Programmiersprache gearbeitet wird: einfach vorher die> höchste vorhandene ID abfragen und dann insert mit x+1 .
Und wenn mehrere Clients das parallel machen?
AUTO_INCREMENT ist auf Skalierbarkeit optimiert, und garantiert deshalb
nicht fortlaufende Nummern, insbesondere bei fehlgeschlagenen
Transaktionen.
Für deine Anforderung brauchst du einen Zähler in einer separaten
Tabelle (und SET TRANSACTION ISOLATION LEVEL SERIALIZABLE).
Auto-Increment dekrementiert sich bei einem Rollback der Transaktion
nicht.
Unhübsch, mit einem Index auf der Spalte sollte aber sowas gehen:
insert into rechnungen set name='emil maier', betrag=23
INSERT INTO rechnungen
SET name='Berta Fisch',
betrag=23,
rechnungs_nr = (SELECT IFNULL( MAX( rechnungs_nr ) , 0 ) + 1 FROM
rechnungen r))
SQuirreL schrieb:> insert into rechnungen set name='emil maier', betrag=23>
Das natürlich nicht, sondern der untere Teil, das sich per MAX den
höchsten Wert holt
Ich persönlich würde ungern eine Fortlaufende Rechnungsnummer den Kunden
mitteilen wollen. Das ermöglicht jeden der das "German Tank Problem"
(https://de.wikipedia.org/wiki/German_tank_problem) kennt und mehr als
einmal bei mir Kauft abzuschätzen wie viel Rechnungen ich schreibe.
Ich glaub auch, du solltest den Quatsch lassen. Eine Durchnumerierung
und eine ID sind grundlegend verschiedene Dinge. Bei Dingen wie einer
Rechnungsnummer macht es eventuell sogar sinn, diese zu Randomisieren,
denn es ist nicht unbedingt wünschenswert, dass wer auch immer die
Sieht, weiss, wie viele andere Rechnungen es schon gab, oder was die
vorherige Rechnungsnummer war. "Hey, ich würde gerne meine Bestellungen
100, 99, 98, ... stornieren. Meine Kontonummer hat sich auch noch
geändert, ist jetzt XYZ.". Es gibt auch keinen legitimen Einsatzzweck
für lückenlose Rechnungsnummern.
Bernd K. schrieb:> Emil schrieb:>> lückenlos>> Streiche diese unnötige Anforderung, dann wird alles einfacher.
Leider gesetzliche Forderung.
Rechnungen müssen lückenlos numeriert sein (nicht unbedingt numerisch
aufsteigend, auch MD5 hashes von aufsteigenden Nummern wären ok).
Oder es muss ein Storno zu einer nicht mehr verwendeten Rechnungsnummer
mit Grund vorliegen.
SQuirreL schrieb:> INSERT INTO rechnungen> SET name='Berta Fisch',> betrag=23,> rechnungs_nr = (SELECT IFNULL( MAX( rechnungs_nr ) , 0 ) + 1 FROM> rechnungen r))
Wird auf MySQL (und MariaDB) nicht funktionieren:
"The target table of the INSERT statement may appear in the FROM clause
of the SELECT part of the query. However, you cannot insert into a table
and select from the same table in a subquery."
Ich fürchte, um 2 Queries und einigem Aufwand zur Fehlerverarbeitung bei
parallelen Inserts wird man nicht herumkommen.
Das Problem ist aber auch nicht trivial.
Ok, aber angenommen, es sollte trotzdem lückenlos sein. Es ist schwer,
im Netz eine Lösung zu finden, da überall der Quatsch mit AUTO_INCREMENT
behauptet wird.
Michael B. schrieb:> Bernd K. schrieb:>> Emil schrieb:>>> lückenlos>>>> Streiche diese unnötige Anforderung, dann wird alles einfacher.>> Leider gesetzliche Forderung.
Stumpfsinn, wie AK schon gepostet hat, noch zusätzlich:
https://www.haufe.de/finance/buchfuehrung-kontierung/fortlaufende-nummerierung-von-rechnungen-ausnahmen_186_387400.html
Und bei den hier diskutierten Lösungen rollen sich einem die Zehennägel
auf. Businesslogik an technische Spezifika der eingesetzten DB zu
koppeln, brrr.
Rechnungsnummer mit AUTOINCREMENT, hoffentlich ist das nur Übung und
nicht produktiv.
Emil schrieb:> Ok, aber angenommen, es sollte trotzdem lückenlos sein. Es ist> schwer,> im Netz eine Lösung zu finden, da überall der Quatsch mit AUTO_INCREMENT> behauptet wird.
Das ist Applikationslogik, das muss deine Applikation lösen:
- "Fortlaufend"-Logik, Nummernschema
- Prüfung was nach dieser logik das letzte gespeicherte Maximum ist,
Bestimmung der Nachfolge
und dabei Randbedingungen betrachten wie:
- Nebenläufigkeit
- Transaktionssicherheit etcpp.
Und für die Punkte jeweils entscheiden, wo im Applikationsstack man das
unterbringt.
Software schrieb:> Stumpfsinn, wie AK schon gepostet hat
Ein Trottel kommt halt selten alleine.
Dein Artikel schreibst selbst 'fortlaufende Numerierung'.
Das Finanzamt möchte wissen, ob du eine Rechnung kassiert aber nicht
verbucht hast.
Das muss aus der Numerierung hervor gehen.
Es muss also erkennbar sein, wenn eine Rechnung (im laufenden Jahr)
fehlt.
Die Numerierung muss aber nich so sein, daß auch ein Kunde erkennt wie
viele Geschäfte du schon gemacht hast, sie kann also verwürfelt sein.
Hauptsache das Finanzamt erfährt, die der Fortlauf ist.
Und nein, eine Transaktions-Rollback ist kein Rechnersystemabsturz der
als Ausnahme (wenn man ihn dokumentiert) erlaubt wäre "am 25.11. hatten
wir einen Aussetzer, die letzte vergebene Rechnugsnummer war 456, die
nächste 500"
Michael B. schrieb:> Ein Trottel kommt halt selten alleine.>> Dein Artikel schreibst selbst 'fortlaufende Numerierung'.
Hast Du überhaupt gelesen was A.K. verlinkt hat:
"Eine lückenlose Abfolge der ausgestellten Rechnungsnummern ist nicht
zwingend." - Umsatzsteueranwendungserlass 2010
Damit ist alles gesagt. Der Gersetzgeber war im ersten Anlauf zu dumm
(wie üblich) um präzise zu formulieren, er hat "eindeutig" gemeint und
"fortlaufend" geschrieben, was soll man sonst auch von ansonsten
arbeitslosen Anwälten erwarten die in die Politik gingen weil sie zu
dumm waren eine andere brötchengebende Beschäftigung zu finden.
In diesem Erlass wurde dieser schwachsinnige Schreibfehler dann
offensichtlich korrigiert und richtiggestellt. Friede, Freude,
Eierkuchen.
Bernd K. schrieb:> Der Gersetzgeber war im ersten Anlauf zu *dumm*> (wie üblich) um präzise zu formulieren,
Sachte. Das Umsatzsteuergesetz ist älter als die elektronische
Rechnungsverarbeitung. Damals waren sequentielle Nummernkreise für das
FA sicherlich praktischer. Vielleicht stand das ja sogar schon in der
ersten Fassung von 1918 so drin.
Tom Müller schrieb:> Krass. Ich bin auch immer davon ausgegangen, dass AUTO_INCREMENT immer> nur um 1 erhöht wird. Wieder etwas gelernt.
Das ist konfigurierbar, man kann beliebige Schrittweiten konfigurieren.
Dass der aktuelle Wert bei einem Rollback nicht zurückgesetzt wird wurde
ja schon angesprochen. Oft holt sich der SQL-Server auch gleich einen
ganzen Block. Hatte den Fall mal bei einem Kunden, wo deshalb nach jedem
Serverneustart eine Lücke von rund 1000 war.
Hinzu kommt, dass man fortlaufende Rechnungsnummern ja auch gerne
gemeinsam mit dem Jahr sieht. Also beim Jahreswechsel wird wieder mit 1
begonnen. Da ist's dann sowieso Essig mit der Identity.
Bernd K. schrieb:> Damit ist alles gesagt. Der Gersetzgeber war im ersten Anlauf zu dumm> (wie üblich) um präzise zu formulieren, er hat "eindeutig" gemeint und> "fortlaufend" geschrieben,
Nur die Bürger sind halt immer noch dümmer.
Es geht nicht um eindeutig, sondern darum dass das FA Lücken erkennen
kann.
Das wird nicht erreicht wenn man Nummern in wahlfrei wechselndem Abstand
vergibt.
Man dürfte aber z.B. Stellen vertauschen
0010
0020
0030
:
0090
0001
0011
:
0091
0002
wenn dem FA diese Methode darlegt.
Oder eben 2 Nummernkreise haben
A01, E01, E02, E03, A02, A03, E04, A04...
Aber das wird Brrnd nie verstehen.
MaWin schrieb:> Es geht nicht um eindeutig, sondern darum dass das FA Lücken erkennen> kann.>> Das wird nicht erreicht wenn man Nummern in wahlfrei wechselndem Abstand> vergibt.>> Man dürfte aber z.B. Stellen vertauschen
Wenn eine intern fortlaufende Nummer mit einem Block-Cipher
verschlüssele und Base32 o. Ä. kodiere und so meine Rechnungs- oder
Seriennummern erhalte, kann man nun wirklich nicht von wahlfrei reden,
ich bezweifle aber, dass die damit klarkommen würden ;p
Wie wäre es mit einer Referenztabelle wo man immer ein bestimmtes
Kontingent an aufsteigenden Nummern vorhält. Die Nummern werden dabei
natürlich nicht mit auto_increment erzeugt. In der Rechnungstabelle lege
ich dann einen Fremdschlüssel auf diese Tabelle. Vielleicht vergleichbar
mit einer Lookup-Tabelle.
Das Argument „braucht es doch nicht, weil…“ merk ich mir. Wenn mein Chef
wieder auf der Matte steht, probiere ich mal, ob ich damit durchkomme.
Ob das Problem real ist oder nicht, spielt doch eigentlich für die
Problemlösung keine Rolle. Viele von uns werden doch mit so etwas jeden
Tag gequält. Ob es Sinn macht oder nicht, spielt dabei meist keine
Rolle.
Grüße...
Ich finde krass, dass das Internet(R) eigentlich flächendeckend
behauptet, man solle in dem Fall AUTO_INCREMENT verwenden. xD
Meine Lösung sieht jetzt so aus (aus dem Kopf grob umrissen):
lock tables rechnungen write
select max(rechnungsnummer) as max_rechnungsnummer from rechnungen
Überprüfen, ob ein Wert zurückgeliefert wurde, dann um 1 erhöhen etc.
insert into rechnungen set rechnungsnummer=[...alle Daten...]
unlock tables
Nicht schön und ggf. langsam, was mich aber nicht stört. Dass die DB das
nicht von sich aus mit einer fertigen Operation kann, ist allerdings ein
Armutszeugnis.
Software schrieb:> Stumpfsinn, wie AK schon gepostet hat, noch zusätzlich:>> https://www.haufe.de/finance/buchfuehrung-kontierung/fortlaufende-nummerierung-von-rechnungen-ausnahmen_186_387400.html
Schon komisch, Finanzgerichte und die Oberfinanzdirektion Koblenz sehen
das ganz anders...
Vielleicht ist der "Haufe Verlag" keine verlässliche Quelle für die
rechtliche Bewertungen von Vorschriften?
Davon abgesehen, ist es durchaus kritisch zu sehen, die Geschäftslogik
der Rechnungsnummern einem Implementierungsdetail der Datenbank
anzuvertrauen...
“Dass die DB das nicht von sich aus mit einer fertigen Operation kann,
ist allerdings ein Armutszeugnis.“
Nein. Sondern Zeugnis dessen, dass die Designer dieser Systeme sich
etwas dabei gedacht haben. Spiel das doch einfach mal z.B. für drei
parallele Transaktionen durch, von denen die erste und dritte nicht
COMMITen. Und jetzt?
Gäbe es eine Funktion, die lückenlose IDs ermöglichte, ginge das eben
nicht parallel. Wie das im Hintergrund funktioniert, ist doch für den
Endbenutzer völlig latte. Das ist wie, wenn man z.B. die Funktion AVG()
oder so weglassen würde. Könnte man machen, aber es ist inconvenient.
Und eine fehlende Funktion wie die o.g. ist es eben auch.
Dass das ein reales Problem ist, zeigt doch schon die erste Antwort. Die
allermeisten Leute gehen davon aus, dass es mit AUTO_INCREMENT ginge!
"Gäbe es eine Funktion, die lückenlose IDs ermöglichte, ginge das eben
nicht parallel."
Genau. Und das will niemand in einem skalierbaren Datenbankserver haben.
Ziele sind Zuverlässigkeit, Skalierbarkeit und ein stabiles
transaktionales Verhalten, aber nicht jede denkbare Geschäftslogik a
priori zu implementieren. Im Licht der Skalierbarkeit sind Locks ein
manchmal leider notwendiges Übel, das es zu minimieren gilt. Mal
abgesehen davon, dass eine solche Funktion auch nicht gut mit caching
zusammenspielt.
Auto increment, Sequences etc. erfüllen die schwache Uhrenbedingung und
das reicht. Geschäftslogik kann man dann oben draufsetzen, wenn man sie
halt unbedingt braucht. Das Leute davon ausgehen, das AUTO INCREMENT
lückenlos monoton hochzählt ist nicht das Problem der Datenbank, sondern
vielleicht der Dokumentation, oder der Leute die sie nicht lesen.
Hier mal das Beispiel von PostgreSQL:
"Sequences are based on bigint arithmetic, so the range cannot exceed
the range of an eight-byte integer (-9223372036854775808 to
9223372036854775807).
Because nextval and setval calls are never rolled back, sequence objects
cannot be used if “gapless” assignment of sequence numbers is needed. It
is possible to build gapless assignment by using exclusive locking of a
table containing a counter; but this solution is much more expensive
than sequence objects, especially if many transactions need sequence
numbers concurrently.
Unexpected results might be obtained if a cache setting greater than one
is used for a sequence object that will be used concurrently by multiple
sessions. Each session will allocate and cache successive sequence
values during one access to the sequence object and increase the
sequence object's last_value accordingly. Then, the next cache-1 uses of
nextval within that session simply return the preallocated values
without touching the sequence object. So, any numbers allocated but not
used within a session will be lost when that session ends, resulting in
“holes” in the sequence.
Furthermore, although multiple sessions are guaranteed to allocate
distinct sequence values, the values might be generated out of sequence
when all the sessions are considered. For example, with a cache setting
of 10, session A might reserve values 1..10 and return nextval=1, then
session B might reserve values 11..20 and return nextval=11 before
session A has generated nextval=2. Thus, with a cache setting of one it
is safe to assume that nextval values are generated sequentially; with a
cache setting greater than one you should only assume that the nextval
values are all distinct, not that they are generated purely
sequentially. Also, last_value will reflect the latest value reserved by
any session, whether or not it has yet been returned by nextval.
Another consideration is that a setval executed on such a sequence will
not be noticed by other sessions until they have used up any
preallocated values they have cached."
Da steht nicht nur, WIE sich eine Sequence verhält, sondern sogar WARUM.
Michael B. schrieb:> Das Finanzamt möchte wissen, ob du eine Rechnung kassiert aber nicht> verbucht hast.>> Das muss aus der Numerierung hervor gehen.
Das stimmt..
aber das heisst nicht, dass sie fortlaufend sein müssen!
Ich zähl zB immer jahr monat lfdnr.. das macht das manuelle auffinden
leichter...
Rechnungsnummer kann auch 201911004 sein
und dann in dreierschritten zählen wenn Du lustig bist,
Du musst es nur darlegen dem FA ggü.
Und falls mal eine Nummer wegbricht wird die nummer eben als storniert
markiert und die NULL Rechnung bleibt im System... auch kein Akt.
Und ja, es ist ein Problem wenn dutzende Clients auf derselben Datenbank
rumhacken,
dann kann es (mangels table lock) zu fehlern beim insert kommen,
falls zwei inserts denselben unique content haben
man kann aber die Werte ins error log schreiben;
falls das FA meckert.. hat man das errorlog und kann damit die fehlenden
Nummern dokumentieren (inkl Zeitstempel)
Im Grunde aber.. bei sauberem SQL Aufrufen bleibt's dabei
AutoIncrement ist 100% zuverlässig
sonst würden die wenigsten Webseiten überhaupt funktionieren.
Emil schrieb:> Nicht schön und ggf. langsam, was mich aber nicht stört. Dass die DB das> nicht von sich aus mit einer fertigen Operation kann, ist allerdings ein> Armutszeugnis.
Nein.
Die Autoincrement-Implementierung praktisch aller erwähnenswerten
Datenbanken hat prinzipiell exakt dasselbe Verhalten. Konkurrierende
Zugriffe können dazu führen, dass Lücken entstehen.
Und wenn die Entwickler aller erwähnenswerten Datenbanken zu dem
Ergebnis gekommen sind, dass das die sinnvollste Implementierung ist,
dann gibt es hier nur einen, der sich ein Armutszeugnis ausstellen
lassen muss. Der, der nicht begreift, warum das so sein muss und warum
das überaus sinnvoll ist...
Emil schrieb:> Ich finde krass, dass das Internet(R) eigentlich flächendeckend> behauptet, man solle in dem Fall AUTO_INCREMENT verwenden. xD
Dem ist nicht so, du hast dein Problem wohl vereinfacht formuliert und
dafür eine einfachere Lösung präsentiert bekommen.
> Meine Lösung sieht jetzt so aus (aus dem Kopf grob umrissen):>> [...]>> Nicht schön und ggf. langsam, was mich aber nicht stört. Dass die DB das> nicht von sich aus mit einer fertigen Operation kann, ist allerdings ein> Armutszeugnis.
Also die Quick'n'dirty Variante.
Relationale Datenbanken sind eben primär für die Arbeit auf Datenmengen
gedacht, nicht zur Abbildung von Geschäftslogik. Daher ist es schon in
Ordnung, dass diese Funktionalität nicht auf der Ebene verfügbar ist,
auch wenn das auf den ersten Blick komisch erscheint.
sid schrieb:> Im Grunde aber.. bei sauberem SQL Aufrufen bleibt's dabei> AutoIncrement ist 100% zuverlässig> sonst würden die wenigsten Webseiten überhaupt funktionieren.
Auto Increment macht zuverlässig das, wofür es gedacht ist. Lückenlose
Nummernvergabe aber nicht, selbst bei "sauberem SQL". Die wenigsten
Webseiten benötigen das.
