Multi Query Insert
Ik probeer voor het eerst multi query maar ik krijg een Success terug wanneer de bovenste Insert wel werkt en de onderste niet.
Code (php)
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
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
<?php
if ($HasError == 0) {
// Insert in database
$SQLInsertUser = "INSERT INTO users
(email, password, reg_date, ipaddress, verified)
VALUES
('" . $FormEmail . "', '" . $FormPassword . "', '" . $FormDate . "', '" . $FormIpaddress . "', 'no');";
$SQLInsertUser .= "INSERT INTO verify
(email, hash2)
VALUES
('" . $FormEmail . "', '" . $RandomHash . "')";
if ($conn->multi_query($SQLInsertUser) === FALSE) {
// Nice error if e-mail is duplicate
if ($conn->errno == 1062) {
echo $lang['formhandling-register-email-duplicate'];
echo $lang['form-try-again'];
exit;
} else {
echo "Error: " . $conn->errno;
}
} else {
echo "Success";
}
$conn->close();
exit;
}
?>
if ($HasError == 0) {
// Insert in database
$SQLInsertUser = "INSERT INTO users
(email, password, reg_date, ipaddress, verified)
VALUES
('" . $FormEmail . "', '" . $FormPassword . "', '" . $FormDate . "', '" . $FormIpaddress . "', 'no');";
$SQLInsertUser .= "INSERT INTO verify
(email, hash2)
VALUES
('" . $FormEmail . "', '" . $RandomHash . "')";
if ($conn->multi_query($SQLInsertUser) === FALSE) {
// Nice error if e-mail is duplicate
if ($conn->errno == 1062) {
echo $lang['formhandling-register-email-duplicate'];
echo $lang['form-try-again'];
exit;
} else {
echo "Error: " . $conn->errno;
}
} else {
echo "Success";
}
$conn->close();
exit;
}
?>
Multiqueries zijn hier simpelweg niet voor geschikt, vooral als de kans bestaat dat ergens onderweg een fout optreedt: alle queries tot dat punt zijn dan namelijk al verwerkt wat mogelijk voor rotzooi in je database zorgt.
http://www.w3schools.com/php/php_mysql_insert_multiple.asp
Ik weet van transacties af alleen is het me nog niet gelukt. Ik ga wel een andere manier bedenken, bedankt.
- Ariën - op 11/02/2017 15:58:44:
Opsich zou dit moeten werken:
http://www.phptuts.nl/view/26/6/
http://www.phptuts.nl/view/26/6/
Bedankt, dat ziet er niet erg moeilijk uit. :)
Ik denk dat als je wilt afdwingen dat een reeks queries als batches uitgevoerd worden (in het geheel wel, of in het geheel niet) dat transacties eigenlijk de enige (en enige juiste) gedoodverfde manier is.
Een aantal argumenten om geen multi_query() te gebruiken:
- detectie of iets fout gaat is soms lastig en nogal omslachtig
- als er iets fout is gegaan zit er waarschijnlijk onvolledige data in je database...
- ... die je weer op een of andere manier moet repareren, ongedaan moet maken
- prepared statements werken niet i.c.m. multi_query() (maar als je die per se wilt gebruiken is PDO wellicht een beter alternatief)
- het consequent escapen van DATA binnen de SQL is bij multi_query() extra belangrijk, je wilt namelijk niet dat iemand hier een DROP DATABASE hennie; aan vastfietst
Ik zat zelf te denken aan een rits aan bijvoorbeeld INSERT-queries waarvan je zeker bent dat die werken, om bijvoorbeeld een tabel met data te vullen. Maar dan kan je net zo goed een foreach en een $mysqli->query() gebruiken.
- Ariën - op 11/02/2017 20:43:47:
Ik zat zelf te denken aan een rits aan bijvoorbeeld INSERT-queries waarvan je zeker bent dat die werken, om bijvoorbeeld een tabel met data te vullen.
Mja, maar soms wil je voor een correcte werking tabellen/kolommen kunnen locken zodat je race conditions kunt voorkomen. Transacties zijn daar bij uitstek geschikt voor.
Soms is het simpelweg niet voldoende om "enkele foutloze queries achter elkaar uit te voeren" omdat als dat proces meerdere keren tegelijkertijd wordt uitgevoerd, de queries van deze verschillende aanroepen mogelijk met elkaar verweven zijn wat soms (en zonder transacties / locking) mogelijk ongewenste situaties oplevert.
Een klassiek voorbeeld daarvan is een situatie waarin je geld overmaakt van rekening A naar rekening B. Je wilt dat de hele mutatie één ondeelbare actie vormt anders bestaat de kans dat er vreemde / ongewenste dingen gebeuren met saldo's.
Eigenlijk in alle systemen waarin meerdere gebruikers mogelijk dezelfde data bewerken is het inzetten van transacties handig / verstandig.
Gewijzigd op 11/02/2017 21:43:48 door Thomas van den Heuvel
- Ariën - op 11/02/2017 20:43:47:
De vraag is alleen, waar is multi_query() nou wel goed voor?
Ik zat zelf te denken aan een rits aan bijvoorbeeld INSERT-queries waarvan je zeker bent dat die werken, om bijvoorbeeld een tabel met data te vullen. Maar dan kan je net zo goed een foreach en een $mysqli->query() gebruiken.
Ik zat zelf te denken aan een rits aan bijvoorbeeld INSERT-queries waarvan je zeker bent dat die werken, om bijvoorbeeld een tabel met data te vullen. Maar dan kan je net zo goed een foreach en een $mysqli->query() gebruiken.
En in dat geval zou ik zelfs nóg liever 1 insert-query gebruiken met een lijst values, omdat dat qua performance beter is dan allemaal losse inserts.
Voor de toeschouwer lijkt het wel zo, inderdaad. ;-) Waarschijnlijk zal de server evengoed losse schrijfacties gebruiken, maar de grootste winst is dat hij niet steeds het query execution plan hoeft te maken. Met prepared statements hoeft de server dat ook niet, maar daar moet je voor elk record een execute() uitvoeren, wat ook weer overhead geeft.
Maar terug naar het oorspronkelijke onderwerp, bovenstaande aanpak gaat niet werken omdat je met verschillende tabellen te maken hebt.
Wacht eens, als ik nu alles in één tabel sto... :p
Overigens, zonder het achterliggend datamodel te kennen: als ik kijk naar de queries in de original post, vind ik het goed te verdedigen om alles in 1 tabel te stoppen.
Gewijzigd op 14/02/2017 08:44:58 door Willem vp
Willem vp op 14/02/2017 08:43:43:
Overigens, zonder het achterliggend datamodel te kennen: als ik kijk naar de queries in de original post, vind ik het goed te verdedigen om alles in 1 tabel te stoppen.
Zo te zien hoort alles inderdaad in één tabel:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
<?php
// Insert in database
$SQLInsertUser = "INSERT INTO users
(email, password, reg_date, ipaddress, verified)
VALUES
('" . $FormEmail . "', '" . $FormPassword . "', '" . $FormDate . "', '" . $FormIpaddress . "', 'no');";
$SQLInsertUser .= "INSERT INTO verify
(email, hash2)
VALUES
('" . $FormEmail . "', '" . $RandomHash . "')";
?>
// Insert in database
$SQLInsertUser = "INSERT INTO users
(email, password, reg_date, ipaddress, verified)
VALUES
('" . $FormEmail . "', '" . $FormPassword . "', '" . $FormDate . "', '" . $FormIpaddress . "', 'no');";
$SQLInsertUser .= "INSERT INTO verify
(email, hash2)
VALUES
('" . $FormEmail . "', '" . $RandomHash . "')";
?>
Ik mis hier verschillende dingen:
• De tweede tabel 'verify' bevat alleen een "hash" (die eigenlijk geen hash is maar een random token) bij het e-mailadres dat ook voorkomt in de eerste tabel.
• Er wordt niet opgeslagen wanneer die "hash" voor de verificatie wordt gegenereerd, dus er kunnen kennelijk tot in alle eeuwigheid ongeverifieerde e-mailadressen circuleren.
• Er is geen primaire sleutel die de twee tabellen koppelt (tenzij dat het e-mailadres is, maar dat is om diverse redenen niet handig).
• In de eerste tabel staat de kolom 'verified' op de string 'no', maar daarvan kun je beter een flag maken van 1 bit, bijvoorbeeld een TINYINT(1), of een TIMESTAMP als je het verschil tussen de registratiedatum en de verificatiedatum wilt kunnen gebruiken. Als je security by design toepast, zou de veiligste toestand 'verified' == 'no' bovendien de standaardtoestand moeten zijn — en kun je die weglaten in de query.
• Als je toch in één keer twee queries wilt uitvoeren voor een INSERT van één gebruiker, kun je hier veel beter een transactie gebruiken dan een multiquery.
In beide tabbelen staat email op UNIQUE dus ik krijg geen eeuwigheid aan ongeverifieerde emailadressen. Tevens heeft de tabel verify een TIMESTAMP kolom die update on entry. Ik kan dus altijd zien wanneer dit is gebeurd en kan dit eventueel na twee weken weer verwijderen. Ik geef ook in de email aan dat je binnen twee weken je email moet verifieren.
Beide tabbelen hebben ook een primary key "id".
Wat betreft verified = 'no' dat kan ik veranderen naar de tinyint. Bedankt voor de tip.
Ik ga de transacties tevens toevoegen waar Ariën een voorbeeld van stuurde.
Danny von Gaal op 14/02/2017 09:46:25:
Ik wil liever niet alles in een tabel omdat ik de user tabel klein wil houden met alleen belangrijke gegevens. Wanneer iemand zijn emailadres heeft geverifieerd wordt de entry van de verify tabel ook weer netjes verwijderd.
Je kunt die kolom gerust naar de hoofdtabel users verplaatsen. Als je de kolom nullable maakt met DEFAULT NULL, ga je pas bij honderdduizenden gebruikers verschil merken. In ruil daarvoor krijg je in een veel eerder stadium al een performance-winst, omdat je niet meer twee queries hoeft uit te voeren.
Danny von Gaal op 14/02/2017 09:46:25:
In beide tabbelen staat email op UNIQUE dus ik krijg geen eeuwigheid aan ongeverifieerde emailadressen.
Daar moet je voorzichtig mee zijn, merkte ik afgelopen week weer toen een teamlid me met mijn Gmail-adres aan Slack had toegevoegd. Voor Google maakt het niet uit of je een e-mailadres schrijft als [email protected] met hoofdletters, [email protected] met kleine letters of [email protected] en [email protected] zonder punt. Voor Google is dat één e-mailadres, maar maak je de kolom UNIQUE, dan zijn het verschillende e-mailadressen. Bij Slack hadden ze daarmee ook geen rekening gehouden, dus ik kon niet inloggen mét een geldig e-mailadres.
Gewijzigd op 14/02/2017 11:59:30 door Ward van der Put
En los van dat de kolom op Unique staat kan ik eerst alle letters lowercase maken voor ik het insert anders. Met en zonder punt zijn voor mij wel andere emailadressen.
Gewijzigd op 14/02/2017 12:36:41 door Danny von Gaal
(Wéér een reden om OOP te programmeren, hoef je enkel maar je setter aan te passen)
Gewijzigd op 14/02/2017 13:24:41 door Frank Nietbelangrijk
Frank Nietbelangrijk op 14/02/2017 13:23:29:
@ward: Zo ver ik weet zijn ALLE emailadressen case INsensitive.
Het gedeelte vóór de @ mag volgens de RFC case-sensitive zijn. In de praktijk ben ik nog geen server tegengekomen die dat onderscheid maakt.
Edit:
Je moet overigens niet zoeken op de term 'case sensitive':
Quote:
the local-part MUST be interpreted and assigned semantics only by the host specified in the domain part of the address.
Wat hier eigenlijk staat, is dat software die email-adressen verwerkt niet kan weten hoe de ontvangende mailserver met het local-part van een adres omgaat (het gedeelte voor de @ dus). Zoals Ward al beschreef kan het zijn dat punten in het local-part niet relevant zijn. Maar ook hoofdlettergevoeligheid valt daaronder.
Edit:
Nog een reden waarom het niet heel veel nut heeft om een unique constraint op email-adressen te zetten is RFC 5233. Ruwweg samengevat wordt daarin beschreven dat gebruiker+blabla@ een alias (sub-adres) is van gebruiker@.
Gewijzigd op 14/02/2017 15:06:04 door Willem vp
Het zijn naar mijn idee beiden geen zaken om je heel erg druk over te maken maar je zou in theorie kunnen meemaken dat een bedrijf de volgende mensen en mailadressen in gebruik heeft:
Maar als dat zo is zullen ze daar snel vanaf willen denk ik want dan gaat het wel heel vaak mis of niet?