Dokumentacja SQLite z przykładami: DELETE

Q: Jak usunąć wiersz w SQLite?

Użyj DELETE FROM table_name WHERE condition;. Klauzula WHERE wybiera, które wiersze znikną. Na przykład DELETE FROM users WHERE id = 7; usuwa jednego użytkownika o id 7. Bez WHERE usuwany jest każdy wiersz tabeli.

Q: Jak usunąć wszystkie wiersze z tabeli SQLite?

Uruchom DELETE FROM tablename; bez klauzuli WHERE. SQLite nie ma instrukcji TRUNCATE: DELETE bez filtra jest jej odpowiednikiem, a SQLite optymalizuje go wewnętrznie (tak zwana 'truncate optimization'). Aby zresetować też liczniki AUTOINCREMENT, usuń potem odpowiedni wpis z sqlitesequence.

Q: Czy SQLite potrafi kaskadowo usuwać wiersze w powiązanych tabelach?

Tak, jeśli zadeklarujesz ON DELETE CASCADE na kluczu obcym i włączysz klucze obce przez PRAGMA foreign_keys = ON;. W SQLite klucze obce są domyślnie wyłączone, więc ta pragma ma znaczenie: bez niej kaskady są po cichu ignorowane.

Q: Jak zobaczyć, które wiersze zostały usunięte?

Dodaj klauzulę RETURNING: DELETE FROM users WHERE active = 0 RETURNING id, email; zwraca usunięte wiersze tak jak SELECT. Przydaje się to do logowania, funkcji cofania albo potwierdzenia, że usunięto dokładnie to, co miało zniknąć.

DELETE usuwa wiersze i nic więcej

DELETE wyjmuje wiersze z tabeli. Nie usuwa tabeli, nie zmienia jej schematu i nie dotyka innych tabel (chyba że skonfigurowano kaskady). Składnia jest krótka:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    active INTEGER NOT NULL DEFAULT 1
);

INSERT INTO users (email, active) VALUES
    ('ada@example.com', 1),
    ('boris@example.com', 0),
    ('rosa@example.com', 1);

DELETE FROM users WHERE id = 2;

SELECT * FROM users;

DELETE FROM users WHERE id = 2; znajduje wiersze spełniające warunek i je usuwa. Pozostałe dwa wiersze są nietknięte. Sama tabela wciąż istnieje i możesz dalej do niej wstawiać.

Model myślowy: DELETE to SELECT, który zamiast zwracać pasujące wiersze, wyrzuca je.

Całą pracę wykonuje klauzula WHERE

Każde poważne DELETE stoi i upada na klauzuli WHERE. Zapiszesz ją dobrze, a usuniesz to, co zamierzasz. Zapiszesz ją źle, a usuniesz więcej, czasem całą tabelę.

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT,
    published INTEGER NOT NULL DEFAULT 0,
    views INTEGER NOT NULL DEFAULT 0
);

INSERT INTO posts (title, published, views) VALUES
    ('Draft one',   0, 0),
    ('Old hit',     1, 5000),
    ('Published',   1, 120),
    ('Empty draft', 0, 0);

DELETE FROM posts WHERE published = 0 AND views = 0;

SELECT * FROM posts;

Oba nieopublikowane szkice bez wyświetleń zniknęły. Opublikowane wiersze przetrwały, bo warunek do nich nie pasował. Możesz użyć każdego wyrażenia, które przyjmuje WHERE: IN, LIKE, BETWEEN, podzapytań i kombinacji AND/OR.

Nawyk, który warto wyrobić: zanim uruchomisz DELETE, najpierw uruchom tę samą klauzulę WHERE jako SELECT.

-- Podgląd tego, co zniknie:
SELECT * FROM posts WHERE published = 0 AND views = 0;

-- Wiersze się zgadzają? Teraz je usuń:
DELETE FROM posts WHERE published = 0 AND views = 0;

Ten dwuetapowy taniec uratował więcej baz niż wszystkie narzędzia do kopii zapasowych razem wzięte.

DELETE bez WHERE opróżnia tabelę

Pomiń WHERE, a DELETE usunie każdy wiersz:

CREATE TABLE log (
    id INTEGER PRIMARY KEY,
    message TEXT
);

INSERT INTO log (message) VALUES ('one'), ('two'), ('three');

DELETE FROM log;

SELECT COUNT(*) AS remaining FROM log;

Tabela jest pusta, ale wciąż istnieje. SQLite nie ma instrukcji TRUNCATE: DELETE FROM table; jest jej odpowiednikiem, a SQLite stosuje wewnętrzną "truncate optimization", która zwalnia wszystkie strony naraz, zamiast usuwać wiersze pojedynczo. To szybkie, ale wciąż jest to operacja transakcyjna, którą można wycofać.

Jeśli klucz główny używa AUTOINCREMENT, licznik sam się nie zresetuje. Aby id znów zaczynały się od 1, wyczyść też wiersz sekwencji:

DELETE FROM log;
DELETE FROM sqlite_sequence WHERE name = 'log';

Przy zwykłym INTEGER PRIMARY KEY (bez AUTOINCREMENT) SQLite i tak swobodnie używa id ponownie, więc nie jest to potrzebne.

Usuwanie kilku konkretnych wierszy

IN to najczystszy sposób na usunięcie znanego zestawu wierszy:

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT,
    status TEXT
);

INSERT INTO tasks (title, status) VALUES
    ('write docs',   'done'),
    ('review PR',    'todo'),
    ('deploy',       'done'),
    ('write tests',  'todo'),
    ('archive',      'done');

DELETE FROM tasks WHERE id IN (1, 3, 5);

SELECT * FROM tasks;

Usuwanie możesz też sterować podzapytaniem, co przydaje się, gdy wiersze do usunięcia wyznacza złączenie albo inna tabela:

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT,
    status TEXT
);
CREATE TABLE archived_status (status TEXT);

INSERT INTO tasks (title, status) VALUES
    ('a', 'done'), ('b', 'todo'), ('c', 'cancelled');
INSERT INTO archived_status VALUES ('done'), ('cancelled');

DELETE FROM tasks
WHERE status IN (SELECT status FROM archived_status);

SELECT * FROM tasks;

SQLite nie obsługuje składni DELETE ... JOIN tak jak MySQL, ale podzapytanie w WHERE wykonuje to samo zadanie.

RETURNING: zobacz, co usunięto

Dodaj RETURNING, aby dostać usunięte wiersze jako zbiór wyników, tak jak z SELECT:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    active INTEGER NOT NULL DEFAULT 1
);

INSERT INTO users (email, active) VALUES
    ('ada@example.com',   1),
    ('boris@example.com', 0),
    ('carol@example.com', 0);

DELETE FROM users
WHERE active = 0
RETURNING id, email;

Dostajesz id i email każdego usuniętego wiersza. To bezcenne przy:

Logowaniu dokładnie tego, co zostało usunięte.
Budowaniu funkcji cofania (zwrócone wiersze odkładasz gdzieś na bok).
Potwierdzaniu w jednej rundzie, że usunięcie objęło oczekiwane wiersze.

RETURNING działa z INSERT, UPDATE i DELETE. Szczegółowo omawia go osobna strona.

ON DELETE CASCADE dla powiązanych wierszy

Gdy tabela nadrzędna i podrzędna są połączone kluczem obcym, usunięcie wiersza nadrzędnego zostawia osierocone wiersze podrzędne, chyba że każesz SQLite działać kaskadowo:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Le Guin'), (2, 'Borges');
INSERT INTO books (title, author_id) VALUES
    ('A Wizard of Earthsea', 1),
    ('The Dispossessed',     1),
    ('Ficciones',            2);

DELETE FROM authors WHERE id = 1;

SELECT * FROM books;

Usunięcie autora usuwa też jego książki. Bez ON DELETE CASCADE to samo DELETE albo by się udało i zostawiło osierocone książki (gdy klucze obce są wyłączone), albo zakończyło się błędem ograniczenia (gdy są włączone).

Największa pułapka: w SQLite klucze obce są domyślnie wyłączone. Musisz uruchamiać PRAGMA foreign_keys = ON; w każdym połączeniu. Jeśli pragma nie jest ustawiona, ON DELETE CASCADE jest po cichu ignorowane i książki zostają. Większość sterowników aplikacyjnych ustawia to za ciebie albo udostępnia opcję: sprawdź swój.

Inne opcje kaskadowe, które warto znać: ON DELETE SET NULL (czyści klucz obcy), ON DELETE RESTRICT (odmawia usunięcia, jeśli istnieją wiersze podrzędne), ON DELETE NO ACTION (domyślna, w większości przypadków działa jak RESTRICT).

DELETE z LIMIT (opcja kompilacji)

Niektóre kompilacje SQLite obsługują DELETE ... LIMIT, przydatne do stopniowego usuwania danych z ogromnych tabel partiami:

DELETE FROM logs
WHERE created_at < '2024-01-01'
ORDER BY created_at
LIMIT 1000;

Wymaga to SQLite skompilowanego z SQLITE_ENABLE_UPDATE_DELETE_LIMIT. Oficjalne pliki binarne i większość bibliotek dla języków (sqlite3 w Pythonie, better-sqlite3 w Node) mają to włączone. Jeśli twoja nie ma, dostaniesz błąd składni. Wtedy użyj podzapytania:

DELETE FROM logs
WHERE id IN (
    SELECT id FROM logs
    WHERE created_at < '2024-01-01'
    ORDER BY created_at
    LIMIT 1000
);

Usuwanie partiami utrzymuje małe transakcje, co ma znaczenie, gdy inne połączenia czytają bazę.

Duże usunięcia opakuj w transakcję

DELETE jest domyślnie transakcyjne: albo znika każdy pasujący wiersz, albo żaden. Ale gdy zamierzasz usunąć dużo, jawna transakcja pozwala zrobić ROLLBACK, jeśli coś wygląda podejrzanie:

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    balance INTEGER
);

INSERT INTO accounts (balance) VALUES (100), (200), (0), (50), (0);

BEGIN;
    DELETE FROM accounts WHERE balance = 0;
    SELECT COUNT(*) AS remaining FROM accounts;
ROLLBACK;

SELECT COUNT(*) AS still_here FROM accounts;

ROLLBACK całkowicie cofa usunięcie. W prawdziwej sesji zrobisz COMMIT, gdy liczba wierszy będzie się zgadzać. Transakcje są też dużo szybsze, gdy usuwasz wiele wierszy po jednej instrukcji naraz: opakowanie pętli w BEGIN/COMMIT pozwala uniknąć jednego fsync na każde usunięcie.

Czego DELETE nie usuwa

Kilka częstych nieporozumień, na które warto zwrócić uwagę:

DELETE FROM table; opróżnia tabelę, ale jej nie usuwa. Aby usunąć samą tabelę, użyj DROP TABLE table;.
DELETE nie zmniejsza pliku bazy. Strony są oznaczane jako wolne do ponownego użycia. Aby odzyskać miejsce na dysku, uruchom VACUUM; (omówione w rozdziale o wydajności).
Usunięcie wiersza nie usuwa wierszy podrzędnych w innych tabelach, chyba że ustawiono ON DELETE CASCADE i włączono klucze obce.
DELETE, które nie pasuje do żadnego wiersza, nie jest błędem. To udana instrukcja z changes() = 0. Jeśli musisz to wiedzieć, sprawdź liczbę wierszy.

Dalej: UPSERT

Często tak naprawdę nie chcesz usuwać, tylko wstawić wiersz, jeśli jest nowy, albo zaktualizować go, jeśli już istnieje. SQLite nazywa to UPSERT, a klauzula ON CONFLICT zamienia to w jedną instrukcję zamiast trzech. O tym na następnej stronie.

Najczęściej zadawane pytania

Jak usunąć wiersz w SQLite?

Użyj DELETE FROM table_name WHERE condition;. Klauzula WHERE wybiera, które wiersze znikną. Na przykład DELETE FROM users WHERE id = 7; usuwa jednego użytkownika o id 7. Bez WHERE usuwany jest każdy wiersz tabeli.

Jak usunąć wszystkie wiersze z tabeli SQLite?

Uruchom DELETE FROM table_name; bez klauzuli WHERE. SQLite nie ma instrukcji TRUNCATE: DELETE bez filtra jest jej odpowiednikiem, a SQLite optymalizuje go wewnętrznie (tak zwana 'truncate optimization'). Aby zresetować też liczniki AUTOINCREMENT, usuń potem odpowiedni wpis z sqlite_sequence.

Czy SQLite potrafi kaskadowo usuwać wiersze w powiązanych tabelach?

Tak, jeśli zadeklarujesz ON DELETE CASCADE na kluczu obcym i włączysz klucze obce przez PRAGMA foreign_keys = ON;. W SQLite klucze obce są domyślnie wyłączone, więc ta pragma ma znaczenie: bez niej kaskady są po cichu ignorowane.

Jak zobaczyć, które wiersze zostały usunięte?

Dodaj klauzulę RETURNING: DELETE FROM users WHERE active = 0 RETURNING id, email; zwraca usunięte wiersze tak jak SELECT. Przydaje się to do logowania, funkcji cofania albo potwierdzenia, że usunięto dokładnie to, co miało zniknąć.

Powiązane zagadnienia