Requête SQL: Supprimer tous les enregistrements de la table sauf le plus récent N?

Question

Est-il possible de construire une seule requête mysql (sans variables) pour supprimer tous les enregistrements de la table, à l'exception de la dernière N (triée par id desc)?

Quelque chose comme ça, mais ça ne marche pas :)

 delete from table order by id ASC limit ((select count(*) from table ) - N)
 

Merci.

Answer 1

Vous ne pouvez pas supprimer les dossiers de cette façon, le problème principal étant que vous ne pouvez pas utiliser une sous-requête pour spécifier la valeur d'une clause LIMIT.

Cela fonctionne (testé dans MySQL 5.0.67):

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT 42 -- keep this many records
  ) foo
);

L'intermédiaire de la sous-requête est nécessaire. Sans elle, nous atteindrions deux erreurs:

1. Erreur SQL (1093): Vous ne pouvez pas spécifier une cible de la table 'table' pour la mise à jour dans la clause from - MySQL n'autorise pas à vous référer à la table que vous la suppression de l'intérieur d'une sous-requête directe.
2. Erreur SQL (1235): Cette version de MySQL ne supporte pas encore 'LIMIT & DANS le/TOUTES les/TOUTES les/CERTAINS sous-requête' - Vous ne pouvez pas utiliser la clause LIMIT au sein d'une sous-requête directe d'un PAS DANS l'opérateur.

Heureusement, à l'aide d'un intermédiaire sous-requête nous permet de contourner les deux de ces limitations.

---

NickC a souligné cette requête peut être optimisée de manière significative pour certains cas (comme celui-ci). Je recommande la lecture de sa réponse pour voir si elle correspond à la vôtre.

Answer 2

Je sais que je suis la résurrection de tout à fait une vieille question, mais j'ai récemment rencontré ce problème, mais besoin de quelque chose que les échelles à un grand nombre de puits. Il n'y avait pas de performance des données, et depuis que cette question a eu un peu d'attention, je pensais que je poste ce que j'ai trouvé.

Les solutions que effectivement travaillées ont été les Alex Barrett double sous-requête/NOT IN méthode (similaire au projet de Loi Karwin s), et Quassnoi de l' LEFT JOIN méthode.

Malheureusement, les deux méthodes ci-dessus, de créer de très grandes intermédiaire de tables temporaires et les performances se dégradent rapidement que le nombre d'enregistrements de ne pas être supprimé est grande.

Ce que j'ai réglé sur utilise Alex Barrett double sous-requête (merci!) mais les utilisations <= au lieu de NOT IN:

DELETE FROM `test_sandbox`
  WHERE id <= (
    SELECT id
    FROM (
      SELECT id
      FROM `test_sandbox`
      ORDER BY id DESC
      LIMIT 1 OFFSET 42 -- keep this many records
    ) foo
  )

Il utilise OFFSET , pour obtenir l'id de la Nième enregistrement et supprime cet enregistrement et tous les enregistrements précédents.

Depuis la commande est déjà une hypothèse de ce problème (ORDER BY id DESC), <= est un ajustement parfait.

Il est beaucoup plus rapide, puisque la table temporaire généré par la sous-requête contient un seul enregistrement à la place de N enregistrements.

Cas de Test

J'ai testé les trois méthodes de travail et la nouvelle méthode ci-dessus, dans les deux cas de test.

Les deux cas de test, l'utilisation de 10000 lignes existantes, tandis que le test de la première garde 9000 (efface le plus ancien de 1000) et le deuxième essai de conserve de 50 (efface le plus ancien 9950).

+-----------+------------------------+----------------------+
|           | 10000 TOTAL, KEEP 9000 | 10000 TOTAL, KEEP 50 |
+-----------+------------------------+----------------------+
| NOT IN    |         3.2542 seconds |       0.1629 seconds |
| NOT IN v2 |         4.5863 seconds |       0.1650 seconds |
| <=,OFFSET |         0.0204 seconds |       0.1076 seconds |
+-----------+------------------------+----------------------+

Ce qui est intéressant, c'est que l' <= méthode considère une meilleure performance de l'ensemble du conseil d'administration, mais en fait, est de mieux en mieux le plus vous gardez, au lieu de pire.

Answer 3

Malheureusement pour toutes les réponses données par d'autres gens, vous ne pouvez pas DELETE et SELECT à partir d'un tableau donné dans la même requête.

DELETE FROM mytable WHERE id NOT IN (SELECT MAX(id) FROM mytable);

ERROR 1093 (HY000): You can't specify target table 'mytable' for update 
in FROM clause

Ni le support de MySQL LIMIT dans une sous-requête. Ce sont les limitations de MySQL.

DELETE FROM mytable WHERE id NOT IN 
  (SELECT id FROM mytable ORDER BY id DESC LIMIT 1);

ERROR 1235 (42000): This version of MySQL doesn't yet support 
'LIMIT & IN/ALL/ANY/SOME subquery'

La meilleure réponse que je puisse arriver, c'est de faire ça en deux étapes:

SELECT id FROM mytable ORDER BY id DESC LIMIT n;

Recueillir les id et de faire d'eux une chaîne séparée par des virgules:

DELETE FROM mytable WHERE id NOT IN ( ...comma-separated string... );

(Normalement, l'interpolation par des virgules liste séparée dans une instruction SQL, présente certains risques d'injection SQL, mais dans ce cas, les valeurs ne sont pas en provenance d'une source non fiable, ils sont connus pour être des valeurs d'id de la base de données elle-même.)

remarque: Si ce n'est pas faire le travail en une seule requête, parfois de plus simple, il fait la solution la plus efficace.

Answer 4

DELETE  i1.*
FROM    items i1
LEFT JOIN
        (
        SELECT  id
        FROM    items ii
        ORDER BY
                id DESC
        LIMIT 20
        ) i2
ON      i1.id = i2.id
WHERE   i2.id IS NULL

Answer 5

Si votre identifiant est incrémental, utilisez quelque chose comme:

supprimer de la table où id <(select max (id) de la table) -N