"Insert Into ON Duplicate Key Update" auch ohne Key möglich?

[hausl78]

Hallo!

Ich hätte eine Frage zu der MySQL Funktion "Insert Into ON Duplicate Key Update"

Ich habe folgende Struktur unten und möchte gerne wissen, ob der Befehl von oben da auch funktionieren kann, weil in der 2. Tabelle unten wo geupdatet/inserted werden soll die ID bei Tipps die noch nicht vorhanden sind nicht existiert..

Derzeit mach ich es so (PHP-Skript)
Tipp existiert - identifikation über die TippID, zwecks UPDATE (SQL)
Tipp existiert noch nicht.. dann identifikation mittels Nickname und SpielID für INSERT (SQL)
=> Aber geht das dann auch bei "Insert Into ON Duplicate Key Update" ?? Ich befürchte nicht

SpielID Runde Spiel Beginn ErgebnisTor1 ErgebnisTor2 TippID Nickname SpielIDRef TippTor1 TippTor2
4005 Gruppe A Argentinien - Mexiko 2010-09-20 16:00 \N \N \N \N \N \N \N
4003 Gruppe C Brasilien - Chile 2010-09-20 16:00 \N \N 6003 Franzi 4003 2 2
4009 Gruppe A Chile - Spanien 2010-09-22 16:00 \N \N \N \N \N \N \N
4006 Gruppe B Deutschland - England 2010-09-20 20:00 \N \N \N \N \N \N \N
4004 Gruppe D Niederlande - Slowakei 2010-09-20 20:00 \N \N 6004 Franzi 4004 3 3
4011 Gruppe C Nordkorea - Elfenbeinküste 2010-09-23 16:00 \N \N \N \N \N \N \N
4012 Gruppe D Portugal - Brasilien 2010-09-23 20:00 \N \N \N \N \N \N \N
4010 Gruppe B Schweiz - Honduras 2010-09-22 20:00 \N \N \N \N \N \N \N
4008 Gruppe D Uruguay - Südkorea 2010-09-21 20:00 \N \N \N \N \N \N \N
4007 Gruppe C USA - Ghana 2010-09-21 16:00 \N \N \N \N \N \N \N

Ich habe aber die folgenden Tipps schon erfaßt

TippID Nickname SpielIDRef TippTor1 TippTor2
6001 Franzi 4001 3 2
6002 Franzi 4002 1 0
6003 Franzi 4003 2 2
6004 Franzi 4004 3 3
6005 Karli 4001 1 0
6006 Karli 4002 2 0
6007 Karli 4003 2 1
6008 Karli 4004 1 2

Der Vollständigkeit halber... So hole ich mir die Daten raus -> diese Spiele kann der User dann tippen:

SELECT * FROM tabSpiele
LEFT OUTER JOIN tabTipps
ON tabSpiele.SpielID = tabTipps.SpielID
   AND tabTipps.Nickname = 'Franzi'
WHERE Beginn > '2010-08-01 22:40'ORDER BY Beginn, Runde 

Danke!
Juergen

[Falk Prüfer]

Hallo,

"Insert Into ON Duplicate Key Update" auch ohne Key möglich?

Definitiv: Nein!
Damit ON Duplicate Key Update funktioniert, muß - wie es der Name vermuten läßt - mindestens ein UNIQUE KEY definiert sein.

Gruß Falk

[hausl78]

Also hier dann am besten der klassische "zu Fuß" weg...

UPDATE versuchen
wenn keine upgedatet wurden
INSERT

Oder besseren Vorschlag?

Danke!

[Falk Prüfer]

Naja, dein "klassischer" Weg ist ein Trugschluß. Er verhindert Dopplungen nicht mit Sicherheit! Was ist wenn zwischen Updateversuch und Insert eine zweite Session genau die gleichen Daten schreibt? Auch da gibts kein Update und es wird ein zweites Insert durchgeführt!

Die einzig sichere Möglichkeit eine Dopplung zu verhindern ist das Anlegen eines UNIQUE KEY auf die betreffenden Felder. Und wenn der existiert, dann läßt sich auch INSERT ... ON DUPLICATE KEY UPDATE verwenden.

Gruß Falk

[hausl78]

Das sind aber schlechte Nachrichten...

Ein UNIQUE KEY geht nicht, weil ich den ja nur bei bereits bestehenden Tipps eines User zu einem Spiel eine Tipp-ID habe.

Wenn ein User ein Spiel noch nicht getippt hat, dann gibt es noch keinen Tipp, somit auch keinen Eintrag in der Tipp-Tabelle bzw. Key der UNIQUE sein könnte. Da hab ich nur die ID des Spieles und den User der das Spiel tippt und daraus erstelle ich einen Tipp.

Oder kann man der DB sagen das die Kombination(!) aus User und SpielID auch eindeutig ist?

Juergen

[ebis]

Oder kann man der DB sagen das die Kombination(!) aus User und SpielID auch eindeutig ist?

Warum sollte man denn das nicht sagen können???

[hausl78]

Und wie

Es kann der User öfters vorkommen, es kann die SpielID öfters vorkommen, aber es kann die jeweilige Kombination User und Spiel jeweils nur einmal vorkommen. Also kurzum 1 Tipp je User pro Spiel.

Naja, dein "klassischer" Weg ist ein Trugschluß. Er verhindert Dopplungen nicht mit Sicherheit! Was ist wenn zwischen Updateversuch und Insert eine zweite Session genau die gleichen Daten schreibt? Auch da gibts kein Update und es wird ein zweites Insert durchgeführt!

Wobei ich nicht glaube, das ein User 2 Browser offen hat und sich selbst 2x zeitlgleich tippt :-)

Juergen

[Falk Prüfer]

Und wie

Es kann der User öfters vorkommen, es kann die SpielID öfters vorkommen, aber es kann die jeweilige Kombination User und Spiel jeweils nur einmal vorkommen. Also kurzum 1 Tipp je User pro Spiel.

[highlight=sql]
alter table <tabelle> ADD
CONSTRAINT UNIQUE KEY <eindeutiger_Name> (User, SpielID)
[/highlight]

...
Wobei ich nicht glaube, das ein User 2 Browser offen hat und sich selbst 2x zeitlgleich tippt :-)

Das muß er auch nicht. U.U. genügt es, wenn er mit zittrigen Fingern und Doppelklick - oder weil ihm das Neuladen der Seite zu lange dauert - das Formular zweimal innerhalb kurzer Zeit abschickt.

Gruß Falk

[hausl78]

Ah, danke für die Info!!

ALTER TABLE <tabelle> ADD   CONSTRAINT UNIQUE KEY <eindeutiger_Name> (User, SpielID)

Blöde Frage brauch ich den <eindeutiger_Name> dann irgendwo in der Abfrage oder ist das nur wegen der Definition damit die beiden "gmeinesam eindeutig" sind.

Sorry für die doofe Frage, aber ich weiß nichtmal wie ich da vernünftig googeln soll für mehreren Werte im Constraint.

Juergen

[ebis]

brauch ich den <eindeutiger_Name> dann irgendwo in der Abfrage

nein

oder ist das nur wegen der Definition damit die beiden "gmeinesam eindeutig" sind.
Juergen

so ist es

[Falk Prüfer]

...Blöde Frage brauch ich den <eindeutiger_Name> dann irgendwo in der Abfrage oder ist das nur wegen der Definition damit die beiden "gmeinesam eindeutig" sind.

Der <eindeutiger_Name> wird für den Index vergeben, welcher als Grundlage für den UNIQUE CONSTRAINT angelegt wird. Den Namen kannst du theoretisch sogar weglassen, dann denkt sich MySQL einen aus. Wenn du den Index später Bearbeiten oder Löschen willst ist es jedoch von Vorteil einen "sprechenden" Namen zu haben statt einer (mehr oder weniger) beliebigen Zeichenkette.

Gruß Falk

[hausl78]

Ich hab das jetzt gemacht, Query scheint durch zu sein.. jetzt wäre es folgendes für diese Tabelle

CREATE TABLE `tabtipps` (
    `TippID` INT(11) NOT NULL AUTO_INCREMENT,
    `Nickname` CHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `SpielIDRef` INT(11) NULL DEFAULT NULL,
    `TippTor1` INT(11) NULL DEFAULT NULL,
    `TippTor2` INT(11) NULL DEFAULT NULL,
    UNIQUE INDEX `TippID` (`TippID`),
    UNIQUE INDEX `NicknameUndSpielIDRef` (`Nickname`, `SpielIDRef`)
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=6009 

Danke!
Juergen

[Falk Prüfer]

Nur mal so als Anmerkung:

1. TippID sollte durchaus als PRIMARY KEY und nicht nur als UNIQUE angelegt werden.
2. Die Sinnhaftigkeit eines CHAR für den Nickname ziehe ich mal genauso in Zweifel wie ein INT(11) für TippTor1 und TippTor2. Dir ist der Unterschied zwischen CHAR und VARCHAR bewusst und du hast den Typ CHAR mit Bedacht gewählt? Für einen Tortip würde ich ein Maximum von 99 als durchaus Sinnvoll erachten, was einem INT(2) entsprechen würde. INT(11) ist da wohl "leicht überdimensioniert"
3. In meinen Augen macht ein leerer Nickname genauso wenig Sinn wie eine leere Spielreferenz oder ein leerer Tortip. Also warum sind diese Felder nicht NOT NULL?

Gruß Falk

[hausl78]

1. Hab ich eingetragen, danke!

2. INT(2) gesetzt, danke sehe ich auch so..
Nickname.. ja fixes Textfeld mit (jetzt) fix 25 Stellen.. Hab irgendwo gelesen das CHAR schneller ist als VARCHAR und die paar Bytes mehr kann ich leicht verkraften

3. Auch hier.. danke! ist geändert..

Na dann muss ich nur mehr den Rest in PHP machen

Juergen

[dibo33]

Für einen Tortip würde ich ein Maximum von 99 als durchaus Sinnvoll erachten, was einem INT(2) entsprechen würde. INT(11) ist da wohl "leicht überdimensioniert"

Hm, nur mal so für unbedarfte, welcher Vorteil ergibt sich zwischen int(2) und int(11)?