[PHP & MYSQL] Replace into / update on duplicate

[xAndyx]

Ik ben bezig met een aanleversysteem waarbij klanten hun spullen via de browser kunnen aanleveren. Nu staan alle klant gegevens + orders in een Filemaker db. En deze willen we eigenlijk niet rechtstreeks op internet hebben en ik schrijf er ook liever niet in. Er is gekozen voor een dump naar xml welke ik ga importeren naar mysql.

Nu wil ik de bestaande updaten en de niet bestaande inserten.

Op dit moment werkt het nog zo:

PHP:

//checken of opdrachtgever al in de db staat 
$query = mysql_query("SELECT klant_nr FROM tblOpdrachtgevers WHERE klant_nr = '$klant_nr[0]'") or die ("fout in de query"); 
$klant_nr_DB = mysql_fetch_array($query); 

if($klant_nr[0] == $klant_nr_DB[0]) 
    { 
        //zo ja, update 
        mysql_query("UPDATE tblOpdrachtgevers 
                 SET bedrijfsnaam = '$bedrijfsnaam[0]' 
                 WHERE klant_nr = '$klant_nr[0]'") or die (mysql_error());             
    } 
    else 
    { 
        //zo nee, insert 
        mysql_query("INSERT INTO tblOpdrachtgevers (klant_nr, bedrijfsnaam) 
                 VALUES('$klant_nr[0]', '$bedrijfsnaam[0]')") or die (mysql_error()); 
    }

Dit resulteerd in heel veel select opdrachten en dan nog een keer update of insert.

Moet handiger kunnen denk ik, dus ik ging opzoek naar een andere oplossing:

PHP:

mysql_query("INSERT INTO tblOpdrachtgevers (klant_nr, bedrijfsnaam) 
         VALUES('$klant_nr[0]', '$bedrijfsnaam[0]') 
         ON DUPLICATE KEY UPDATE bedrijfsnaam = '$bedrijfsnaam[0]'");

Ziet er in princiepe goed uit maar het nadeel is dat ik in mijn geval altijd meer updates heb dan inserts.

Zou het dus liever andersom willen hebben. Ik vond deze oplossing:

PHP:

mysql_query ("REPLACE INTO tblOpdrachtgevers (klant_nr, bedrijfsnaam) 
              VALUES('$klant_nr[0]', '$bedrijfsnaam[0]')");

Nadeel van deze is (volgens mysql.com) dat de actie replace into meer tijd kost dan een update. En dat replace echt dingen delete in je tabel ipv updaten.

Dus ik vraag mij af wat het slimste is om te doen?

[Voutloos]

Het slimste om te doen is je datamodel fixen. Als je nou overal een bedrijfId hebt, hoef je als een bedrijf van naam wijzigt maar 1 rij aan te passen, namelijk de rij in de bedrijven tabel. In je huidige tabel is de bedrijfsnaam redundant.

[xAndyx]

Voutloos schreef op dinsdag 22 januari 2008 @ 12:48:
Het slimste om te doen is je datamodel fixen. Als je nou overal een bedrijfId hebt, hoef je als een bedrijf van naam wijzigt maar 1 rij aan te passen, namelijk de rij in de bedrijven tabel. In je huidige tabel is de bedrijfsnaam redundant.

Klant_nr is de id/key in de database. Alleen updaten gaat helaas niet om dat er in de xml dump ook nieuwe klanten tussen de update gevallen staan.

[Voutloos]

Het punt was dat als je meer dan 1 insert danwel update nodig had, je datamodel redundant is.

[FragFrog]

xAndyx schreef op dinsdag 22 januari 2008 @ 13:10:
Alleen updaten gaat helaas niet om dat er in de xml dump ook nieuwe klanten tussen de update gevallen staan.

Dan heb je 2 3 opties:

1. Eerst select, als iets al bestaat een update / niets uitvoeren, anders een insert.
2. Altijd enkel een replace doen.
3. Je laad alle klanten-id's met hun bedrijfsnaam in een keer in een array, vergelijkt die met je nieuwe data en voert alleen de wijzigingen door.

Optie 1 is meestal trager dan optie 2 (alleen niet als er maar heel weinig nieuwe bij komen), dus performancewise is er geen reden om van replace into af te blijven. Let er alleen wel op dat inderdaad de 'oude' rij verwijdert en opnieuw aangemaakt wordt, dit zorgt er ook voor dat auto-incremental ID's aangepast worden wat niet altijd handig is.

Bedenk me net dat je ook gewoon eerst een cache kan maken van de bestaande data wat je heel veel queries kan schelen, is iets meer werk maar waarschijnlijk het snelst van allemaal

[ Voor 19% gewijzigd door FragFrog op 22-01-2008 19:20 ]

[BHR]

Over hoeveel records praten we eigenlijk??

Het is gewoon een if(a) then b else c verhaal, of je het nu zelf in je eigen php code doet, of laat doen door de database. Ik zou trouwens gewoon voor deze gaan..

xAndyx schreef op dinsdag 22 januari 2008 @ 12:22:

PHP:

$result = mysql_query("INSERT INTO tblOpdrachtgevers (klant_nr, bedrijfsnaam) 
         VALUES('$klant_nr[0]', '$bedrijfsnaam[0]') 
         ON DUPLICATE KEY UPDATE bedrijfsnaam = '$bedrijfsnaam[0]'"); 
if($result === false)
{
//foute query!
}

Probeer het vervolgens uit op je dataset en meet de tijd die het duurt. Die "extra" tijd waar de mysql man over praat lijkt mij kleiner dan een roundtrip terug naar jouw applicatie. Indien je daar toch niet tevreden over bent, ga dan pas aan de gang met caching en "enorm" veel qeuries.


my 2 cents..

[Janoz]

Voer gewoon de update uit en vraag de affected rows op. Als deze 0 is dan was er geen record waarvoor gold klant_nr = '$klant_nr[0]'. Dan voer je alsnog een insert uit.

[BHR]

Volgens mij is affected rows ook 0 wanneer de oude waarde gelijk is aan de nieuwe waarde. In dat geval gaat je insert fout.

[Janoz]

Ik las het inderdaad op php.net. Ik ben er echter nog neit helemaal achter of dit weer zo'n MySQL quirk is, of dat het daadwerkelijk zo hoort volgens de SQL standaard. Tot nu toe neig ik naar het eerste, maar ik kan zo snel niet iets vinden.

[sig69]

BHR schreef op woensdag 23 januari 2008 @ 15:09:
Over hoeveel records praten we eigenlijk??

Het is gewoon een if(a) then b else c verhaal, of je het nu zelf in je eigen php code doet, of laat doen door de database. Ik zou trouwens gewoon voor deze gaan..

[...]

Probeer het vervolgens uit op je dataset en meet de tijd die het duurt. Die "extra" tijd waar de mysql man over praat lijkt mij kleiner dan een roundtrip terug naar jouw applicatie. Indien je daar toch niet tevreden over bent, ga dan pas aan de gang met caching en "enorm" veel qeuries.

my 2 cents..

Wilde net een heel soortgelijk verhaal typen, maar "met ^^" volstaat.

[Voutloos]

Janoz schreef op woensdag 23 januari 2008 @ 15:47:
Ik las het inderdaad op php.net. Ik ben er echter nog neit helemaal achter of dit weer zo'n MySQL quirk is, of dat het daadwerkelijk zo hoort volgens de SQL standaard. Tot nu toe neig ik naar het eerste, maar ik kan zo snel niet iets vinden.

Dit is juist waarom het verschil tussen affected_rows en found_rows bestaat. En nee, je kan niet found_rows doen.