Исполняемые доки SQLite: DELETE

Q: Как удалить одну строку в SQLite?

Используйте DELETE FROM имя_таблицы WHERE условие;. Именно WHERE решает, какие строки уйдут. Например, DELETE FROM users WHERE id = 7; удалит одного пользователя с id 7. А вот без WHERE снесёт всю таблицу подчистую — будьте внимательны.

Q: Как удалить все строки из таблицы в SQLite?

Просто выполните DELETE FROM имятаблицы; без WHERE. В SQLite нет команды TRUNCATE — её роль как раз и играет DELETE без фильтра, причём движок применяет внутреннюю оптимизацию (так называемая truncate optimization). Если нужно ещё и сбросить счётчики AUTOINCREMENT, дополнительно почистите таблицу sqlitesequence.

Q: Как узнать, какие именно строки были удалены?

Допишите в конец RETURNING: запрос DELETE FROM users WHERE active = 0 RETURNING id, email; вернёт удалённые строки так же, как обычный SELECT. Удобно для логов, реализации undo или просто чтобы убедиться, что снесли ровно то, что хотели.

DELETE убирает строки — и больше ничего

DELETE удаляет строки из таблицы. Он не сносит саму таблицу, не меняет её схему и не трогает соседние таблицы (если вы, конечно, не настроили каскадное удаление). Синтаксис короткий:

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; находит строки, подходящие под условие, и удаляет их. Остальные две строки остаются нетронутыми. Сама таблица никуда не девается — в неё спокойно можно вставлять новые записи дальше.

Как это удобно держать в голове: DELETE — это тот же SELECT, только вместо того чтобы вернуть найденные строки, он их выбрасывает.

Вся работа держится на WHERE

Любой серьёзный DELETE стоит или падает на своём WHERE. Написали условие правильно — удалили ровно то, что хотели. Ошиблись — снесли лишнее, а иногда и всю таблицу целиком.

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
    ('Черновик один',   0, 0),
    ('Старый хит',      1, 5000),
    ('Опубликовано',    1, 120),
    ('Пустой черновик', 0, 0);

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

SELECT * FROM posts;

Оба черновика без публикаций и просмотров удалены. Опубликованные строки остались на месте — условие до них просто не дотянулось. В WHERE можно использовать любое выражение: IN, LIKE, BETWEEN, подзапросы, связки AND/OR.

Заведите полезную привычку: прежде чем запускать DELETE, прогоните то же самое условие WHERE через SELECT.

-- Предварительный просмотр того, что будет удалено:
SELECT * FROM posts WHERE published = 0 AND views = 0;

-- Устраивают строки? Теперь удалите их:
DELETE FROM posts WHERE published = 0 AND views = 0;

Этот танец в два шага спас больше баз данных, чем все бэкап-утилиты вместе взятые.

DELETE без WHERE очищает всю таблицу

Если не указать WHERE, то DELETE снесёт все строки до единой:

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

INSERT INTO log (message) VALUES ('один'), ('два'), ('три');

DELETE FROM log;

SELECT COUNT(*) AS remaining FROM log;

Таблица опустела, но сама по себе никуда не делась. В SQLite нет отдельной команды TRUNCATE — её роль выполняет DELETE FROM table;, причём движок применяет внутреннюю оптимизацию (так называемый truncate optimization) и сбрасывает все страницы разом, а не удаляет строки по одной. Получается быстро, но операция всё ещё транзакционная — при желании её можно откатить.

Если для первичного ключа вы использовали AUTOINCREMENT, счётчик сам собой не обнулится. Чтобы id снова пошли с 1, нужно дополнительно очистить служебную строку с последовательностью:

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

Если у тебя обычный INTEGER PRIMARY KEY (без AUTOINCREMENT), SQLite и так свободно переиспользует id, так что городить ничего не нужно.

Удаление нескольких конкретных строк

IN — самый аккуратный способ удалить заранее известный набор строк:

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

INSERT INTO tasks (title, status) VALUES
    ('написать документацию', 'done'),
    ('проверить PR',          'todo'),
    ('развернуть',            'done'),
    ('написать тесты',        'todo'),
    ('архивировать',          'done');

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

SELECT * FROM tasks;

Удалять строки можно и через подзапрос — это удобно, когда нужные записи описываются через join или живут в другой таблице:

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 не поддерживает синтаксис DELETE ... JOIN, как это умеет MySQL, но подзапрос в WHERE решает ту же задачу ничуть не хуже.

RETURNING: смотрим, что именно удалили

Добавьте RETURNING — и удалённые строки вернутся в виде результирующего набора, прямо как при 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;

Вы получаете обратно id и email каждой удалённой строки. Это бесценно для:

Логирования того, что именно было удалено.
Реализации функции отмены (можно куда-нибудь припрятать возвращённые строки).
Проверки в один запрос, что удаление затронуло именно те строки, которые вы ожидали.

RETURNING работает с INSERT, UPDATE и DELETE. Подробнее об этом — на отдельной странице.

ON DELETE CASCADE для связанных строк

Когда родительская и дочерняя таблицы связаны внешним ключом, удаление родителя оставляет осиротевшие дочерние записи — если, конечно, вы не скажете SQLite каскадировать удаление:

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;

Удаление автора автоматически удаляет и его книги. Без ON DELETE CASCADE тот же DELETE либо отработает и оставит «висячие» книги (если внешние ключи выключены), либо упадёт с ошибкой ограничения целостности (если включены).

Главный подводный камень: в SQLite внешние ключи по умолчанию выключены. Для каждого соединения нужно выполнять PRAGMA foreign_keys = ON;. Если эта прагма не установлена, ON DELETE CASCADE молча игнорируется — книги останутся на месте. Большинство драйверов либо включают её сами, либо дают для этого опцию; проверьте свой.

Из других вариантов каскада стоит знать: ON DELETE SET NULL (обнулить внешний ключ), ON DELETE RESTRICT (запретить удаление, если есть зависимые строки) и ON DELETE NO ACTION (поведение по умолчанию — в большинстве случаев работает так же, как RESTRICT).

DELETE с LIMIT (опция времени сборки)

Некоторые сборки SQLite поддерживают DELETE ... LIMIT — удобно, когда нужно подчищать огромные таблицы пачками:

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

Чтобы это сработало, SQLite должен быть собран с флагом SQLITE_ENABLE_UPDATE_DELETE_LIMIT. В официальных сборках и в большинстве биндингов для популярных языков (sqlite3 в Python, better-sqlite3 в Node) он уже включён. Если же в вашей сборке его нет, словите синтаксическую ошибку — тогда придётся обойтись подзапросом:

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

Удаление пачками сохраняет транзакции небольшими — это важно, когда параллельно с базой работают другие подключения на чтение.

Оборачиваем массовое удаление в транзакцию

DELETE сам по себе транзакционный: либо удалятся все подходящие строки, либо ни одной. Но если вы собираетесь удалить много данных, явная транзакция даёт возможность сделать ROLLBACK, если что-то пойдёт не так:

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 полностью откатывает удаление. В реальной работе вы бы вызвали COMMIT, как только убедились, что число удалённых строк выглядит правильно. Ещё транзакции заметно ускоряют удаление множества строк по одной — оборачивание цикла в BEGIN/COMMIT избавляет от fsync на каждое удаление.

Что на самом деле не удаляет данные

Несколько типичных заблуждений, о которых стоит сказать прямо:

DELETE FROM table; очищает таблицу, но не удаляет её саму. Чтобы убрать таблицу целиком, используйте DROP TABLE table;.
DELETE не уменьшает размер файла базы данных. Страницы просто помечаются как свободные для повторного использования. Чтобы реально освободить место на диске, выполните VACUUM; (об этом — в главе про производительность).
Удаление строки не удаляет связанные строки в других таблицах, если только не настроен ON DELETE CASCADE и не включены внешние ключи.
DELETE, под который не попала ни одна строка, — это не ошибка. Запрос отработал успешно, просто changes() = 0. Если вам нужно знать наверняка, проверяйте количество затронутых строк.

Дальше: UPSERT

Часто на самом деле нужно вовсе не удаление — нужно вставить строку, если её ещё нет, или обновить, если она уже есть. В SQLite это называется UPSERT, и конструкция ON CONFLICT позволяет уложиться в один запрос вместо трёх. Об этом — в следующей главе.

Часто задаваемые вопросы

Как удалить одну строку в SQLite?

Используйте DELETE FROM имя_таблицы WHERE условие;. Именно WHERE решает, какие строки уйдут. Например, DELETE FROM users WHERE id = 7; удалит одного пользователя с id 7. А вот без WHERE снесёт всю таблицу подчистую — будьте внимательны.

Как удалить все строки из таблицы в SQLite?

Просто выполните DELETE FROM имя_таблицы; без WHERE. В SQLite нет команды TRUNCATE — её роль как раз и играет DELETE без фильтра, причём движок применяет внутреннюю оптимизацию (так называемая truncate optimization). Если нужно ещё и сбросить счётчики AUTOINCREMENT, дополнительно почистите таблицу sqlite_sequence.

Поддерживает ли SQLite каскадное удаление связанных строк?

Да, но при двух условиях: во-первых, у внешнего ключа должно быть указано ON DELETE CASCADE, во-вторых — внешние ключи должны быть включены через PRAGMA foreign_keys = ON;. По умолчанию в SQLite они выключены, и без этой прагмы каскады молча игнорируются — это классический подводный камень.

Как узнать, какие именно строки были удалены?

Допишите в конец RETURNING: запрос DELETE FROM users WHERE active = 0 RETURNING id, email; вернёт удалённые строки так же, как обычный SELECT. Удобно для логов, реализации undo или просто чтобы убедиться, что снесли ровно то, что хотели.

Связанные концепции

SQLite DELETE: удаление строк через WHERE и RETURNING