[SQL] Datensätze löschen, jedoch letzten 100 behalten.

L

Lukasz

Hallo

Ich habe ein Projekt gesartet, bei dem alle paar Sekunden ein Datensatz in die Datenbank hinzukommt. Es geht um Buchungen von klicks.

Nun sind es nach ein paar Tagen schon über 100.000 Einträge, und das von gerade mal 50 Usern. Jetzt suche ich verzweifelt nach einem SQL Code, der mir alle Datensätze, bis auf die letzten 100 löscht.

Ich binn eventuell zu blöd zu suchen, aber ich finde nichts. Es sollen definitiv nur 100 Pro User überig bleiben. Die id des user habe ich.

So hänge ich mein WHERE user = und an. Nur habe ich auch eine Zeitspalte, und es sollen die 100 aktuellsten übrig bleiben.
 
Welches DBMS betrifft dies? MySQL?

Hier ein kleiner Ansatz: Ich weiß jetzt nicht, ob sich das in einer einzigen Anweisung lösen läßt, aber du könntest per TOP/LIMIT (Top wäre SQL Server, LIMIT MySQL) die letzten 100 Einträge pro User sortiert nach der Datensatz ID bzw. der Zeit herausholen. Dann weißt du, welche du nicht löschen darfst. Du besitzt nun also die Menge der Datensätze die du definitiv löschen kannst.

Ich hoffe dieser Ansatz hilft dir weiter.
 
Hallo

Erstmal ein recht herzliches Dankeschön. Habe ich auch zu anfangs so gedacht, jedoch bedutet das, dass ich entweder mit jedem Abruf ne Menege Daten vom MYSQL Server auslesen muss, oder aber ich den Speicher des Servers zumüllen muss.

Ich dachte es gibt in sql eventuell eine Löschmöglichkeit, die das mit einem Befehl kann.

Meine Tabelle sieht wie folgt aus:
Userid | Buchungsnummer | Zeit | Betrag

Jetzt sollte ich einfach hergehen, und eine Löschbedingung haben, alle bestimmten Datensätze die mehr wie hundert sind zu löschen. Leider geht sowas eventuell wahrscheinlich garnicht? -

Ich denke aber, dass es bestimmt im Zusammenhang eine Kombination gehen sollte.

Wenn ich einfach hergehen würde und nach Zeit löschen würde, dann würden inaktive oder selten aktive User ihre Buchungen nicht überblicken können. Das ist eben mein Problem. Und ich habe schon sehr viele Anfragen an den MYSQL Server, sschon alleine der Überprüfung der Echtheit, um Faker zu enttarnen.
 
Zuletzt bearbeitet von einem Moderator:
Du kannst keine direkte Bedingung an das DELETE anhängen.
Das hat folgende Gründe:
Die Bedingung basiert auf der Tabelle in der gelöscht wird. Die Bedingung lässt sich (wenn überhaupt) über ein Subquery erzeugen. Ein Subquery für ein manipulierendes Query darf aber nicht auf die gleiche Tabelle zugreifen. Dadurch werden mögliche Endlosschleifen und damit ein Absturz des Datenbankservers verhindert.

Desweiteren solltest Du bedenken, dass ein Subquery nur mit MySQL >= 4.1 funktioniert.
Auf jeden Fall solltest Du mit Transaktionen bzw. einer Read-Sperre (schließt eine Write-Sperre ein) arbeiten, da Du zwei Queries benötigst.

Als erstes solltest Du Dir Gedanken über ein Select-Statement machen, welches die zu löschenden Datensätze selektiert. Dazu unten mehr. Da liegt IMHO derzeit das größte Problem. Hast Du dieses, projezierst Du nur den Primärschlüssel und erzeugst damit eine temporäre Tabelle.

In die Bedingung des Delete schreibst Du dann:
SQL:
DELETE
#...
WHERE primärschlüssel IN (SELECT primärschlüssel FROM tempTable)
Hast Du MySQL < 4.1, sparst Du Dir die temporäre Tabelle und liest die Primärschlüssel aus. Diese schreibst Du komma-seperiert in einen String und fügst ihn statt des Subqueries ein.
Hast Du einen zusammengesetzten Primärschlüssel (p1 bis pn), musst Du alle Felder des Primärschlüssels projezieren und ihn in der Bedingung getrennt behandeln, mit and verknüpft. In etwa so:
SQL:
DELETE
#...
WHERE p1 IN (SELECT p1 FROM tempTable)
  AND p2 IN (SELECT p2 FROM tempTable)
  #...
  AND pn IN (SELECT pn FROM tempTable)
Der Workaround ist dann entsprechend, Du kannst natürlich dann alle gemeinsam projezieren und in getrennten Strings verarbeiten.

Das Select-Statement solltest Du zunächst unabhängig von dem Delete entwickeln und auf seine Richtigkeit überprüfen.

Evtl. wird es einfacher, die negierte Bedingung zu selektieren, also alle Datensätze zu suchen, die bestehen bleiben sollen und dann mit NOT IN zu arbeiten.

Nun zu dem Select:
Auch das ist IMHO nicht ohne Subquery zu machen. Ggf. findet sich auch hier ein Workaround, so dass es auch in MySQL < 4.1 machbar ist.

Ich würde wie folgt heran gehen (ich abstrahiere mal ein bisschen von Deiner Tabelle und der nötigen Projektion):

Zuerst ist der hundertste Datensatz eines jeden Users zu finden:
SQL:
SELECT * FROM
Tabelle t1
WHERE t1.Zeit=(SELECT min(t2.Zeit) FROM Tabelle t2
    WHERE t2.UserID=t1.UserID
    GROUP BY t2.UserID
    ORDER BY Zeit DESC
    LIMIT 100)
So erhältst Du den ältesten Datensatz zu jedem User, der noch bestehen bleiben soll.

Wenn Du das t1.Zeit= jetzt in ein t1.Zeit>= verwandelst und die Projektion auf den Primärschlüssel beschränkst, solltest Du alle Primärschlüssel erhalten, die zu den Datensätzen gehören, die Du behalten willst:
SQL:
SELECT 
  p1,
  #..
  pn
FROM Tabelle t1
WHERE t1.Zeit>=(SELECT min(t2.Zeit) FROM Tabelle t2
    WHERE t2.UserID=t1.UserID
    ORDER BY Zeit DESC
    LIMIT 100)
Schau am Besten erst mal, was bei dem Query rumkommt und ob es Deinen Bedingungen genügt.

Wichtig ist vor allem, dass Deine Zeit in einem "sortierfähigen" Format vorliegt, also entweder als MySQL-Datetime oder als Integer, der einen Unix-Timestamp repräsentiert.

Außerdem kann es bei dieser Vorgehensweise dazu kommen, dass über 100 Einträge beibehalten werden, wenn die Eintragungen an der Schnittstelle zu 100 Einträgen eines Users sekundengenau zeitgleich erfolgt sind.

Gruß hpvw
 
Zurück