SQLite数据删除 #

-- DELETE 基本语法
DELETE FROM table_name
WHERE condition;

-- 示例表结构
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    status INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, email, status) VALUES
    ('John', 'john@example.com', 1),
    ('Jane', 'jane@example.com', 1),
    ('Bob', 'bob@example.com', 0),
    ('Alice', 'alice@example.com', 0),
    ('Charlie', 'charlie@example.com', 1);

-- SQLite DELETE 特点：
-- 1. 可以删除单行或多行
-- 2. 支持子查询条件
-- 3. 支持 RETURNING 子句（3.35+）
-- 4. 不支持 LIMIT（需要变通）
-- 5. 触发 DELETE 触发器

-- 删除指定行
DELETE FROM users WHERE id = 1;

-- 删除多行
DELETE FROM users WHERE status = 0;

-- 删除所有行（危险！）
DELETE FROM users;

-- 等于
DELETE FROM users WHERE id = 1;

-- 不等于
DELETE FROM users WHERE status != 1;

-- 大于/小于
DELETE FROM users WHERE id > 100;
DELETE FROM users WHERE created_at < date('now', '-30 days');

-- 范围
DELETE FROM users WHERE id BETWEEN 1 AND 10;

-- AND
DELETE FROM users 
WHERE status = 0 AND created_at < date('now', '-30 days');

-- OR
DELETE FROM users 
WHERE status = 0 OR created_at < date('now', '-365 days');

-- NOT
DELETE FROM users 
WHERE NOT status = 1;

-- IN
DELETE FROM users WHERE id IN (1, 2, 3);

-- NOT IN
DELETE FROM users WHERE status NOT IN (1, 2);

-- 子查询
DELETE FROM users 
WHERE id IN (SELECT user_id FROM inactive_users);

-- LIKE 模式匹配
DELETE FROM users WHERE email LIKE '%@old-domain.com';

-- GLOB 模式匹配
DELETE FROM users WHERE email GLOB '*@test.*';

-- 删除没有订单的用户
DELETE FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);

-- 删除有特定条件的记录
DELETE FROM users
WHERE id IN (
    SELECT user_id 
    FROM orders 
    WHERE total < 10
);

-- 使用EXISTS
DELETE FROM users
WHERE NOT EXISTS (
    SELECT 1 FROM orders WHERE orders.user_id = users.id
);

-- 删除超过一定时间未活跃的用户
DELETE FROM users
WHERE id IN (
    SELECT u.id 
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE o.order_date IS NULL 
    OR o.order_date < date('now', '-365 days')
);

-- 删除重复记录（保留最新的一条）
DELETE FROM logs
WHERE id NOT IN (
    SELECT MAX(id) 
    FROM logs 
    GROUP BY user_id, action
);

-- 返回被删除的数据
DELETE FROM users
WHERE id = 1
RETURNING id, name, email;

-- 返回所有列
DELETE FROM users
WHERE status = 0
RETURNING *;

-- 返回计算值
DELETE FROM users
WHERE id = 1
RETURNING 
    id, 
    name, 
    'deleted' AS action,
    datetime('now') AS deleted_at;

-- 批量删除并返回
DELETE FROM users
WHERE status = 0
RETURNING id, name;

-- 查看删除的行数
SELECT changes();

-- 启用外键约束
PRAGMA foreign_keys = ON;

-- 创建带级联删除的表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    total REAL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- 删除用户时，自动删除相关订单和订单项
DELETE FROM users WHERE id = 1;

-- CASCADE：级联删除
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- SET NULL：设置为NULL
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- SET DEFAULT：设置为默认值
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET DEFAULT
);

-- RESTRICT：限制删除（默认）
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

-- NO ACTION：无动作（同RESTRICT）
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE NO ACTION
);

-- 如果没有设置级联，需要手动删除

BEGIN TRANSACTION;

-- 先删除子表记录
DELETE FROM order_items 
WHERE order_id IN (SELECT id FROM orders WHERE user_id = 1);

DELETE FROM orders WHERE user_id = 1;

-- 最后删除主表记录
DELETE FROM users WHERE id = 1;

COMMIT;

-- SQLite 不支持 TRUNCATE TABLE
-- TRUNCATE TABLE users;  -- 错误！

-- 替代方案1：DELETE
DELETE FROM users;

-- 替代方案2：删除并重建表
DROP TABLE users;
CREATE TABLE users (...);

-- 替代方案3：使用DELETE + VACUUM
DELETE FROM users;
VACUUM;

-- 方法1：DELETE FROM
-- 优点：触发触发器
-- 缺点：较慢，不重置ROWID
DELETE FROM users;

-- 方法2：DROP + CREATE
-- 优点：快速，重置ROWID
-- 缺点：需要重建索引、触发器
DROP TABLE users;
CREATE TABLE users (...);
CREATE INDEX ...;

-- 方法3：DELETE + VACUUM
-- 优点：回收空间
-- 缺点：最慢
DELETE FROM users;
VACUUM;

-- 添加删除标记列
ALTER TABLE users ADD COLUMN deleted_at DATETIME;

-- 软删除：更新deleted_at
UPDATE users 
SET deleted_at = CURRENT_TIMESTAMP 
WHERE id = 1;

-- 查询时排除已删除记录
SELECT * FROM users WHERE deleted_at IS NULL;

-- 恢复删除
UPDATE users 
SET deleted_at = NULL 
WHERE id = 1;

-- 创建视图只显示未删除记录
CREATE VIEW active_users AS
SELECT id, name, email, status
FROM users
WHERE deleted_at IS NULL;

-- 使用视图
SELECT * FROM active_users;

-- 通过视图操作
INSERT INTO active_users (name, email) VALUES ('Test', 'test@example.com');
UPDATE active_users SET status = 0 WHERE id = 1;
-- 注意：DELETE 不能直接通过视图软删除

-- 创建触发器拦截DELETE操作
CREATE TRIGGER users_soft_delete
INSTEAD OF DELETE ON users
BEGIN
    UPDATE users 
    SET deleted_at = CURRENT_TIMESTAMP 
    WHERE id = OLD.id AND deleted_at IS NULL;
END;

-- 现在DELETE操作变成软删除
DELETE FROM users WHERE id = 1;
-- 实际执行的是 UPDATE users SET deleted_at = ... WHERE id = 1

-- SQLite 不支持这种语法
-- DELETE FROM users WHERE status = 0 LIMIT 10;  -- 错误！

-- 使用子查询实现LIMIT删除
DELETE FROM users
WHERE id IN (
    SELECT id FROM users 
    WHERE status = 0 
    LIMIT 10
);

-- 分批删除大量数据
-- 每次删除1000条
DELETE FROM logs
WHERE id IN (
    SELECT id FROM logs 
    WHERE created_at < date('now', '-365 days')
    LIMIT 1000
);
-- 重复执行直到删除完成

-- 使用事务
BEGIN TRANSACTION;
DELETE FROM logs WHERE created_at < date('now', '-30 days');
COMMIT;

-- 分批删除大量数据
BEGIN TRANSACTION;
DELETE FROM logs
WHERE id IN (
    SELECT id FROM logs 
    WHERE created_at < date('now', '-365 days')
    LIMIT 10000
);
COMMIT;
-- 重复执行

-- 临时禁用索引
-- SQLite不支持DISABLE INDEX
-- 可以先删除索引，删除后再创建

-- 删除大量数据后执行VACUUM
DELETE FROM logs WHERE created_at < date('now', '-365 days');
VACUUM;

-- 更新统计信息
ANALYZE;

-- 错误：忘记WHERE条件
DELETE FROM users;  -- 删除所有行！

-- 安全做法：先查询
SELECT COUNT(*) FROM users WHERE status = 0;
-- 确认后再执行删除

-- 使用事务
BEGIN TRANSACTION;
DELETE FROM users WHERE status = 0;
-- 检查结果
SELECT changes();
-- 确认无误后提交
COMMIT;
-- 或回滚
ROLLBACK;

-- 删除前备份
CREATE TABLE users_backup AS SELECT * FROM users WHERE status = 0;

-- 或导出到文件
-- .output users_to_delete.sql
-- SELECT 'INSERT INTO users VALUES(' || id || ',' || quote(name) || ');' 
-- FROM users WHERE status = 0;
-- .output stdout

-- 使用参数化查询
-- Python
-- cursor.execute("DELETE FROM users WHERE id = ?", (user_id,))

-- PHP
-- $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
-- $stmt->execute([$user_id]);

-- 创建删除触发器
CREATE TRIGGER users_delete_log
AFTER DELETE ON users
BEGIN
    INSERT INTO users_log (user_id, action, action_time)
    VALUES (OLD.id, 'DELETE', CURRENT_TIMESTAMP);
END;

-- 触发器会在DELETE时自动执行
DELETE FROM users WHERE id = 1;
-- 自动记录到 users_log

-- 在触发器中使用OLD引用被删除的行
CREATE TRIGGER orders_delete_archive
BEFORE DELETE ON orders
BEGIN
    INSERT INTO orders_archive (
        order_id, user_id, total, deleted_at
    )
    VALUES (
        OLD.id, OLD.user_id, OLD.total, CURRENT_TIMESTAMP
    );
END;

-- 启用外键
PRAGMA foreign_keys = ON;

-- 错误：FOREIGN KEY constraint failed
DELETE FROM users WHERE id = 1;
-- 因为 orders 表有引用

-- 解决方案：
-- 1. 先删除子表记录
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;

-- 2. 或使用级联删除
-- 创建表时指定 ON DELETE CASCADE

-- 删除返回0行
DELETE FROM users WHERE id = 999;

-- 检查影响的行数
SELECT changes();  -- 返回0

-- 解决方案：确认条件是否正确
SELECT * FROM users WHERE id = 999;

-- 触发器可能阻止删除
CREATE TRIGGER prevent_delete
BEFORE DELETE ON users
BEGIN
    SELECT RAISE(ABORT, 'Cannot delete users');
END;

-- 删除会失败
DELETE FROM users WHERE id = 1;
-- Error: Cannot delete users