Tester chce wyczyścić tabelę pomostową przed ponownym uruchomieniem testu. Czy powinno być DELETE, TRUNCATE czy DROP? Zły wybór może oznaczać powolną pracę, brak stołu lub bardzo złe popołudnie na produkcji.

Poniższe przykłady DELETE i DROP uruchomiliśmy w SQLite 3.50, a dane wyjściowe pochodzą z tego uruchomienia. W SQLite nie ma polecenia TRUNCATE, więc uwagi TRUNCATE opierają się na oficjalnej dokumentacji PostgreSQL, MySQL i SQL Server, a nie na naszym własnym teście.

Krótka odpowiedź

• Chcesz usunąć niektóre wiersze? Użyj DELETE ... WHERE.
• Chcesz opróżnić stół, ale go zatrzymać? Użyj TRUNCATE, jeśli jest to obsługiwane.
• Chcesz, żeby stół zniknął na dobre? Użyj DROP TABLE.

DELETE: usuń wiersze

DELETE to polecenie zmiany danych (DML). Usuwa wiersze pojedynczo, uruchamia wyzwalacze na poziomie wiersza i można je filtrować. Przygotowaliśmy małą tabelę z wyzwalaczem audytu:

CREATE TABLE logs (id INTEGER PRIMARY KEY AUTOINCREMENT, level TEXT);
INSERT INTO logs (level) VALUES ('INFO'),('ERROR'),('INFO'),('DEBUG');

CREATE TABLE audit (msg TEXT);
CREATE TRIGGER logs_delete AFTER DELETE ON logs
BEGIN INSERT INTO audit VALUES ('deleted ' || OLD.id); END;

DELETE FROM logs WHERE level = 'DEBUG';

SELECT id, level FROM logs;  -- (1,'INFO'), (2,'ERROR'), (3,'INFO')
SELECT * FROM audit;         -- 'deleted 4'

Poszedł tylko pasujący wiersz i wyzwalacz to zarejestrował. Uruchomienie DELETE FROM logs bez klauzuli WHERE spowodowało usunięcie pozostałych trzech wierszy, a tabela kontroli zawierała wówczas cztery wpisy, po jednym na każdy usunięty wiersz.

DELETE nie resetuje liczników automatycznego przyrostu. Po opróżnieniu tabeli nasza następna wstawka otrzymała id 5, a nie 1. W SQLite jest to spowodowane AUTOINCREMENT. Kolumny tożsamości MySQL InnoDB i SQL Server również kontynuują pracę od miejsca, w którym znajdowały się po wykonaniu DELETE.

TRUNCATE: opróżnij tabelę

TRUNCATE TABLE logs; usuwa każdy wiersz na raz. Nie może przyjmować klauzuli WHERE. Zamiast usuwać wiersze jeden po drugim, baza danych zazwyczaj zwalnia strony danych tabeli, dlatego w przypadku dużych tabel działa znacznie szybciej.

Kiedy uruchomiliśmy go w SQLite, otrzymaliśmy błąd składniowy, ponieważ SQLite nie ma polecenia:

TRUNCATE TABLE logs;
-- near "TRUNCATE": syntax error

W SQLite użyj DELETE FROM logs; bez klauzuli WHERE. Dokumentacja SQLite opisuje „optymalizację obcięcia”, która sprawia, że ​​jest to szybkie, gdy tabela nie ma wyzwalaczy. Nasza tabela miała wyzwalacz, więc usuwanie odbywało się wiersz po wierszu i uruchamiało go dla każdego wiersza.

W bazach danych obsługujących TRUNCATE, według ich dokumentacji:

• Zwykle nie uruchamia wyzwalacze DELETE na poziomie wiersza.
• Zwykle resetuje liczniki tożsamości lub automatycznego przyrostu, na przykład w MySQL i SQL Server. W PostgreSQL dodajesz RESTART IDENTITY.
• W większości systemów potrzebuje silniejszych uprawnień niż DELETE.

DROP: usuń sam stół

DROP TABLE to polecenie schematu (DDL). Usuwa dane, definicję tabeli, jej indeksy, wyzwalacze i ograniczenia. Ma to również wpływ na wszystko, co odnosi się do tabeli, na przykład widoki lub klucze obce.

DROP TABLE logs;
SELECT * FROM logs;
-- no such table: logs

DROP TABLE IF EXISTS logs;  -- no error, even though it is gone

IF EXISTS jest przydatny w skryptach instalacyjnych, które mogą być uruchamiane więcej niż raz. Widoki opuszczonej tabeli mogą się zepsuć, jak pokazaliśmy w wyjaśnieniu widoków SQL.

Tabela porównawcza

Czy możesz się wycofać?

W tym miejscu wiele artykułów nadmiernie upraszcza. Szczera odpowiedź jest taka, że ​​to zależy od bazy danych.

DELETE można wycofać w ramach transakcji w dowolnym miejscu. Przetestowaliśmy to:

BEGIN;
DELETE FROM logs;
SELECT COUNT(*) FROM logs;  -- 0
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 3

DROP również wycofano w SQLite, który obsługuje transakcyjny DDL:

BEGIN;
DROP TABLE logs;
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 1 (the table is back)

Dla TRUNCATE i DROP w innych bazach danych, zgodnie z ich dokumentacją:

Zatem „TRUNCATE nigdy nie można wycofać” jest błędne w przypadku PostgreSQL i SQL Server, a „zawsze można go wycofać” jest błędne w przypadku MySQL. Poza transakcją żadnej z nich nie można cofnąć bez kopii zapasowej.

Dlaczego TRUNCATE jest szybki

DELETE rejestruje każdy usunięty wiersz, aby można było cofnąć i powtórzyć zmianę, uruchamia wyzwalacze i sprawdza ograniczenia wiersz po wierszu. Na stole z milionami wierszy powoduje to dużo pracy i objętości dziennika. TRUNCATE zazwyczaj zwalnia pamięć w jednej operacji, rejestrując znacznie mniej. Aby usunąć duży podzbiór, a nie wszystko, usuwaj partiami, aby uniknąć długich blokad. Pokrywamy koszty blokowania i zapytań w ramach optymalizacji zapytań SQL.

Klucze obce i uprawnienia

• Klucze obce. SQL Server odmawia CIĘCIA tabeli, do której odwołuje się klucz obcy. PostgreSQL wymaga również obcięcia tabel referencyjnych lub użycia CASCADE. Zamiast tego polecenie DELETE sprawdza każdy wiersz pod kątem ograniczeń.
• Uprawnienia. DELETE wymaga uprawnień do usuwania. TRUNCATE często wymaga ALTER lub dedykowanego prawa TRUNCATE, a DROP wymaga własności lub ALTER w schemacie.
• Replikacja i audyt. Jeśli inne systemy zależą od dzienników zmian lub wyzwalaczy na poziomie wiersza, TRUNCATE może je pominąć. Najpierw skontaktuj się z właścicielem tych systemów.

Którego użyć

W przypadku danych testowych popularny jest TRUNCATE, ponieważ resetuje identyfikatory, dzięki czemu testy są powtarzalne. Zawijanie każdego testu w wycofywaną transakcję to kolejna opcja wyjaśniona w Transakcje SQL i ACID.

Lista kontrolna bezpieczeństwa

1. Sprawdź, z jaką bazą danych i środowiskiem jesteś połączony.
2. Najpierw uruchom klauzulę WHERE jako SELECT.
3. Zawiń zmianę w transakcji tam, gdzie obsługuje ją Twoja baza danych.
4. Utwórz najnowszą, przetestowaną kopię zapasową przed TRUNCATE lub DROP w środowisku produkcyjnym.
5. Umieść instrukcje DROP w sprawdzonych skryptach migracji, nigdy w konsolach ad hoc.
6. Sprawdź, co zależy od tabeli, np. widoki, klucze obce i raporty, zanim ją upuścisz.

Bezpieczne usuwanie dużych ilości danych

Usunięcie milionów wierszy jednym DELETE może zablokować tabelę na długi czas, zapełnić dziennik transakcji i spowolnić replikację. Powszechnym wzorcem jest usuwanie w małych partiach i powtarzanie, aż nic nie pozostanie:

-- SQL Server style (not run for this article)
DELETE TOP (5000) FROM logs WHERE created_at < '2026-01-01';

-- PostgreSQL style (not run for this article)
DELETE FROM logs
WHERE id IN (SELECT id FROM logs WHERE created_at < '2026-01-01' LIMIT 5000);

Uruchom instrukcję w pętli, z krótką przerwą między partiami, aż zgłosi zero wierszy. Każda partia to mała transakcja, więc blokady są krótkie, a inne zapytania nadal działają. Indeks w kolumnie filtra, tutaj created_at, zapewnia szybkość każdej partii.

W przypadku danych rosnących według daty, takich jak dzienniki i zdarzenia, partycjonowanie tabeli jest jeszcze lepsze. Każdy miesiąc znajduje się na osobnej partycji, a usuwanie starych danych staje się szybką operacją, która powoduje usunięcie lub obcięcie jednej partycji zamiast usuwania wierszy jeden po drugim.

Miękkie usuwa

Wiele aplikacji nigdy fizycznie nie usuwa rekordów biznesowych. Dodają kolumnę taką jak deleted_at i zamiast tego ustawiają ją, a następnie zapytania filtrują według deleted_at IS NULL. Miękkie usunięcia ułatwiają odzyskiwanie i przechowują historię na potrzeby audytów, ale każde zapytanie musi pamiętać filtr, co jest dobrym zadaniem w przypadku widoku. Unikalne ograniczenia również wymagają ostrożności, ponieważ nietrwale usunięty wiersz nadal zachowuje swoją wartość. Przepisy dotyczące prywatności mogą wymagać rzeczywistego usunięcia danych osobowych po pewnym czasie, więc trwałe usunięcie nie zastępuje polityki przechowywania.

Podsumowanie

DELETE to precyzyjne i ostrożne narzędzie. TRUNCATE to przycisk szybkiego resetowania, a DROP całkowicie usuwa tabelę. Największym ryzykiem jest założenie, że jedna baza danych zachowuje się jak inna, szczególnie w przypadku wycofywania zmian. W razie wątpliwości przetestuj najpierw polecenie na kopii tabeli we własnej bazie danych.

Najczęstsze pytania

Ucz się dalej