【MySQL】数据表删除数据_mysql 删除数据-优快云博客

本文链接：https://blog.youkuaiyun.com/Go_ahead1025/article/details/147861029

在这里插入图片描述

个人主页：Guiat
归属专栏：MySQL

在这里插入图片描述

正文

1. 数据删除基础概念

1.1 删除操作的重要性

1.2 删除操作风险分析

2. DELETE语句详解

2.1 DELETE基本语法结构

2.2 基本DELETE示例

-- 删除单条记录
DELETE FROM customers
WHERE customer_id = 1001;

-- 删除多条符合条件的记录
DELETE FROM products
WHERE category = 'discontinued' AND stock = 0;

-- 删除日期范围内的记录
DELETE FROM orders
WHERE order_date < '2022-01-01';

2.3 使用ORDER BY和LIMIT控制删除

-- 删除最旧的1000条日志
DELETE FROM system_logs
ORDER BY created_at ASC
LIMIT 1000;

-- 先删除优先级低的任务
DELETE FROM tasks
WHERE status = 'completed'
ORDER BY priority ASC, completion_date ASC
LIMIT 500;

3. 高级DELETE操作

3.1 多表删除

-- 删除所有已取消订单的订单项
DELETE oi
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'cancelled';

-- 删除没有对应用户的所有评论
DELETE c
FROM comments c
LEFT JOIN users u ON c.user_id = u.user_id
WHERE u.user_id IS NULL;

3.2 使用子查询的DELETE

-- 删除所有已停产类别的产品
DELETE FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE discontinued = TRUE
);

-- 删除没有订单的客户
DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);

-- 删除低于平均销量的产品
DELETE FROM products
WHERE sales_count < (
    SELECT AVG(sales_count) FROM products
);

3.3 使用事务进行安全删除

-- 使用事务安全删除
START TRANSACTION;

-- 删除操作
DELETE FROM important_data
WHERE last_updated < '2022-01-01';

-- 检查影响的行数
SELECT ROW_COUNT() AS deleted_rows;

-- 如果删除的行数符合预期，提交事务
-- COMMIT;

-- 如果删除的行数不符合预期，回滚事务
-- ROLLBACK;

4. TRUNCATE TABLE操作

4.1 TRUNCATE与DELETE的区别

4.2 TRUNCATE使用场景

-- 完全清空临时表
TRUNCATE TABLE temp_import_data;

-- 重置测试数据
TRUNCATE TABLE test_customers;
TRUNCATE TABLE test_orders;

5. 批量删除策略

5.1 分批删除大量数据

-- 分批删除存储过程示例
DELIMITER //
CREATE PROCEDURE batch_delete_old_logs()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE deleted_count INT DEFAULT 0;
    DECLARE batch_size INT DEFAULT 5000;
    DECLARE total_deleted INT DEFAULT 0;
    
    -- 开始循环删除
    batch_loop: LOOP
        -- 删除一批数据
        DELETE FROM system_logs 
        WHERE log_date < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
        LIMIT batch_size;
        
        -- 获取影响的行数
        SET deleted_count = ROW_COUNT();
        SET total_deleted = total_deleted + deleted_count;
        
        -- 如果没有更多数据可删除，退出循环
        IF deleted_count = 0 THEN
            LEAVE batch_loop;
        END IF;
        
        -- 增加短暂延迟让系统"呼吸"
        DO SLEEP(0.5);
    END LOOP;
    
    -- 返回删除的总行数
    SELECT CONCAT('Total deleted: ', total_deleted, ' rows') AS result;
END //
DELIMITER ;

-- 执行存储过程
CALL batch_delete_old_logs();

5.2 使用临时表辅助删除

-- 创建临时表存储要删除的ID
CREATE TEMPORARY TABLE temp_orders_to_delete (
    order_id INT PRIMARY KEY
);

-- 填充临时表
INSERT INTO temp_orders_to_delete
SELECT order_id
FROM orders
WHERE order_date < '2022-01-01'
AND status = 'completed'
AND (SELECT SUM(amount) FROM order_items WHERE order_items.order_id = orders.order_id) < 10;

-- 先删除关联表中的数据
DELETE oi FROM order_items oi
JOIN temp_orders_to_delete t ON oi.order_id = t.order_id;

-- 再删除主表中的数据
DELETE o FROM orders o
JOIN temp_orders_to_delete t ON o.order_id = t.order_id;

-- 清理临时表
DROP TEMPORARY TABLE temp_orders_to_delete;

6. 特殊删除场景

6.1 级联删除

-- 创建带级联删除的外键
ALTER TABLE order_items
ADD CONSTRAINT fk_order_items_orders
FOREIGN KEY (order_id) REFERENCES orders(order_id)
ON DELETE CASCADE;

-- 手动实现级联删除
START TRANSACTION;

-- 先删除子表记录
DELETE FROM order_items WHERE order_id = 1001;
DELETE FROM order_shipments WHERE order_id = 1001;
DELETE FROM order_payments WHERE order_id = 1001;

-- 再删除主表记录
DELETE FROM orders WHERE order_id = 1001;

COMMIT;

6.2 软删除策略

-- 添加软删除相关字段
ALTER TABLE customers
ADD is_deleted TINYINT(1) DEFAULT 0,
ADD deleted_at DATETIME DEFAULT NULL;

-- 执行软删除
UPDATE customers
SET is_deleted = 1,
    deleted_at = NOW()
WHERE customer_id = 1001;

-- 查询时排除软删除的记录
SELECT * FROM customers
WHERE is_deleted = 0;

-- 创建视图简化查询
CREATE VIEW active_customers AS
SELECT * FROM customers
WHERE is_deleted = 0;

6.3 分区表删除策略

-- 创建按月分区的表
CREATE TABLE log_events (
    id INT NOT NULL,
    log_date DATE NOT NULL,
    message VARCHAR(255),
    PRIMARY KEY (id, log_date)
)
PARTITION BY RANGE (YEAR(log_date) * 100 + MONTH(log_date)) (
    PARTITION p202201 VALUES LESS THAN (202202),
    PARTITION p202202 VALUES LESS THAN (202203),
    PARTITION p202203 VALUES LESS THAN (202204),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

-- 清空指定分区
ALTER TABLE log_events TRUNCATE PARTITION p202201;

-- 删除指定分区
ALTER TABLE log_events DROP PARTITION p202201;

-- 添加新分区
ALTER TABLE log_events ADD PARTITION (
    PARTITION p202204 VALUES LESS THAN (202205)
);

7. 删除数据安全策略

7.1 备份与回滚策略

-- 在删除前创建表的备份
CREATE TABLE customers_backup AS
SELECT * FROM customers;

-- 备份要删除的数据
CREATE TABLE deleted_customers_archive AS
SELECT * FROM customers
WHERE last_activity_date < '2020-01-01';

-- 执行删除前验证将被删除的记录数
SELECT COUNT(*) AS to_be_deleted
FROM customers
WHERE last_activity_date < '2020-01-01';

-- 执行删除，带有事务保护
START TRANSACTION;

DELETE FROM customers
WHERE last_activity_date < '2020-01-01';

-- 验证结果
SELECT COUNT(*) AS remaining FROM customers;

-- 确认无误后提交，有误则回滚
-- COMMIT;
-- 或
-- ROLLBACK;

7.2 日志和审计

-- 创建审计表
CREATE TABLE delete_audit_log (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64) NOT NULL,
    record_id VARCHAR(100) NOT NULL,
    deleted_by VARCHAR(100) NOT NULL,
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    record_data JSON
);

-- 创建触发器记录删除操作
DELIMITER //
CREATE TRIGGER customers_before_delete
BEFORE DELETE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO delete_audit_log (
        table_name,
        record_id,
        deleted_by,
        record_data
    )
    VALUES (
        'customers',
        OLD.customer_id,
        CURRENT_USER(),
        JSON_OBJECT(
            'customer_id', OLD.customer_id,
            'name', OLD.name,
            'email', OLD.email,
            'created_at', OLD.created_at,
            'last_activity_date', OLD.last_activity_date
        )
    );
END //
DELIMITER ;

8. 实际应用场景

8.1 数据归档和清理

-- 数据归档示例: 归档旧订单到历史表
-- 1. 创建归档表
CREATE TABLE orders_archive LIKE orders;

-- 2. 归档数据
INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR);

-- 3. 验证归档
SELECT COUNT(*) FROM orders_archive;

-- 4. 删除已归档数据
DELETE FROM orders
WHERE order_date < DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR);

-- 自动化归档和清理存储过程
DELIMITER //
CREATE PROCEDURE archive_old_data(archive_date DATE)
BEGIN
    DECLARE archived_count INT DEFAULT 0;
    
    START TRANSACTION;
    
    -- 归档旧数据
    INSERT INTO orders_archive
    SELECT * FROM orders
    WHERE order_date < archive_date;
    
    SET archived_count = ROW_COUNT();
    
    -- 删除已归档数据
    DELETE FROM orders 
    WHERE order_date < archive_date;
    
    COMMIT;
    
    -- 返回处理结果
    SELECT CONCAT('Archived and deleted ', archived_count, ' records') AS result;
END //
DELIMITER ;

8.2 数据清洗和去重

-- 识别重复记录
SELECT email, COUNT(*) AS count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

-- 使用窗口函数标记重复（MySQL 8.0+）
WITH duplicates AS (
    SELECT 
        customer_id,
        email,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS row_num
    FROM customers
)
SELECT * FROM duplicates WHERE row_num > 1;

-- 删除重复记录，保留最新的
DELETE c1 FROM customers c1
JOIN customers c2 ON c1.email = c2.email
WHERE c1.customer_id < c2.customer_id;

-- 使用临时表方法删除重复
CREATE TEMPORARY TABLE unique_customers AS
SELECT MAX(customer_id) AS customer_id
FROM customers
GROUP BY email;

DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM unique_customers
);

DROP TEMPORARY TABLE unique_customers;

8.3 GDPR与数据隐私合规

-- GDPR用户数据删除示例

-- 1. 创建用户删除请求记录
INSERT INTO deletion_requests (user_id, request_date, status)
VALUES (1001, NOW(), 'pending');

-- 2. 开始删除流程
START TRANSACTION;

-- 3. 删除用户相关所有数据
-- 删除用户评论
DELETE FROM comments WHERE user_id = 1001;

-- 删除用户订单项
DELETE oi FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE o.user_id = 1001;

-- 删除用户订单
DELETE FROM orders WHERE user_id = 1001;

-- 删除用户收货地址
DELETE FROM shipping_addresses WHERE user_id = 1001;

-- 删除用户支付方式（或匿名化）
UPDATE payment_methods
SET card_number = 'XXXXXXXXXXXX1234',
    cardholder_name = 'ANONYMIZED',
    expiry_date = NULL,
    anonymized = TRUE
WHERE user_id = 1001;

-- 最后删除用户账户
DELETE FROM users WHERE user_id = 1001;

-- 4. 更新删除请求状态
UPDATE deletion_requests
SET status = 'completed', completed_at = NOW()
WHERE user_id = 1001;

COMMIT;