Czy jest możliwe zbudowanie pojedynczego zapytania mysql (bez zmiennych) w celu usunięcia wszystkich rekordów z tabeli, z wyjątkiem ostatniego N (posortowanego według identyfikatora desc)?
Coś takiego, tylko to nie działa :)
delete from table order by id ASC limit ((select count(*) from table ) - N)
Dzięki.
Odpowiedzi:
Nie możesz usunąć rekordów w ten sposób, a głównym problemem jest to, że nie możesz użyć podzapytania do określenia wartości klauzuli LIMIT.
To działa (testowane w 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
);
Wymagane jest podzapytanie pośrednie . Bez tego napotkalibyśmy dwa błędy:
Na szczęście użycie pośredniego podzapytania pozwala ominąć oba te ograniczenia.
Nicole zwróciła uwagę, że to zapytanie można znacznie zoptymalizować dla pewnych przypadków użycia (takich jak ten). Polecam również przeczytanie tej odpowiedzi, aby sprawdzić, czy pasuje do Twojej.
Wiem, że wskrzeszam dość stare pytanie, ale ostatnio napotkałem ten problem, ale potrzebowałem czegoś, co dobrze skaluje się do dużej liczby . Nie było żadnych istniejących danych dotyczących wydajności, a ponieważ to pytanie przyciągnęło sporo uwagi, pomyślałem, że opublikuję to, co znalazłem.
Rozwiązaniami, które faktycznie zadziałały, były podwójne pod-zapytanie /NOT IN metoda Alexa Barretta (podobne do Billa Karwina ) i metoda QuassnoiLEFT JOIN .
Niestety obie powyższe metody tworzą bardzo duże pośrednie tabele tymczasowe, a wydajność szybko spada, ponieważ liczba rekordów, które nie są usuwane, staje się duża.
To, na czym się zdecydowałem, wykorzystuje podwójne zapytanie podrzędne Alexa Barretta (dzięki!), Ale <=zamiast 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
)
Używa OFFSETdo uzyskania identyfikatora N- tego rekordu i usuwa ten rekord oraz wszystkie poprzednie rekordy.
Ponieważ zamawianie jest już założeniem tego problemu ( ORDER BY id DESC), <=jest to idealne dopasowanie.
Jest to znacznie szybsze, ponieważ tymczasowa tabela wygenerowana przez podzapytanie zawiera tylko jeden rekord zamiast N rekordów.
Przetestowałem trzy metody pracy i nową metodę powyżej w dwóch przypadkach testowych.
Oba przypadki testowe używają 10000 istniejących wierszy, podczas gdy pierwszy test zachowuje 9000 (usuwa najstarszy 1000), a drugi test zachowuje 50 (usuwa najstarszy 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 |
+-----------+------------------------+----------------------+
Co ciekawe, <=metoda zapewnia lepszą wydajność we wszystkich obszarach, ale w rzeczywistości staje się lepsza, im więcej trzymasz, zamiast gorzej.
ROW_NUMBER(): stackoverflow.com/questions/603724/ ...
Niestety w przypadku wszystkich odpowiedzi udzielonych przez innych ludzi nie możesz DELETEiz SELECTdanej tabeli w tym samym zapytaniu.
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
Nie może też obsługiwać MySQL LIMITw podzapytaniu. To są ograniczenia 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'
Najlepszą odpowiedzią, jaką mogę wymyślić, jest zrobienie tego w dwóch etapach:
SELECT id FROM mytable ORDER BY id DESC LIMIT n;
Zbierz identyfikatory i utwórz z nich ciąg oddzielony przecinkami:
DELETE FROM mytable WHERE id NOT IN ( ...comma-separated string... );
(Zwykle interpolacja listy oddzielonej przecinkami do instrukcji SQL wprowadza pewne ryzyko iniekcji SQL, ale w tym przypadku wartości nie pochodzą z niezaufanego źródła, są znane jako liczby całkowite z samej bazy danych).
uwaga: chociaż nie pozwala to na wykonanie zadania w jednym zapytaniu, czasami najskuteczniejsze jest prostsze rozwiązanie do wykonania.
DELETE FROM mytable WHERE id NOT IN (SELECT id FROM mytable ORDER BY id DESC LIMIT 3);działa dobrze.
Jeśli twój identyfikator jest przyrostowy, użyj czegoś takiego jak
delete from table where id < (select max(id) from table)-N
Aby usunąć wszystkie rekordy z wyjątkiem ostatniego N , możesz użyć zapytania przedstawionego poniżej.
Jest to jedno zapytanie, ale zawiera wiele instrukcji, więc w rzeczywistości nie jest to jedno zapytanie, tak jak było to zamierzone w pierwotnym pytaniu.
Potrzebujesz także zmiennej i wbudowanej (w zapytaniu) przygotowanej instrukcji z powodu błędu w MySQL.
Mam nadzieję, że i tak może się przydać ...
nnn to wiersze do zachowania, a tabela to tabela, nad którą pracujesz.
Zakładam, że masz rekord automatycznie zwiększający się o nazwie id
SELECT @ROWS_TO_DELETE := COUNT(*) - nnn FROM `theTable`;
SELECT @ROWS_TO_DELETE := IF(@ROWS_TO_DELETE<0,0,@ROWS_TO_DELETE);
PREPARE STMT FROM "DELETE FROM `theTable` ORDER BY `id` ASC LIMIT ?";
EXECUTE STMT USING @ROWS_TO_DELETE;
Zaletą tego podejścia jest wydajność : przetestowałem zapytanie w lokalnej bazie danych z około 13 000 rekordami, zachowując ostatnie 1000. Działa w 0,08 sekundy.
Skrypt z zaakceptowanej odpowiedzi ...
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
);
Trwa 0,55 sekundy. Około 7 razy więcej.
Środowisko testowe: mySQL 5.5.25 na MacBookPro i7 z końca 2011 roku z dyskiem SSD
DELETE FROM table WHERE ID NOT IN
(SELECT MAX(ID) ID FROM table)
wypróbuj poniższe zapytanie:
DELETE FROM tablename WHERE id < (SELECT * FROM (SELECT (MAX(id)-10) FROM tablename ) AS a)
wewnętrzne zapytanie podrzędne zwróci 10 pierwszych wartości, a zapytanie zewnętrzne usunie wszystkie rekordy z wyjątkiem 10 pierwszych.
W wielu przypadkach użycie identyfikatora do tego zadania nie jest możliwe. Na przykład - stół ze statusami Twittera. Oto wariant z określonym polem datownika.
delete from table
where access_time >=
(
select access_time from
(
select access_time from table
order by access_time limit 150000,1
) foo
)
Chciałem tylko wrzucić to do miksu dla każdego, kto używa Microsoft SQL Server zamiast MySQL. Słowo kluczowe „Limit” nie jest obsługiwane przez MSSQL, więc musisz użyć alternatywy. Ten kod działał w SQL 2008 i jest oparty na tym poście SO. https://stackoverflow.com/a/1104447/993856
-- Keep the last 10 most recent passwords for this user.
DECLARE @UserID int; SET @UserID = 1004
DECLARE @ThresholdID int -- Position of 10th password.
SELECT @ThresholdID = UserPasswordHistoryID FROM
(
SELECT ROW_NUMBER()
OVER (ORDER BY UserPasswordHistoryID DESC) AS RowNum, UserPasswordHistoryID
FROM UserPasswordHistory
WHERE UserID = @UserID
) sub
WHERE (RowNum = 10) -- Keep this many records.
DELETE UserPasswordHistory
WHERE (UserID = @UserID)
AND (UserPasswordHistoryID < @ThresholdID)
Trzeba przyznać, że to nie jest eleganckie. Jeśli jesteś w stanie zoptymalizować to pod kątem Microsoft SQL, udostępnij swoje rozwiązanie. Dzięki!
Jeśli chcesz usunąć rekordy również w oparciu o inną kolumnę, oto rozwiązanie:
DELETE
FROM articles
WHERE id IN
(SELECT id
FROM
(SELECT id
FROM articles
WHERE user_id = :userId
ORDER BY created_at DESC LIMIT 500, 10000000) abc)
AND user_id = :userId
DELETE FROM table WHERE id NOT IN (
SELECT id FROM table ORDER BY id, desc LIMIT 0, 10
)
Dlaczego nie
DELETE FROM table ORDER BY id DESC LIMIT 1, 123456789
Po prostu usuń wszystkie wiersze oprócz pierwszego (kolejność to DESC!), Używając bardzo dużej liczby jako drugiego argumentu LIMIT. Spójrz tutaj
Odpowiadając na to po długim czasie ... Przyszedłem do tej samej sytuacji i zamiast skorzystać z wymienionych odpowiedzi, przyszedłem poniżej -
DELETE FROM table_name order by ID limit 10
Spowoduje to usunięcie pierwszych 10 rekordów i zachowanie najnowszych rekordów.