Anzahl der Einträge einer MySQL-Tabelle kürzen

[Mareike]

Hallo,

könnt Ihr mir sagen, wie ich die Anzahl der Einträge meiner SQL-Tabelle kürzen kann, dabei möchte ich nicht jeden einzelnen Eintrag löschen. Es sollen die letzten hundert Einträge sortiert nach Datum übrig bleiben.

SELECT *  
FROM `nachrichten`  
ORDER BY `datum` desc  
LIMIT 0 , 100

Das wäre die Ausgabe, jetzt sollen automatisch alle anderen Einträge gelöscht werden.

Jetzt schonmal ganz lieben Dank und liebe Grüße,

Mareike

[dedlfix]

Tach!

könnt Ihr mir sagen, wie ich die Anzahl der Einträge meiner SQL-Tabelle kürzen kann, dabei möchte ich nicht jeden einzelnen Eintrag löschen. Es sollen die letzten hundert Einträge sortiert nach Datum übrig bleiben.

Haben diese hundert Datensätze ein Identifizierungsmerkmal? Dann lösche alle Datensätze, deren ID NOT IN (deine select-abfrage mit ID statt * als subquery).

dedlfix.

[Mareike]

Hallo

Haben diese hundert Datensätze ein Identifizierungsmerkmal? Dann lösche alle Datensätze, deren ID NOT IN (deine select-abfrage mit ID statt * als subquery).

Nein, kein eindeutiges Merkmal nur eben die 100 aktuellsten ...

Mmmh?

Mareike

[dedlfix]

Tach!

Haben diese hundert Datensätze ein Identifizierungsmerkmal? Dann lösche alle Datensätze, deren ID NOT IN (deine select-abfrage mit ID statt * als subquery).
Nein, kein eindeutiges Merkmal nur eben die 100 aktuellsten ...

Selbst mein erster Vorschlag funktioniert so nicht, denn es ist nicht möglich, in einer Tabelle gleichzeitig Datensätze zu löschen und ebendiese Tabelle als Subquery anzugeben. In einem Zuge geht das also auf keinen Fall, wenn sich die Selektionsbedingung nicht in einfachen Ausdrücke formulieren lässt. Auch LIMIT ist hier nicht verwendbar.

Ich würde nun versuchen, die "Datümer" der nach Datum absteigend sortierten und auf 100 limitierten Datensätze in einer temporären Tabelle zwischenzulagern und anschließend wie oben vorgeschlagen eine DELETE aber Datum statt ID und die temporäre Tabelle als Subquery zu verwenden. Dann kann es zwar vorkommen, dass ein paar Datensätze mehr oder weniger bleiben, aber die 100 war vermutlich nur ein Richtwert und keine exakte Bedingung.

Ein anderer Versuch wäre, das Datum des ältesten noch zu behaltenden Datensatzes zu ermitteln und alles ältere zu löschen. Der erste Teil sollte mit LIMIT 100,1 funktionieren.

Weiterhin wäre möglich, eine Abfrage über alle Datensätze absteigend sortiert und auf 100 limitiert in einem CREATE TABLE neue SELECT ... anzugeben, um so eine neue Tabelle zu erstellen und die andere dann zu löschen. Gegebenenfalls nachher die neue in den alten Namen umbenennen. Das kann aber Nebenwirkungen haben, weil das kein atomarer (einteiliger) Vorgang ist.

Alle Versuche stehen oder fallen aber mit der Qualität der Datumsangaben. Wenn da viele Datensätze mit demselben Datums- oder Datums-Zeit-Wert drinstehen wirst du die 100 nur mit dem letzten Vorschlag genau treffen. Wobei dann aber die Uneindeutigkeit bei der Sortierung nicht gerade hilfreich ist.

dedlfix.

[Mareike]

Hallo dedlfix

danke für Deine Antwort.

Mmmh, alles wohl nicht so einfach, wie ich dachte.

Wäre es einfacher bzw. möglich einfach die Tabellenlänge auf 100 Zeilen zu begrenzen, d.h. sobald der 101 Eintrag angelegt wird, wird der älteste Eintrag gelöscht ...?

Geht das in MySQL direkt?

LG, Mareike

[Fred Furunkelstein 2013]

Geht das in MySQL direkt?

Wie wäre es denn damit eine Spalte mit der notwendigen ID (Index und Autoinkrement) einfach hinzuzufügen? Wo ist das Problem?

Fred Furunkelstein 2013

[Mareike]

Hallo

Wo ist das Problem?

ok, das stimmt.

Wenn ich das mache, wie kann ich dann eben immer nur die letzten 100 aktuellsten Einträge aufbewahren?

LG

[Fred Furunkelstein 2013]

Wenn ich das mache, wie kann ich dann eben immer nur die letzten 100 aktuellsten Einträge aufbewahren?

* Subquerry
* Subquerry mit IN, ANY, SOME

DELETE FROM `nachrichten` WHERE `id` NOT IN  
(  
    SELECT `id`  
    FROM `nachrichten`  
    ORDER BY `datum` DESC  
    LIMIT 0 , 100  
)

Fred

[Fred Furunkelstein 2013]

Unter gewissen Umständen (keine Abfragen, kein konkurrierendes schreiben) würde ich bei konstant maximal 100 Datensätzen auf die Datenbank verzichten und etwas wie:

<?php  
exec ('echo `date`";Das ist die Beschreibung" >> datei.csv && filename=mktemp && tail -n 100 datei.csv > $filename && cat $filename > datei.csv && rm $filename');  
?>

machen. Das wird einigen nicht gefallen (weil es ein Linux als OS bedingt), ist aber vermutlich ungleich schneller.

Fred

[Mareike]

Mmmh,

DELETE FROM nachrichten WHERE id NOT IN

(
    SELECT id
    FROM nachrichten
    ORDER BY datum DESC
    LIMIT 0 , 100
)

  
da kriege ich leider diesen Fehler  
  
`This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery'`{:.language-sql}  
  
LG, Mareike

[Jens Holzkämper]

Tach,

DELETE FROM nachrichten WHERE id NOT IN

(
    SELECT id
    FROM nachrichten
    ORDER BY datum DESC
    LIMIT 0 , 100
)

>   
> da kriege ich leider diesen Fehler  
>   
> This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery'  
  
kann man Mysql mit  
~~~sql
  
DELETE FROM `nachrichten` WHERE `id` NOT IN (  
    SELECT * FROM (  
        SELECT `id`  
        FROM `nachrichten`  
        ORDER BY `datum` DESC  
        LIMIT 0 , 100  
    ) as t  
)

überlisten?

mfg
Woodfighter

[dedlfix]

Tach!

DELETE FROM nachrichten WHERE id NOT IN

(
    SELECT id
    FROM nachrichten
    ORDER BY datum DESC
    LIMIT 0 , 100
)

  

> > da kriege ich leider diesen Fehler  
> > This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery'  
  
Das, hab ich ja schon in der Revidierung meines ersten Vorschlags erwähnt, geht sowieso nicht, weil MySQL keine Subquerys auf dieselbe Tabelle unterstützt, wenn die Hauptquery schreibende Operationen vornimmt (zumindest DELETE und UPDATE sind davon betroffen).  
  

> kann man Mysql mit  
> ~~~sql
  

> DELETE FROM `nachrichten` WHERE `id` NOT IN (  
>     SELECT * FROM (  
>         SELECT `id`  
>         FROM `nachrichten`  
>         ORDER BY `datum` DESC  
>         LIMIT 0 , 100  
>     ) as t  
> )

überlisten?

Zumindest MySQL 5.5 kann damit aber umgehen. Hier wird vermutlich wegen der t-Subquery implizit eine temproräre Tabelle angelegt, was die oben erwähnte Limitierung umgeht. Ob damit allerdings auch die LIMIT-Beschränkung (in Mareikes Version) aufgehoben ist, konnte ich dem Handbuch nicht entnehmen.

dedlfix.

[Jens Holzkämper]

Tach,

Zumindest MySQL 5.5 kann damit aber umgehen. Hier wird vermutlich wegen der t-Subquery implizit eine temproräre Tabelle angelegt, was die oben erwähnte Limitierung umgeht.

jo, darauf hatte ich gehofft; kein schöner Hack, weil das im Prinzip davon abhängt, wie die jeweilige Version das umsetzt, aber die Problemstellung ist halt auch ungewöhnlich.

mfg
Woodfighter

[Mareike]

Hallo,

kann man Mysql mit

DELETE FROM nachrichten WHERE id NOT IN (
    SELECT * FROM (
        SELECT id
        FROM nachrichten
        ORDER BY datum DESC
        LIMIT 0 , 100
    ) as t
)

  
Ja, funktioniert prima!  
  
DANKE

[dedlfix]

Tach!

Wäre es einfacher bzw. möglich einfach die Tabellenlänge auf 100 Zeilen zu begrenzen, d.h. sobald der 101 Eintrag angelegt wird, wird der älteste Eintrag gelöscht ...?

Nein. Es gibt zwar bei CREATE TABLE eine MAX_ROWS-Klausel, aber die ist kein hartes Limit sondern nur ein Hinweis. Ein Limit stellt sie nur für ein Cluster dar, was aber bei dir wohl eher nicht im Einsatz ist.

Man kann eine Stored Procedure programmieren, die das Überschreiten des Limits prüft und dann handelt (gemäß zum Beispiel einer der bereits vorgeschlagenen Methoden) aber dann musst du dich so weit disziplinieren, dass du nur noch über diese Stored Procedure Daten einfügst und niemals daran vorbei.

dedlfix.

[Matti Mäkitalo]

Hi,

könnt Ihr mir sagen, wie ich die Anzahl der Einträge meiner SQL-Tabelle kürzen kann, dabei möchte ich nicht jeden einzelnen Eintrag löschen. Es sollen die letzten hundert Einträge sortiert nach Datum übrig bleiben.

SELECT *

FROM nachrichten
ORDER BY datum desc
LIMIT 0 , 100

  
DELETE kennt bei mysql auch LIMIT und ORDER BY. Ich würde daher folgendes versuchen: berechne die Gesamtzahl der Einträge. Dann lösche in umgekehrter Reihenfolge (d.h. die ältesten zuerst) und limitiere auf Gesamtzahl der Einträge weniger 100.  
  
Bis die Tage,  
Matti