ON DUPLICATE KEY UPDATE & Auto-increment

Kennelijk voert de query dan altijd de INSERT uit en komt de UPDATE niet aan bod. Laat de query eens zien?

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.

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:

<?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').'"
                                    ';
?>

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.

Hoi Ward. Ik heb geen autoincrement waarde in mijn query staan of bedoel je dat niet?

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?

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)

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?


[size=xsmall]Toevoeging op 06/09/2014 10:22:55:[/size]

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

[size=xsmall]Toevoeging op 06/09/2014 10:25:03:[/size]

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

>> Dat klinkt toch niet logisch ....

Toch wel:

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.

@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?

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:

       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.