[MySQL] Zwei Datensätze tauschen

[DaPhreak]

Hallo Forum,

Ich habe grade ein kleines Problem, was ich zwar lösen kann, wo ich mich aber frage, ob das nicht eleganter geht.

Auf ein Minimalbeispiel runtergebrochen sähe es etwa so aus:

Tabelle sequence speichert die Reihenfolge in der Dinge angeordnet sein sollen:

sequence: SeqNr | thingid

SeqNr ist dabei der Primärschlüssel und läuft von 0 bis N-1 durch.


Nun zeige ich die Sequenz an und will dem Nutzer die Möglichkeit geben, ein Ding in der Liste rauf/runterzuschieben. Ich bekomme also vom user den Input: Schiebe Ding mit SeqNr <n> einen rauf/runter.

Ich kann jetzt nicht einfach SeqNr <n> in der Tabelle um 1 verringern, denn dann gäbe es ja zwei Einträge mit SeqNr <n-1>, was nicht geht, da SeqNr der Primary Key ist. Ich muss also die thingids tauschen: Erst ThingId zu SeqNr <n> und <n-1> holen (SELECT) und dann die beiden wieder schreiben (UPDATE). Im schlimmsten Falle also 1 SELECT + 2 UPDATEs.

Die Frage ist nun ob das schneller/eleganter geht. Ich kenne viele der Kniffe von MySQL nicht (die lerne ich häufig durch mitlesen hier ), daher meine Hoffnung, dass es da was geeignetes gibt. Gabs da nicht mal was wie man UPDATE mit Werten aus einem SELECT füttern kann? Ich bekomms jedenfalls nicht zusammen. Danke schonmal.

 

[ice-breaker]

Das DB-Design ist schon komisch.
Warum hast du für das Ordering der Things eine extra-Tabelle und packst nicht eine order-Number in die thing-Tabelle rein?

Aber funktionieren wird das in deiner Lösung nur über einen Dreieckstausch, du wirst also einen der Werte erst an einen Platz legen müssen, der definitiv nicht benutzt wird (0, der maximale Wert usw)

 

[DaPhreak]

Das DB-Design ist schon komisch.
Warum hast du für das Ordering der Things eine extra-Tabelle und packst nicht eine order-Number in die thing-Tabelle rein?

Naja das war nur ein kleiner Extrakt des ganzen. In Wirklichkeit gibt es User, die die Dinge besitzen und in der Ordertabelle ist (userid, seqnr) der Primärschlüssel. Der Hauptgrund für die Sequenztabelle ist, dass es die reservierte thingid "-1" gibt, die an beliebig vielen Stellen dazwischen eingefügt werden kann. Die stellt am Ende in der sortierten Liste der Dinge eine Art Trennzeichen dar. Man könnte zwar dieses Trennzeichen in der things-Tabelle listen, aber da es mehrfach auftauchen kann ist die Beziehung nicht 1:1.

Mir gefällt das Design auch nicht, aber wie geht es besser?

Ich könnte natürlich der Sequenz-Tabelle einfach eine globale Id als Primärschlüssel verpassen und auf die Eindeutigkeit der SeqNr auf Scriptseite achten. Bloß ich hab mal gelernt, dass man das so nicht macht, weil das kein gutes Design ist.

Hm, nur was wäre hier dann gutes + effizientes Design?

Aber funktionieren wird das in deiner Lösung nur über einen Dreieckstausch, du wirst also einen der Werte erst an einen Platz legen müssen, der definitiv nicht benutzt wird (0, der maximale Wert usw)

Läuft also letztlich wieder auf 3 Anfragen hinaus, richtig?

 

[B2T]

Wieso eigentlich 2 UPDATE? Du kannst das auch mit einem UPDATE erledigen, wenn Du die if-Funktion verwendest oder den UPDATE auf einen Selfjoin ansetzt.

UPDATE sequence
  SET thingId = if(seqNr = <<n>>,<<thingId n-1>>,<<thingId n>>)
  WHERE seqNr = <<n>> - 1 or seqNr = <<n>>;

oder

UPDATE sequence s1
  JOIN sequence s2
    ON s1.seqNr - 1 = s2.seqNr
  SET s1.thingId = <<thingId n>>, s2.thingId = <<thingId n-1>>
  WHERE s2.seqNr = <<n>>;

Ich vermute, die erste Version ist effizienter.

UPDATE aus einem SELECT versorgen geht leider nicht, weil MySQL meckert, wenn in einem SELECT in der WHERE-Klausel die vom UPDATE betroffene Tabelle adressiert wird.

Also entweder Du versorgst den UPDATE von außen über PHP oder Du verwendest benutzerdefinierte Variablen:

SELECT
  @thingId_n-1 := s1.thingId,
  @thingId_n := s2.thingId
FROM sequence s1
  JOIN sequence s2
    ON s1.seqNr - 1 = s2.seqNr
  WHERE s2.seqNr = <<n>>;

Dann sieht der UPDATE (ohne Selfjoin) so aus:

UPDATE sequence
  SET thingId = if(seqNr = <<n>>,@thingId_n-1,@thingId_n)
  WHERE seqNr = <<n>> - 1 or seqNr = <<n>>;

 

[DaPhreak]

Hey, danke für die Antwort! Genau solche Kniffe in MySQL meinte ich. Okay IF kannte ich eigentlich, aber das bei UPDATE zu verwenden, da wär ich gar nicht drauf gekommen, coole Idee.

Also entweder Du versorgst den UPDATE von außen über PHP oder Du verwendest benutzerdefinierte Variablen:

SELECT
  @thingId_n-1 := s1.thingId,
  @thingId_n := s2.thingId
FROM sequence s1
  JOIN sequence s2
    ON s1.seqNr - 1 = s2.seqNr
  WHERE s2.seqNr = <<n>>;

Dann sieht der UPDATE (ohne Selfjoin) so aus:

UPDATE sequence
  SET thingId = if(seqNr = <<n>>,@thingId_n-1,@thingId_n)
  WHERE seqNr = <<n>> - 1 or seqNr = <<n>>;

Das sieht sehr elegant aus, gefällt mir. Bin mir nur nicht so 100 pro sicher obs auch wirklich schneller ist als separate Updates... bei Gelegenheit mal testen. Das Script für das ich das brauchte ist schon mit der 3-Query-Lösung im Einsatz, beim nächsten mal probier ich das.