[MySQL] INSERT INTO x SET volgnummer = <hoogste nummer + 1>

[RePlayer]

Ik wil een query draaien, die een entry toevoegt in de tabel facturen met o.a. de velden id, volgnummer, naam.

Het veld volgnummer moet eigenlijk direct bij de insert de waarde hebben van het hoogst voorkomende volgnummer + 1, anders moet ik een extra query draaien om het laatste volgnummer te vinden.

Ik kan niet het veld id met autoincrement gebruiken, omdat er meerdere administraties in de tabel terechtkomen, en dus meerdere sets van volgnummers.

Is het misschien beter/netter om in een aparte tabel bij te houden wat het laatste volgnummer van een administratie is? Is het dan nog wel mogelijk om slechts één query te gebruiken?

[Kalentum]

Wat je wilt kan gewoon in MySQL:
http://dev.mysql.com/doc/...ample-auto-increment.html

Je moet alleen een primaire sleutel definieren over twee kolommen.

[PaulZ]

Lijkt mij dat je toch minimaal uit komt op twee queries: 1 insert en 1 om het nummer op te halen (waar dat ook staat).

[ Voor 35% gewijzigd door PaulZ op 17-01-2009 15:36 . Reden: Iets beter lezen... ]

[RePlayer]

rutgerw schreef op zaterdag 17 januari 2009 @ 15:30:
Wat je wilt kan gewoon in MySQL:
http://dev.mysql.com/doc/...ample-auto-increment.html

Je moet alleen een primaire sleutel definieren over twee kolommen.

Twee kolommen met auto increment bedoel je? Dus bijvoorbeeld adm1_volgnummer en adm2_volgnummer?
Als de gebruiker dan nog een administratie wil voeren moet de tabel-layout gewijzigde worden. Dat is eigenlijk niet de bedoeling..

[rogierslag]

via een SUBSELECT haal je het hoogste volgnummer op waar je een aan toevoegd. Zo gebruik je slechts één query en weet je zeker dat er geen andere query tussendoor fietst.

Ik weet niet meer precies hoe subselects werken in inserts, maar volgens mij ging het zo

code:

INSERT INTO x (id,veld1,veld2) VALUES ( (SELECT MAX(id) FROM x WHERE iets='iets' ) , 'waarde veld1','waarde veld2')

[Cousin Boneless]

Oppassen met het eerste record.. null + 1 = null
dus

SQL:

ifnull(max(volgnummer) +1,1)

[remco_k]

RePlayer schreef op zaterdag 17 januari 2009 @ 15:23:
Ik kan niet het veld id met autoincrement gebruiken, omdat er meerdere administraties in de tabel terechtkomen, en dus meerdere sets van volgnummers.

Waarom zou je dat willen?
Kies gewoon voor 1 uniek volgnummer. Dus 1 kolom autoincrement.
Dat Administratie 1 dan eerst volgnummer 2500 krijgt, dan administratie 2 2501 en dan administratie 1 2502, lekker belangrijk. Sterker nog, is misschien zelfs wel wenselijk omdat een volgnummer nu echt uniek is.

[Kalentum]

RePlayer schreef op zaterdag 17 januari 2009 @ 15:49:
[...]


Twee kolommen met auto increment bedoel je? Dus bijvoorbeeld adm1_volgnummer en adm2_volgnummer?
Als de gebruiker dan nog een administratie wil voeren moet de tabel-layout gewijzigde worden. Dat is eigenlijk niet de bedoeling..

Nee: een primary key over twee kolommen, bv factuur_id en administratie_id. Die factuur_id maak je autoincrement en administratie_id is een ID wat je voor de insert al weet. Je insert wordt dan iets als:

SQL:

INSERT INTO facturen (administratie_id, bedrag) VALUES (12,43.22);

In de mysql handleiding staat dit uitgelegd met een voorbeeld.

[RePlayer]

remco_k schreef op zaterdag 17 januari 2009 @ 16:25:
[...]

Waarom zou je dat willen?
Kies gewoon voor 1 uniek volgnummer. Dus 1 kolom autoincrement.
Dat Administratie 1 dan eerst volgnummer 2500 krijgt, dan administratie 2 2501 en dan administratie 1 2502, lekker belangrijk. Sterker nog, is misschien zelfs wel wenselijk omdat een volgnummer nu echt uniek is.

Dat lijkt me geen geweldige oplossing. Wat doe je dan als er meer dan 2500 facturen komen? Je kunt natuurlijk een aantal nemen dat nooit gehaald zal worden, maar dan nog vind ik het niet mooi.

Ik heb het volgende bedacht: in de tabel facturen zal bestaan uit tenminste de kolommen id, administratie_id, document_nummer.

Bij het invoegen met subquery zoek ik het laatste nummer voor het administratie id dat van toepassing is.

[remco_k]

RePlayer schreef op zaterdag 17 januari 2009 @ 17:17:
[...]


Dat lijkt me geen geweldige oplossing. Wat doe je dan als er meer dan 2500 facturen komen? Je kunt natuurlijk een aantal nemen dat nooit gehaald zal worden, maar dan nog vind ik het niet mooi.

Ik heb het volgende bedacht: in de tabel facturen zal bestaan uit tenminste de kolommen id, administratie_id, document_nummer.

Bij het invoegen met subquery zoek ik het laatste nummer voor het administratie id dat van toepassing is.

Je hebt me niet begrepen of je weet niet wat een autincremented kolom is.
Die 2500 was slechts een voorbeeld waar je op een dag op uitkomt, en waarna je ook gewoon door kan gaan. Autoincrement begint gewoon op 1 en loopt dan door veel verder dan 2500.

[flat]

remco_k schreef op zaterdag 17 januari 2009 @ 16:25:
[...]

Waarom zou je dat willen?
Kies gewoon voor 1 uniek volgnummer. Dus 1 kolom autoincrement.
Dat Administratie 1 dan eerst volgnummer 2500 krijgt, dan administratie 2 2501 en dan administratie 1 2502, lekker belangrijk. Sterker nog, is misschien zelfs wel wenselijk omdat een volgnummer nu echt uniek is.

Alleen vindt de Belastingdienst dat niet zo tof. Facturen moeten doorlopend genummerd worden.

[cariolive23]

Is het misschien beter/netter om in een aparte tabel bij te houden wat het laatste volgnummer van een administratie is?

Wanneer jij in één tabel meerdere administraties gaat zetten en voor iedere administratie moet een eigen serie nummers worden gebruikt, ben je wel verplicht om per administratie ergens deze nummering bij te houden. En dat gaat je niet lukken met auto_increment, je kunt in MySQL maar één auto_increment gebruiken (die je heb je overigens wel nodig voor het id van het record). In PostgreSQL zou je per administratie een eigen sequence kunnen aanmaken, in MySQL zul je ergens in een tabel moeten bijhouden waar je bent gebleven met de nummering.

Let op: Ga dit heel goed testen, ook met meerdere gelijktijdige gebruikers en doe heel erg je best om tijdens het testen dubbele nummers aan te maken. Met een sequence (PostgreSQL) gaat je dat niet lukken, maar wanneer je zelf wat moet maken in MySQL, dan heb ik m'n twijfels. Misschien is het handig om gewoon met een botte table lock een tabel even dicht te gooien voor anderen. Dan kun je veilig een nieuw nummer aanmaken zonder dat er een kans op dubbele nummers bestaat. De performance zal dan niet al te best zijn, maar dat is dan niet anders. Beter een langzaam maar correct werkend systeem dan een snel maar volledig onbetrouwbaar ding waar je dus niet op kunt vertrouwen. Of overstappen op PostgreSQL, ben je van al je problemen verlost.

[HawVer]

Ik zou er voor kiezen om het in een aparte tabel bij te houden. Dat gebruiken we hier ook voor de factuurnummering in ons administratie pakket. Voor elke reeks een apart record met iig de huidige waarde. Het voordeel hiervan dat eventuele fouten in de nummering makkelijker op te lossen zijn. Als je alle database acties precies binnen één transactie per factuur laat lopen kan het haast niet fout gaan. En neem de adviezen van cariolive23 ter harte. Eventueel kun je ook record level locking gebruiken. Zorg ervoor dat de tijd die je een record/tabel lockt zo kort mogelijk is. Je wilt niet dat andere gebruikers lang moeten wachten, of een time out krijgen.

[cariolive23]

Als je alle database acties precies binnen één transactie per factuur laat lopen kan het haast niet fout gaan.

Goede opmerking, je moet met dit soort dingen inderdaad wel transacties gebruiken. Vergeet dus niet om de innoDB-engine van MySQL te gebruiken, anders gaat dat niet werken. Ook al krijg je geen foutmeldingen op het starten of afsluiten van een transactie wanneer een transactie helemaal niet door de engine wordt ondersteund...

[djluc]

Je maakt toch sowieso een unique key aan op klantnummer + factuurnummer? Dan is het hele issue met dubbele vermeldingen sowieso niet mogelijk.

[cariolive23]

djluc schreef op zondag 18 januari 2009 @ 15:51:
Je maakt toch sowieso een unique key aan op klantnummer + factuurnummer? Dan is het hele issue met dubbele vermeldingen sowieso niet mogelijk.

Klopt, maar je kunt redelijk gek worden van de vele foutmeldingen. Met een goed systeem, zie genoemde sequence in PostgreSQL, speelt dit hele probleem niet en is de unique-key niet meer dan een laatste redmiddel die je nooit nodig zult hebben.

[Kalentum]

cariolive23 schreef op zondag 18 januari 2009 @ 13:39:
[...]

Wanneer jij in één tabel meerdere administraties gaat zetten en voor iedere administratie moet een eigen serie nummers worden gebruikt, ben je wel verplicht om per administratie ergens deze nummering bij te houden. En dat gaat je niet lukken met auto_increment, je kunt in MySQL maar één auto_increment gebruiken (die je heb je overigens wel nodig voor het id van het record). In PostgreSQL zou je per administratie een eigen sequence kunnen aanmaken, in MySQL zul je ergens in een tabel moeten bijhouden waar je bent gebleven met de nummering.

Dit kan overigens wel in MySQL:

code:

mysql> create table facturen 
(factuur_id int not null auto_increment,
administratie_id int not null, 
primary key (administratie_id,factuur_id));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into facturen (administratie_id) values 
(1),(2),(2),(1),(3),(4),(2),(1);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from facturen;
+------------+------------------+
| factuur_id | administratie_id |
+------------+------------------+
|          1 |                1 | 
|          2 |                1 | 
|          3 |                1 | 
|          1 |                2 | 
|          2 |                2 | 
|          3 |                2 | 
|          1 |                3 | 
|          1 |                4 | 
+------------+------------------+
8 rows in set (0.00 sec)

mysql>

Voila: factuur_id is een auto_increment die per administratie_id een volgnummer bijhoudt.

[RePlayer]

Bedankt voor de nuttige bijdragen!

[djluc]

cariolive23 schreef op zondag 18 januari 2009 @ 17:58:
[...]

Klopt, maar je kunt redelijk gek worden van de vele foutmeldingen. Met een goed systeem, zie genoemde sequence in PostgreSQL, speelt dit hele probleem niet en is de unique-key niet meer dan een laatste redmiddel die je nooit nodig zult hebben.

Die vang je uiteraard gewoon af, je kan deze gewoon verwachten.

Het MySQL voorbeeld is erg verbazend, je geeft niet aan dat een kolom auto-increment is maar toch is die het wel! Strange.

[Kalentum]

djluc schreef op maandag 19 januari 2009 @ 11:55:
[...]
Het MySQL voorbeeld is erg verbazend, je geeft niet aan dat een kolom auto-increment is maar toch is die het wel! Strange.

De auto_increment staat er wel, bij factuur_id.

[djluc]

rutgerw schreef op maandag 19 januari 2009 @ 12:16:
[...]


De auto_increment staat er wel, bij factuur_id.

klopt, mijn fout

[Guillome]

Goede tip zeg, dat wist ik ook niet handig!

[evaarties]

rutgerw schreef op zondag 18 januari 2009 @ 20:25:
[...]


Dit kan overigens wel in MySQL:

code:

mysql> create table facturen 
(factuur_id int not null auto_increment,
administratie_id int not null, 
primary key (administratie_id,factuur_id));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into facturen (administratie_id) values 
(1),(2),(2),(1),(3),(4),(2),(1);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from facturen;
+------------+------------------+
| factuur_id | administratie_id |
+------------+------------------+
|          1 |                1 | 
|          2 |                1 | 
|          3 |                1 | 
|          1 |                2 | 
|          2 |                2 | 
|          3 |                2 | 
|          1 |                3 | 
|          1 |                4 | 
+------------+------------------+
8 rows in set (0.00 sec)

mysql>

Voila: factuur_id is een auto_increment die per administratie_id een volgnummer bijhoudt.

Gaat dit goed doordat MySQL zo slim is om door te hebben dat de primary key uit 2 delen bestaat en daardoor ook de auto increment opsplitst?

[T-MOB]

rutgerw schreef op zondag 18 januari 2009 @ 20:25:
[...]

Dit kan overigens wel in MySQL:
...

Voila: factuur_id is een auto_increment die per administratie_id een volgnummer bijhoudt.

Let op: dat werkt alleen zo in MyISAM-tabellen!

[Kalentum]

evaarties schreef op maandag 19 januari 2009 @ 12:44:
[...]


Gaat dit goed doordat MySQL zo slim is om door te hebben dat de primary key uit 2 delen bestaat en daardoor ook de auto increment opsplitst?

Van opsplitsen is geen sprake: je mag maar 1 auto_increment per tabel hebben. Bij een primary key met meer kolommen moet je bij een insert dus altijd een deel van de primary key expliciet inserten. In mijn voorbeeld was dat dus administratie_id. Omdat het andere veld in de primary key een auto_increment is hoef je die niet op te geven en krijg je toch een unieke key per rij.

[Voutloos]

T-MOB schreef op maandag 19 januari 2009 @ 12:55:
[...]
Let op: dat werkt alleen zo in MyISAM-tabellen!

Voor de volledigheid: Zie http://dev.mysql.com/doc/...ample-auto-increment.html . MyISAM only en nog meer hergebruik van oude waardes bij deletes. Ik zou in ieder geval nog een keer nadenken over andere primairy keys alvorens deze feature te gebruiken. Dit zou mij in ieder geval niet als enige reden overtuigen om MyISAM te kiezen ipv InnoDB.

[Guillome]

werkt niet in mysql als ik het naboots (in phpmyadmin)

[Voutloos]

Welke mysql versie, welke engine, etc. etc. Heb je de gegeven documentatie lnk gelezen?

[Cartman!]

Over die oplopende factuurnummers, ik heb daar een paar jaar geleden eens over gebeld omdat ik niet wist hoe strict dat was maar je mag gewoon <jaar><maand><nr> doen waarbij je nummer elke maand mag resetten. Zolang het nummer maar elke keer hoger wordt en het een logische volgorde heeft (dus niet van 100 naar 450 oid) is dat geen probleem. Hoop niet dat dit ondertussen veranderd is en dergelijke antwoorden wisselen weleens per medewerker bij de Belastingtelefoon

[djluc]

Of je per maand dat nummer ook mag resetten weet ik niet. Je mag sowieso in principe vanalles er voor zetten. Je kan ook <vastgetal><klantnr><nr><vastgetal><maandnr> doen ofzo. Als het maar duidelijke structuur heeft en gedocumenteerd is.

[HawVer]

Cartman! schreef op dinsdag 20 januari 2009 @ 09:01:
Over die oplopende factuurnummers, ik heb daar een paar jaar geleden eens over gebeld omdat ik niet wist hoe strict dat was maar je mag gewoon <jaar><maand><nr> doen waarbij je nummer elke maand mag resetten. Zolang het nummer maar elke keer hoger wordt en het een logische volgorde heeft (dus niet van 100 naar 450 oid) is dat geen probleem. Hoop niet dat dit ondertussen veranderd is en dergelijke antwoorden wisselen weleens per medewerker bij de Belastingtelefoon

Een factuurnummer is maar een referentie nummer. Je bent altijd vrij om daar nog weer een eigen nummering als referentie aan toe te voegen. Een belastigdienst medewerker kan wel a. vertellen, maar op het moment dat een inspecteur op de stoep staat met b. dan trek je aan het kortste eind. Hier nemen we geen risico wat dat betreft.