DELETE

-- Basic
DELETE FROM users WHERE id = 42;

-- LIMIT — caps damage from a hand-run delete
DELETE FROM logs
WHERE   created_at < NOW() - INTERVAL 30 DAY
ORDER   BY created_at
LIMIT   10000;

-- Run in a loop until done (avoids huge transactions)
-- (in your application code)
do {
    rows = await db.query('DELETE FROM logs WHERE created_at < ? ORDER BY id LIMIT 10000',
                          [cutoff]);
} while (rows.affectedRows > 0);

-- Multi-table DELETE — remove from several tables in one statement
DELETE p, pt
FROM   posts p
JOIN   post_tags pt ON pt.post_id = p.id
WHERE  p.author_id = 1;

-- DELETE … USING — alternative syntax, same effect
DELETE p
FROM   posts p USING users u
WHERE  p.user_id = u.id
  AND  u.banned = 1;

-- ON DELETE CASCADE — let the schema do the cleanup
CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    CONSTRAINT fk_posts_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
);

-- TRUNCATE — instant empty, resets AUTO_INCREMENT, NO triggers
TRUNCATE TABLE sessions;

-- Soft delete — flag + filter
UPDATE users SET deleted_at = NOW() WHERE id = 42;
-- And read with the flag
SELECT * FROM users WHERE deleted_at IS NULL;

DELETE FROM users
WHERE created_at < NOW() - INTERVAL 1 YEAR;