ON DUPLICATE KEY UPDATE & Auto-increment

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Pagina: 1 2 volgende »

N K

N K

05/09/2014 23:07:58
Quote Anchor link
Waarom wordt er bij ON DUPLICATE KEY UPDATE de auto-increment opgehoogd?
Ik krijg zo gaten in mijn auto-increment (op zich niet heel erg maar wel irritant) en het id loopt wel erg op na veel updates. Ook gebruik ik dit id in een url wat nu niet meer als favoriet kan worden opgeslagen. Niet wenselijk dus. Heeft iemand een idee hoe dit te omzeilen?
 
PHP hulp

PHP hulp

17/10/2021 15:12:28
 
Ward van der Put
Moderator

Ward van der Put

06/09/2014 09:34:30
Quote Anchor link
Kennelijk voert de query dan altijd de INSERT uit en komt de UPDATE niet aan bod. Laat de query eens zien?
 
N K

N K

06/09/2014 10:01:20
Quote Anchor link
Ben iets verder. Er gebeurt iets anders. Er vallen gewoon gaten in mijn AI als er een insert plaatsvind. Net nog een run gedraaid en de laatst ingevoegde records bevatten onderstaande AI's.
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
23859
24046
24066
24296
24311
24411
24538
24819
24864
24954
24999
25003
25007
25011
25070
25091
25590
25615
25683
25695
25709
25719
25885
26174
26175
26176
26177
26178
26179


Mijn query is:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<?php
$sql_insert_event
= '
                        INSERT INTO
                                    event
                                        (
                                            fb_event_id,
                                            fb_venue_id,
                                            fb_image_id,
                                            user_id,
                                            title,
                                            description,
                                            ticket_url,
                                            startdate,
                                            starttime,
                                            create_date,
                                            change_date,
                                            published,
                                            event_status  
                                        )
                                    VALUES
                                            (
                                            "'
.mysqli_real_escape_string($con, $eid).'",
                                            "'
.mysqli_real_escape_string($con, $fb_venue_id).'",
                                            "'
.mysqli_real_escape_string($con, $fb_image_id).'",
                                            "'
.mysqli_real_escape_string($con, 1).'",
                                            "'
.mysqli_real_escape_string($con, $event["name"]).'",
                                            "'
.mysqli_real_escape_string($con, $event["description"]).'",
                                            "'
.mysqli_real_escape_string($con, $event["ticket_uri"]).'",
                                            "'
.mysqli_real_escape_string($con, $start_date).'",
                                            "'
.mysqli_real_escape_string($con, $start_time).'",
                                            "'
.mysqli_real_escape_string($con, date('Y-m-d H:i:s')).'",
                                            "'
.mysqli_real_escape_string($con, date('Y-m-d H:i:s')).'",
                                            "'
.mysqli_real_escape_string($con, 1).'",
                                            "'
.mysqli_real_escape_string($con, 'n').'")
                        ON DUPLICATE KEY UPDATE
                                    fb_event_id = "'
.mysqli_real_escape_string($con, $eid).'",
                                    fb_venue_id = "'
.mysqli_real_escape_string($con, $fb_venue_id).'",
                                    fb_image_id = "'
.mysqli_real_escape_string($con, $fb_image_id).'",
                                    user_id = "'
.mysqli_real_escape_string($con, 1).'",
                                    title = "'
.mysqli_real_escape_string($con, $event["name"]).'",
                                    description = "'
.mysqli_real_escape_string($con, $event["description"]).'",
                                    ticket_url = "'
.mysqli_real_escape_string($con, $event["ticket_uri"]).'",
                                    startdate = "'
.mysqli_real_escape_string($con, $start_date).'",
                                    starttime = "'
.mysqli_real_escape_string($con, $start_time).'",
                                    change_date = "'
.mysqli_real_escape_string($con, date('Y-m-d H:i:s')).'",
                                    published = "'
.mysqli_real_escape_string($con, 1).'",
                                    event_status = "'
.mysqli_real_escape_string($con, 'u').'"
                                    '
;
?>
 
Ward van der Put
Moderator

Ward van der Put

06/09/2014 10:06:58
Quote Anchor link
De auto-increment wordt alleen automatisch ingesteld als je géén waarde doorgeeft. Je query bevat overal id's mét een concrete waarde: als eentje daarvan de auto-increment index is, wordt de index dus niet automatisch met 1 verhoogd, maar op een concrete waarde gezet.
 
N K

N K

06/09/2014 10:08:51
Quote Anchor link
Hoi Ward. Ik heb geen autoincrement waarde in mijn query staan of bedoel je dat niet?
 
Ward van der Put
Moderator

Ward van der Put

06/09/2014 10:14:21
Quote Anchor link
Ja, dat wilde ik even controleren, maar dan heb je het goed gedaan.

Vreemd dat er grote gaten vallen. Zit niet een ander proces toevallig records te verwijderen? Het valt me in je reeks namelijk op dat de laatste getallen wél aangesloten zijn:

26174
26175
26176
26177
26178
26179

Ik mis trouwens een WHERE in de UPDATE. Wat wil je updaten?
Gewijzigd op 06/09/2014 10:19:48 door Ward van der Put
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

06/09/2014 10:19:22
Quote Anchor link
Dit is de manier waarop MySQL werkt, als een rij wordt geupdated ipv geinsert gooit ie toch de AI waarde omhoog.
Hetzelfde geldt voor bulk inserts (zonder on duplicate key)
 
N K

N K

06/09/2014 10:22:27
Quote Anchor link
Tja, ik heb geen delete query in mijn script zitten.
Er moet uiteraard wel iets zijn maar kan het nog niet vinden. Ik wil mijn hele script wel posten eventueel?


Toevoeging op 06/09/2014 10:22:55:

@Ger : Meen je dat nou? Dat klinkt toch niet logisch?

Toevoeging op 06/09/2014 10:25:03:

...Maar de tussenliggende AI's van mijn reeks zijn niet bekend in de database. Wat zou daarmee gebeurt kunnen zijn?
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

06/09/2014 11:12:01
Quote Anchor link
>> Dat klinkt toch niet logisch ....

Toch wel:
Quote:
For INSERT statements where the number of rows to be inserted is known at the beginning of processing the statement, InnoDB quickly allocates the required number of auto-increment values without taking any lock, but only if there is no concurrent session already holding the table-level AUTO-INC lock (because that other statement will be allocating auto-increment values one-by-one as it proceeds)

Omdat niet bekend is of er een rij geinsert of geupdate moet worden, wordt toch de AI waarde met één opgehoogd, zodat een 'gelijktijdige' insert geen duplicates kan opleveren.

>> Maar de tussenliggende AI's van mijn reeks zijn niet bekend in de database. Wat zou daarmee gebeurt kunnen zijn

De AI waarde van een tabel is iets wat op server niveau wordt bijgehouden, dus er wordt niet naar een tabel gekeken wat de max waarde is.
 
N K

N K

06/09/2014 11:17:51
Quote Anchor link
@Ger,

Oke dus, bij het invoegen van een record wat al bestaat en waardoor de INSERT dus faalt wordt door MYSQL de Auto-increment WEL opgehoogd.Bij een volgende insert kunnen er dus AI's missen.

Bij een update vind ik het toch niet netjes dat dit wordt gedaan. Is de workaround hiervoor om dan toch weer met meerdere queries te werken?
 
Willem vp

Willem vp

06/09/2014 12:20:01
Quote Anchor link
De uitspraak van Ger dat bij een update je autoincrement-id wordt verhoogd, klinkt logisch, maar klopt niet. ;-) Ik kan het in ieder geval niet reproduceren.

Het lijkt mij logisch (maar ik kan zo snel niet vinden of dat ook daadwerkelijk zo is) dat MySQL eerst de indices checkt of de waardes die je wilt invoeren conflicteren met een key. Ik zou het onlogisch vinden als hij rücksichtlos een record gaat toevoegen en pas achteraf constateert dat het helemaal niet kan.

Ik heb nog wel een tip voor je query: in het 'on duplicate'-gedeelte zit je nu voor heel veel velden dezelfde waardes in te voeren als in het insert-gedeelte van de query. Dat kun je veel efficiënter oplossen met de values-functie:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
       ON DUPLICATE KEY UPDATE
                                    fb_event_id = values(fb_event_id),
                                    fb_venue_id = values(fb_venue_id),
                                    fb_image_id = values(fb_image_id),
[...]
                                    change_date = NOW(),
                                    published = 1,
                                    event_status = 'u'"


Verder valt het me op dat je (te) vaak de real_escape_string-functie gebruikt. In principe is dat alleen maar nodig als er tekens in je string staan waar de query zich in kan verslikken (ruwweg: newlines, backslashes en quotes) of als je niet zeker weet of die tekens erin staan (bijvoorbeeld bij user-input). Een met date() gegenereerde datum of een integer hoef je dus niet te escapen.

Sowieso is het efficiënter om de MySQL-functie NOW() te gebruiken als je het huidige tijdstip wilt invoeren in de query.

Wat is in je tabel het datatype van velden als fb_event_id en published? Het lijkt me aannemelijk dat dit INT is. Je behandelt de velden in je query echter als strings. Ook dat is niet aan te bevelen.
 
Henk de Vriep

Henk de Vriep

06/09/2014 12:20:04
Quote Anchor link
Je zou evt eerst kunnen kijken met een query of deze entry al bestaat en dan updaten. Zoniet inserten. Het is zwaarder maar het lost in ieder geval het probleem op.
 
Willem vp

Willem vp

06/09/2014 12:22:19
Quote Anchor link
> Het is zwaarder maar het lost in ieder geval het probleem op.

Dat het zwaarder is, ben ik meteen met je eens. Ik vraag me echter af of het het probleem oplost. Ik vermoed namelijk dat het niet in deze query zit, maar heel ergens anders.
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

06/09/2014 12:31:01
Quote Anchor link
>> De uitspraak van Ger dat bij een update je autoincrement-id wordt verhoogd, klinkt logisch, maar klopt niet. ;-)

Het zit hem in ON DUPLICATE KEY update, MySQL weet van te voren niet of ie kan inserten of moet updaten.

Dit geldt overigens alleen voor InnoDb tabellen, en het is instelbaar:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
SET  innodb_autoinc_lock_mode = 0
 
N K

N K

06/09/2014 12:31:51
Quote Anchor link
@willem. Ja die query kan beter. Ben nog beginner. Dank voor de info.
 
Willem vp

Willem vp

06/09/2014 12:38:23
Quote Anchor link
> Dit geldt overigens alleen voor InnoDb tabellen

Dat is geen onbelangrijke toevoeging... ;-)
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

06/09/2014 17:42:28
Quote Anchor link
>> Dat is geen onbelangrijke toevoeging... ;-)
Klopt, speciaal voor jou ...... ;-)

Overigens:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
ON DUPLICATE KEY UPDATE
    change_date = NOW()

Dan kan je nooit weten over er daadwerkelijk iets veranderd is.
In dit geval kan je aan de change_date beter een TIMESTAMP datatype meegeven, of met MySQL 5.6.5+ een DATETIME met DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
En dan de kolom uit de query weglaten.
Gewijzigd op 06/09/2014 18:05:09 door Ger van Steenderen
 
N K

N K

06/09/2014 19:55:23
Quote Anchor link
Ehm kunnen we het even samenvatten?
Als ik het goed begrijp:
In een InnoDb tabel geldt, wanneer er gebruik wordt gemaakt van ON DUPLICATE KEY UPDATE, dat:
1) Gefaalde inserts toch ervoor zorgen dat de AI wordt verhoogd.
2) Updates ook ervoor zorgen dat de AI wordt verhoogd (..dit heb ik nog niet kunnen testen)

Tenzij je SET innodb_autoinc_lock_mode = 0 uitvoert? Dan blijft de AI opeenvolgend?
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

06/09/2014 20:05:37
Quote Anchor link
>> Tenzij je SET innodb_autoinc_lock_mode = 0 uitvoert? Dan blijft de AI opeenvolgend?

Garantie tot aan de deur ......
 
N K

N K

07/09/2014 14:49:07
Quote Anchor link
Ger, ik heb het gevoel dat ik nu iets doe wat eigenlijk niet de bedoeling is en vraag me af of dat ik niet gewoon een verkeerde werkwijze heb.

Ik gebruik voor mijn site overal de AI om een record uniek te identificeren. Deze moet niet onderhevig zijn aan veranderingen aangezien ik deze ook gebruik in o.a. urls. Is het misschien beter om bij een insert de AI te hashen en op te slaan in een aparte kolom en dat aan te houden om een record uniek te maken of zeg ik nu iets heel stoms?

Dit zou m.i. bovenstaand probleem niet meer actueel maken.
 
Willem vp

Willem vp

07/09/2014 14:52:56
Quote Anchor link
> Ik gebruik voor mijn site overal de AI om een record uniek te identificeren.
> Deze moet niet onderhevig zijn aan veranderingen aangezien ik deze ook gebruik in o.a. urls.

Eenmaal uitgegeven wordt het id niet meer veranderd, tenzij je dat zelf doet. Ik zie het probleem dus niet, eigenlijk...
 

Pagina: 1 2 volgende »



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.