MariaDB数据删除 #

DELETE FROM table_name
WHERE condition;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    status TINYINT DEFAULT 1,
    is_deleted TINYINT DEFAULT 0,
    deleted_at DATETIME,
    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', 1),
    ('Charlie', 'charlie@example.com', 0);

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

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

-- 使用IN条件
DELETE FROM users WHERE id IN (1, 2, 3);

-- 使用BETWEEN
DELETE FROM users WHERE id BETWEEN 10 AND 20;

-- 使用LIKE
DELETE FROM users WHERE email LIKE '%@test.com';

-- 删除所有数据（保留表结构）
DELETE FROM users;

-- 注意：这会删除所有数据！
-- 推荐使用TRUNCATE（更快）
TRUNCATE TABLE users;

-- 限制删除行数
DELETE FROM users WHERE status = 0 LIMIT 10;

-- 分批删除
DELETE FROM users WHERE status = 0 LIMIT 1000;

-- 按顺序删除
DELETE FROM users 
WHERE status = 0 
ORDER BY created_at ASC 
LIMIT 10;

-- 删除最旧的记录
DELETE FROM users 
ORDER BY created_at ASC 
LIMIT 100;

-- 启用安全模式
SET sql_safe_updates = 1;

-- 安全模式下必须有KEY条件或LIMIT
DELETE FROM users WHERE id = 1;  -- OK
DELETE FROM users WHERE name = 'John';  -- 错误
DELETE FROM users LIMIT 1;  -- OK

-- 关闭安全模式
SET sql_safe_updates = 0;

-- 删除前先查询
SELECT * FROM users WHERE id = 1;

-- 确认后再删除
DELETE FROM users WHERE id = 1;

-- 使用事务
START TRANSACTION;
DELETE FROM users WHERE id = 1;
-- 确认无误后提交
COMMIT;
-- 或回滚
ROLLBACK;

-- 创建备份表
CREATE TABLE users_backup AS SELECT * FROM users WHERE status = 0;

-- 删除数据
DELETE FROM users WHERE status = 0;

-- 如果需要恢复
INSERT INTO users SELECT * FROM users_backup;

-- 清空表
TRUNCATE TABLE users;

-- 或简写
TRUNCATE users;

-- 使用DELETE：
-- 1. 需要WHERE条件
DELETE FROM users WHERE status = 0;

-- 2. 需要触发器
DELETE FROM users WHERE id = 1;

-- 3. 需要事务回滚
START TRANSACTION;
DELETE FROM users;
ROLLBACK;

-- 使用TRUNCATE：
-- 1. 清空整个表
TRUNCATE TABLE logs;

-- 2. 重置AUTO_INCREMENT
TRUNCATE TABLE users;  -- id从1开始

-- 3. 快速删除大量数据
TRUNCATE TABLE temp_data;

-- 创建关联表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 多表删除语法
DELETE users, orders
FROM users
JOIN orders ON users.id = orders.user_id
WHERE users.id = 1;

-- 删除用户及其订单
DELETE u, o
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;

-- 使用子查询作为条件
DELETE FROM users 
WHERE id IN (
    SELECT user_id FROM orders WHERE status = 'cancelled'
);

-- 使用EXISTS
DELETE FROM users 
WHERE EXISTS (
    SELECT 1 FROM orders 
    WHERE user_id = users.id 
    AND status = 'cancelled'
);

-- 注意：不能在子查询中引用要删除的表
-- 错误示例：
-- DELETE FROM users WHERE id IN (SELECT user_id FROM users WHERE status = 0);

-- 正确做法：
DELETE FROM users 
WHERE status = 0 AND id IN (SELECT id FROM (SELECT id FROM users WHERE status = 0) AS tmp);

-- 创建带级联删除的表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 删除用户时，订单自动删除
DELETE FROM users WHERE id = 1;
-- orders表中user_id=1的记录也会被删除

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

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

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

-- NO ACTION：同RESTRICT
CREATE TABLE orders (
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE NO ACTION
);

-- 软删除：不真正删除数据，而是标记为已删除
-- 优点：
-- 1. 数据可恢复
-- 2. 保留历史记录
-- 3. 支持审计追踪

-- 添加软删除字段
ALTER TABLE users 
ADD COLUMN is_deleted TINYINT DEFAULT 0,
ADD COLUMN deleted_at DATETIME;

-- 软删除操作
UPDATE users 
SET is_deleted = 1, 
    deleted_at = NOW() 
WHERE id = 1;

-- 查询时过滤已删除数据
SELECT * FROM users WHERE is_deleted = 0;

-- 恢复数据
UPDATE users 
SET is_deleted = 0, 
    deleted_at = NULL 
WHERE id = 1;

-- 创建视图（只显示未删除数据）
CREATE VIEW active_users AS
SELECT id, name, email, status
FROM users
WHERE is_deleted = 0;

-- 使用视图查询
SELECT * FROM active_users;

-- 通过视图操作
INSERT INTO active_users (name, email) VALUES ('New', 'new@example.com');
UPDATE active_users SET status = 0 WHERE id = 1;

-- 创建触发器防止真正删除
DELIMITER //
CREATE TRIGGER prevent_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    -- 将删除转换为软删除
    INSERT INTO users (id, name, email, status, is_deleted, deleted_at)
    VALUES (OLD.id, OLD.name, OLD.email, OLD.status, 1, NOW());
    
    -- 取消真正的删除
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Use soft delete instead';
END //
DELIMITER ;

-- MariaDB 10.5+ 支持RETURNING
DELETE FROM users 
WHERE id = 1
RETURNING id, name, email;

+----+------+-------------------+
| id | name | email             |
+----+------+-------------------+
|  1 | John | john@example.com  |
+----+------+-------------------+

-- 低优先级删除
DELETE LOW_PRIORITY FROM users 
WHERE status = 0;

-- 等待所有读取完成后执行

-- QUICK选项（不更新索引）
DELETE QUICK FROM users WHERE status = 0;

-- 适合MyISAM表，删除后需优化表
OPTIMIZE TABLE users;

-- 大表分批删除
-- 方法1：使用LIMIT
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;

-- 方法2：使用存储过程
DELIMITER //
CREATE PROCEDURE batch_delete()
BEGIN
    DECLARE done INT DEFAULT 0;
    WHILE done = 0 DO
        DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;
        IF ROW_COUNT() = 0 THEN
            SET done = 1;
        END IF;
        DO SLEEP(0.1);
    END WHILE;
END //
DELIMITER ;

CALL batch_delete();

-- 大量删除时临时禁用索引
ALTER TABLE logs DISABLE KEYS;

DELETE FROM logs WHERE created_at < '2023-01-01';

ALTER TABLE logs ENABLE KEYS;

-- 分区表快速删除
-- 删除整个分区
ALTER TABLE logs DROP PARTITION p2022;

-- 比DELETE快得多

-- 创建删除日志表
CREATE TABLE user_delete_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    name VARCHAR(50),
    email VARCHAR(100),
    deleted_by VARCHAR(50),
    deleted_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 使用触发器记录删除
DELIMITER //
CREATE TRIGGER log_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_delete_log (user_id, name, email, deleted_by)
    VALUES (OLD.id, OLD.name, OLD.email, CURRENT_USER());
END //
DELIMITER ;

-- 错误：Cannot delete or update a parent row: a foreign key constraint fails
DELETE FROM users WHERE id = 1;

-- 解决方案1：先删除关联数据
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;

-- 解决方案2：使用级联删除
-- （需要在创建表时设置）

-- 解决方案3：临时禁用外键检查
SET foreign_key_checks = 0;
DELETE FROM users WHERE id = 1;
SET foreign_key_checks = 1;

-- 查看锁等待
SHOW PROCESSLIST;

-- 查看锁信息
SELECT * FROM information_schema.innodb_locks;

-- 终止阻塞的进程
KILL <process_id>;

-- InnoDB表空间不会自动收缩
-- 优化表释放空间
OPTIMIZE TABLE users;

-- 或使用ALTER TABLE重建表
ALTER TABLE users ENGINE=InnoDB;

# 从备份文件恢复
mariadb -u root -p mydb < backup.sql

# 恢复单个表
mariadb -u root -p mydb < users_backup.sql

# 查看二进制日志
mariadb-binlog /var/lib/mysql/mysql-bin.000001

# 恢复到指定时间点
mariadb-binlog --stop-datetime="2024-01-15 10:00:00" /var/lib/mysql/mysql-bin.000001 | mariadb -u root -p

-- 恢复软删除的数据
UPDATE users 
SET is_deleted = 0, 
    deleted_at = NULL 
WHERE id = 1;

-- 批量恢复
UPDATE users 
SET is_deleted = 0, 
    deleted_at = NULL 
WHERE deleted_at > '2024-01-01';

删除前检查
├── 确认删除条件
├── 备份重要数据
├── 检查外键约束
├── 使用事务
├── 考虑使用软删除
└── 记录删除日志