Suppression aléatoire d'un nombre inconnu d'enregistrements

Question

J'ai une table, nommée items.
La structure est: {name, id, category_id}

Il y a un nombre inconnu d'enregistrements dans la table, je dois limiter le nombre d'enregistrements pour chaque catégorie à un nombre MAX, 20 par exemple.

Pseudo

DELETE FROM items
WHERE (SELECT COUNT(category_id) FROM items)> 20
LIMIT (SELECT COUNT(category_id) FROM items) - 20

Answer 1

DELETE FROM items 
WHERE (category_id,id) not in (
  SELECT category_id, id FROM items  a WHERE id IN (
     SELECT id FROM items s 
     WHERE a.category_id = s.category_id
     ORDER BY category_id LIMIT 20) 
  ORDER BY  id)

Answer 2

Si vous avez une grande table, la manière la plus efficace est de recréer cette table:

create table items_temp like items;
INSERT into items_temp (id, category_id, ... ) 
  SELECT id, category_id, ... from items orig
  WHERE id in (
     SELECT id from items items_select
     WHERE orig.category_id = items_select.category_id
     ORDER by category_id LIMIT 20
     );
RENAME table items to items_old, items_temp to items;

MODIFICATION: TRUNCATE table items_old Ne supprimez pas - c'est ce que nous essayons d'éviter - les suppressions supprimeront effectivement chaque ligne et respecteront les contraintes étrangères, c'est ce qui prendra beaucoup de CPU et d'E/S, le troncage ne le fait pas.

Sinon, vous risquez fort de faire tomber votre base de données pendant la phase de suppression. Si c'est une petite quantité de données, ce n'est pas grave.